スプレッドシートQuery関数を使ってみよう ④WHERE句

クエリイメージ スプレッドシート
この記事は約10分で読めます。
Advertisements

読んで欲しい人

  • SQLに興味があるが、一から環境やデータ整備するのがめんどくさい人
  • vlookupとかでデータ抽出しているが、限界を感じている人
  • データ分析したい人
  • ノンプログラマーでもクエリぐらいは使いたいって人
  • クエリって美味しいの?って人(クエリは美味しいですよって意味も込めて)

Query関数(クエリ関数)シリーズ予定 (あくまで予定)

  1. 概要と目的
  2. クエリ関数の構成
  3. 基本の基本 SELECT句(全部取得、特定カラムだけ取得)
  4. 抽出の基本 WHERE句を使ってみる(数値一致、大小、日付一致、大小etc)←今回
  5. GROUP BY句を使ってみる
  6. 他のシートからデータを持ってきてみよう〜 ImportRange関数との組み合わせ〜
  7. 並び替えの基本 ORDER BY句
  8. SQLやるなら必須 サブクエリをやってみる
  9. データに改行が入ってるぞ likeで対応できない時はcontains/starts withが助けてくれる

Query関数:WHERE句の基本

WHERE句ですが、基本はSELECT文で抽出範囲を選んだのちに、条件を設定するために作成します。スプレッドシート関数のsumif/sumifsとかのif/ifs部分に類似するイメージですね。

イメージ
=query(データ範囲,"select * where (カラム) = '抽出対象'")

サンプル
=query(sample!A1:H7,"select * where B = 'Eng'") #前回公開しているデータでB列deptがEngのデータを抽出

検索の方法としては、主に

  • 一致(複数一致, OR一致、前方一致、後方一致)
  • 含有(like, containsなど)
  • 以上以下
  • 空白か否か
  • 正規表現

に分類できます。以下では一つづつ実際の書き方をみながら説明していきます。

今後利用する元データは前回リンクを貼ってありますが、再度貼ると共に、画像も貼り付けておきます。

URL Link: https://developers.google.com/chart/interactive/docs/querylanguage

元データ

WHERE句 複数条件一致(論理積)はANDを使う

抽出条件を複数使いたい場合(sumifsのイメージ)は、ANDでWHERE句内の抽出条件を複数設定します。

例えば、deptがEngに所属している、ageが35歳の人を抽出したいとしましょう。こんな感じになります。

=query(sample!A1:H7,"select * where B = 'Eng' AND F =35")

whereの後に、B=XXとF=XXをAND条件でつないでいます。これで、2つの条件共にみたすデータだけが抽出できます。この後説明していきますが、条件は=だけでなく、様々(大小やNull出ないなど)ありますので、それを利用することも可能です。

例えば、deptがEngに所属している、ageが30歳以上の人を抽出したい場合、以下のようになります。

=query(sample!A1:H7,"select * where B = 'Eng' AND F >=30")

WHERE句 どちらか条件一致(論理和)はORを使う

これはスプレッドシート関数sumifsだと標準でサポートされていない集計方法ですが、WHERE句では簡単にできます。先ほどみたANDの部分をORに変えればOKです。

例えば、deptがEngに所属している人、もしくはageが30歳未満の人を抽出したいとしましょう。こんな感じになります。

=QUERY(sample!A1:H7,"select * where B = 'Eng' or F <30")

WHERE句 前方一致はstarts with/ 後方一致はends with

特定の文字/数字/記号で始まる行を検索するためにはstarts withを使います。

例えば、deptがSで始まるものを抽出すると、Sales部門所属のデータだけが抽出されます。

=QUERY(sample!A1:H7,"select * where B starts with 'S'")

また、2005年入社の人を抽出したいときには、hireDateが2005で始まるものを抽出すれば行けます。

=query(sample!A1:H7,"where E starts with 2005")

他方、特定の文字/数字/記号で終わる行を検索するためにはends withを使います。

例えば、10日入社の社員だけ抽出してみたい場合、ends withが有用です。

=query(sample!A1:H7,"where E ends with 10")

WHERE句 日付を検索するためのDate

さて、2005年10月10日入社の人を抽出したい、となりました。普通にWHERE句を利用して日付を指定しても想定した結果が表示されません。

❌ =query(sample!A1:H7,"where E = 2005-10-10") #ValueErrorになる
❌ =query(sample!A1:H7,"where E = '2005-10-10'") #何も抽出されない

これは、日付を検索する場合には、dateを検索条件の前につける必要があります。

⭕️ =query(sample!A1:H7,"where E = date '2005-10-10'")

これは、dateパラメータを付けないと、入力した条件(今回の場合2005-10-10)が、日付として判定されず、一致する値がないとquery関数で判断されるためです。

また、日付を直接入力するのではなく、セルから日付を参照する場合はTEXT関数とセットで使います。

⭕️ =query(sample!A1:H7,"where E = date '”& TEXT(条件入力セル,”YYYY-MM-DD”) & “'")

&で囲んで、TEXT関数という使用方法は非常によく使う方法です。スプレッドシート 上に検索条件入力枠を持たせる場合に使いますので、ぜひ慣れてください。TEXT関数がわからない方は、ググってもらうと色々記事が出てくるかと思います。

WHERE句 時刻を検索するためのtimeofday/timestamp型のdatetime ついでにその他の型も復習

先ほどDate型を紹介しましたが、実はこれまではstring(文字列)/number(数値)の2つの型をベースにみてきていました。では、そもそもWHERE句で利用するデータ型の一覧はどうなっているのでしょうか。以下でみていきましょう。

内容
string文字列: “aa”, ‘bb’など
number数値: 1, 1.0, -2 , -71など
booleantrue or false
date日付、yyyy-mm-dd:date “2005-10-10”
timeofday 時刻、HH:mm:ss: timeofday “12:30:45”
datetime日付と時刻、yyyy-mm-dd HH:mm:ss: datetime ‘2008-03-18 12:30:34.123’

数値や文字列はそのまま入力可能ですが、日付や時刻については、検索条件の前にdate/timeofday/datetimeなどのデータ型の宣言をしないと動作しませんので注意してください。

WHERE句 最もよく使われるlike検索

LIKEパラメータを使えば、2つのワイルドカードを元にデータを抽出することができます。

ワイルドカード

  1. %(パーセント記号): ゼロor 1文字以上
  2. _ (アンダーバー): 任意の1文字

Like %を利用して検索する

特定のゼロor1文字以上として認識される%を利用すると、前方一致・内部一致・後方一致検索全てがカバーできます。

前方一致

2005年入社の人を検索したいとします。前方一致なので、検索したい2005+%でできます。

=query(sample!A1:H7,"where E like '2005%'")

これがstarts withと一致します。

後方一致

10月10日入社の人を検索してみましょう。後方一致ですね、%+10-10で行けますね。

=query(sample!A1:H7,"where E like '%10-10'")

これでends withと同様の動作をします。

内部一致

2005年の何月でもいいから10日入社の人を検索するとします。

=query(sample!A1:H7,"where E like '2005%10'")
like検索はあくまでstr/num検索

さて、気づかれた方もいるかと思いますが、これまで日付を検索するときはDATEパラメータをつけることが必要でしたが、今回のLIKEパラメータではDATEパラメータが不要となっています。これは、日付を検索しているのですが、実際に検索している型は日付ではなく、数値なのです。

そのためLIKE検索をする場合、検索条件をDATE型に変更する必要はありません

Like _を利用して検索する

_(アンダーバー)を利用する場合、文字数がわかっているor特定の文字数内でフレーズを検索したいときに利用します。

例えば、salaryが3桁の人を検索したいとすると以下のような形になります。

=query(sample!A1:H7,"where D like '___'") #アンダーバー3つ

もちろん%と_を組み合わせることも可能ですので、セットで利用するケースもよくあります。

WHERE CONTAINS検索  LIKE検索ににてるけど少し違う

LIKE検索ににているのですが、少し違うのはCONTAINS検索。単純に特定の文字・記号等が含まれているかどうかを判断します。LIKE検索で代替も可能ですが、フレーズ検索で利用する場合、前後文字とかの条件を考えなくてもいいので楽ですね。あとは、データ上で目安にする記号とうが含まれてる(受注したら、案件名に★をつける)データにする、みたいな運用をしている方も活用方法がありますね。

=query(データ範囲,"select * where A contains '★'")

WHERE 空白検索NULL is null /is not null

is nullを利用すると空白だけを、is not nullを利用すると空白ではないものだけ抽出可能です。

空白ではない
=query(sample!A1:H7,"select * where H is not null")
空白である
=query(sample!A1:H7,"select * where H is null")

空白ではないは利用するケースがあまり多くないかもですが、データの漏れを検索する際にis not nullはよく利用します。

WHERE 正規表現による検索MATCHES

MATCHESパラメータを使えば、自分が設定した正規表現を元にデータを抽出することができます。

正直いうと、『複数条件AND/OR』と『日付のDATE』、『空白』以外は、MATCHESだけで代用できます。

例えば先ほどLIKE _(アンダーバー)で紹介した3桁収入の人だけ抽出するは、以下のように正規表現で代替できます。

=query(sample!A1:H7,"where D matches '.{3}'") #アンダーバー3つ

.が任意の1文字を意味し、{数値}で繰り返しの数を指定します。正規表現の記法は色々やくに立ちますので、どこかで一覧を紹介したいと思います。現段階ではググってもらえると。

まとめ

駆け足で色々紹介してきましたが、多いですね。それだけWHEREで検索することはqueryにおいてメジャーな機能ということになります。

ぜひいろんなパターンを習得してみてください。同じような機能なのになぜあるんだって物もあるかと思いますが、実は細かな仕様で差があったりします。それは、最後9回目のデータが汚い場合(改行が入っていたり、同じ列に違うデータタイプがあったり)において、少し説明していきたいなと思っています。

連載目次: spreadsheet_query関数を使いこなそう

SQL勉強もかねて、spreadsheetのquery関数の使い方に慣れていきます。簡単なselect文の使い方から、SQLとの違いも含めて勉強していきます。

  1. スプレッドシートQuery関数を使ってみよう ①概要と目的
  2. スプレッドシートQuery関数を使ってみよう ②クエリ関数の構成
  3. スプレッドシートQuery関数を使ってみよう ③SELECT句
  4. スプレッドシートQuery関数を使ってみよう ④WHERET句
  5. スプレッドシートQuery関数を使ってみよう ⑤GROUP BYを使ってみる
  6. スプレッドシートQuery関数を使ってみよう ⑥他のシートからデータを持ってきてみよう〜 ImportRange関数との組み合わせ〜
  7. スプレッドシートQuery関数を使ってみよう ⑦並び替えの基本 ORDER BY句
  8. スプレッドシートQuery関数を使ってみよう ⑧SQLやるなら必須 Query関数でもサブクエリをやってみる
  9. スプレッドシートQuery関数を使ってみよう ⑨データに改行が入ってるぞ etc クエリ関数エラー対策

コメント

  1. […] スプレッドシートQuery関数を使ってみよう ④WHERE句クエリ使ってみたいけ… […]