QUERY関数のWHERE句を使用すると、自分で指定した条件にマッチするデータを絞り込んで抽出することができます。
例えば、このデータから給料が800以上である人だけを絞り込みたい場合は、以下のクエリ文を記述します。
=QUERY(B2:I8, "WHERE E >= 800")
これで、給料を800以上もらっている人は、JohnとMikeであることが分かりました。
QUERY関数は、第1引数に『データ』、第2引数に『目的に沿ったSQL風なクエリ文』を書くという流れになるので、この第2引数クエリを思い通りに操れるかどうか、が目的のデータを正しく抜き出すための必要条件になります。
2020年12月現在、11種類ものクエリ句が存在し、
| クエリ句 | 役割 | 重要度(5段階) |
|---|---|---|
SELECT | データから列を抽出する | ★★★★★ |
WHERE | WHERE句で条件に一致する行を抽出する | ★★★★★ |
GROUP BY | 列のデータを行方向に集約(グルーピング)する | ★★★★★ |
PIVOT | 列のデータを列方向に集約(グルーピング)する | ★★★★☆ |
ORDER BY | 出力されるデータを並び替える | ★★★★☆ |
SKIPPING | 出力されるデータの先頭行から任意の行数ずつスキップする | ★☆☆☆☆ |
LIMIT | 出力されるデータの行数を制限する | ★★★☆☆ |
OFFSET | 出力されるデータの先頭行から任意の行数スキップする | ★★☆☆☆ |
LABEL | 出力される列名を変更する | ★★★★☆ |
FORMAT | 出力される列のデータの書式を変更する | ★★★☆☆ |
OPTIONS | 追加オプションを設定する | ★☆☆☆☆ |
この記事では、条件に一致する行を抽出するWHERE句について、じっくり解説していきます。
データサイエンティスト / ブロガー
たぬ
tanu
福岡在住のデータサイエンティスト。Pythonでルーチンワークを瞬殺する仕組みを作ることやデータを視覚化することが得意です。
WHERE句の基本
WHERE句は、WHEREと列、比較演算子、値の間に半角スペースを入力して、"WHERE 列 比較演算子 値"とクエリ文を書くと、任意の条件に絞り込んだデータだけを抽出することができます。
=QUERY(データ, "WHERE 列 比較演算子 値")
=QUERY(B2:I8, "WHERE E >= 800")比較演算子は、以下の表にまとめています。
| 比較演算子 | 意味 | データ型 |
|---|---|---|
= | 一致 | すべて |
<> | 以外 | すべて |
<= | 以下 | number、date、timeofday、datetime |
< | 未満 | number、date、timeofday、datetime |
>= | 以上 | number、date、timeofday、datetime |
> | 超過 | number、date、timeofday、datetime |
プログラマの方は、『一致』の = と == 、『以外』の <> と != を間違えないように注意してくださいね。
数値データ（number）
数値データは、クエリ文字列の中では10進法で指定します。万が一、データが2進法や16進法で格納されている場合は10進法に戻しましょう。例：800、0.3
=QUERY(B2:I8, "WHERE G <= 30")
文字列データ（string）
文字列データは、クエリ文字列の中でシングルクォーテーションで囲む必要があります。例：'Eng'、'Dave'
=QUERY(B2:I8, "WHERE C <> 'Eng'")
日付データ（date）
日付データは、クエリ文字列の中でキーワード DATE の後に、yyyy-MM-dd 形式の文字列を続けて記述します。例: DATE '2008-03-18'
注意点はDATE '2008/03/18'やDATE '2008-3-18'とするのではなく、DATE '2008-03-18'と正確に記述しなければいけない点です。
=QUERY(B2:I8, "WHERE F >= DATE '2005-01-01'")
時刻データ（timeofday）
時刻データは、クエリ文字列の中でキーワード TIMEOFDAY の後に、HH:mm:ss[.SSS] 形式の文字列を続けて記述します。例: TIMEOFDAY '13:00:00'
ミリ秒部分はオプションなので、省略可能です。
=QUERY(B2:I8, "WHERE D = TIMEOFDAY '13:00:00'")
日時データ（datetime）
日時データは、クエリ文字列の中でキーワード DATETIME の後に、yyyy-MM-dd HH:mm:ss[.SSS] 形式の文字列を続けて記述します。例: DATETIME '2007-12-02 13:00:00'
ミリ秒部分はオプションなので、省略可能です。
=QUERY(B2:I8, "WHERE I >= DATETIME '2007-01-01 00:00:00'")
真偽値データ（boolean）
真偽値データは、クエリ文字列の中で数値データと同じようにシングルクォーテーションで囲まず、そのままTRUEまたはFALSEと記述します。
=QUERY(B2:I8, "WHERE H = TRUE")
高度な条件抽出（文字列比較演算子）
WHERE句には6つの比較演算子の他にも、もっと柔軟にデータを抽出することができる、5つの文字列比較演算子が存在します。
クエリ文も大きくは変わらず、"WHERE 列 文字列比較演算子 値"と記述します。
| 文字列比較演算子 | 意味 |
|---|---|
IS NULL | 欠損値抽出 |
IS NOT NULL | 有効データ抽出 |
CONTAINS | 部分一致抽出 |
LIKE | ワイルドカード抽出 |
MATCHES | 正規表現一致抽出 |
それぞれ見てみましょう。
IS NULL（欠損値抽出）
欠損値とはデータの欠損、つまり空白（null、NaN）のことを指しています。
文字列比較演算子の IS NULL を使うと、空白である人を抽出することができます。
このデータで説明すると、I列の上長になった日（seniorityStartTime）にデータの欠損が見受けられます。
=QUERY(B2:I8, "WHERE I IS NULL")
IS NOT NULL（有効データ抽出）
有効データとは欠損値ではないデータ、つまり空白（null、NaN）ではないことを指しています。
文字列比較演算子の IS NOT NULL を使うと、有効データのみを抽出することができます。
=QUERY(B2:I8, "WHERE I IS NOT NULL")
CONTAINS（部分一致抽出）
部分一致とは、指定した値の前後に他の値が含まれていても一致することを指しています。
文字列比較演算子の CONTAINS を使うと、部分一致抽出することができます。
=QUERY(B2:I8, "WHERE B CONTAINS 'Da'")
LIKE（ワイルドカード抽出）
文字列比較演算子の LIKE を使うと、2種類のワイルドカードを使って抽出できます。
| ワイルドカード | 意味 |
|---|---|
_ | 任意の1文字 |
% | 0文字または任意の1文字以上 |
'____': 任意の4文字's___': sで始まる4文字'a%': aで始まる文字（前方一致）'%n': nで終わる文字（後方一致）'a%e': aで始まり、eで終わる文字
=QUERY(B2:I8, "WHERE B LIKE 'D___'")MATCHES（正規表現一致抽出）
文字列比較演算子の MATCHES を使うと、正規表現を使ってデータを抽出することができます。
正規表現については、この記事の範疇を超えているので、以前書いた記事を参考にしてください。
知らないと損！正規表現を使ってスプレッドシートの文字列を便利に抽出する方法
=QUERY(B2:I8, "WHERE B MATCHES '.{3}e'")
複数条件と否定（論理演算子）
WHERE句では論理演算子を用いて、複数の条件を指定したり、否定したりすることができます。
| 論理演算子 | 意味 |
|---|---|
AND | 論理積 |
OR | 論理和 |
NOT | 否定 |
AND（論理積抽出）
論理演算子ANDは、クエリ文の中で"WHERE 条件式A AND 条件式B"と記述すると、AかつB、つまり条件式AとBを同時にみたすデータを抽出することができます。
=QUERY(B2:I8, "WHERE C = 'Eng' AND G >= 30")
OR（論理和抽出）
論理演算子ORは、クエリ文の中で"WHERE 条件式A OR 条件式B"と記述すると、AまたはB、つまり条件式AまたはBのいずれかをみたすデータを抽出することができます。
=QUERY(B2:I8, "WHERE NOT(C = 'Eng' AND G >= 30)")
NOT（否定抽出）
論理演算子NOTは、クエリ文の中で"WHERE NOT 条件式"と記述すると、条件式の否定、つまり条件をみたさないデータを抽出することができます。
NOTを使うと、ANDのときに例で紹介した『所属（dept）がエンジニア かつ 年齢（age）が30歳以降である』という条件は、『所属（dept）がエンジニア かつ 年齢（age）が30歳以降ではない』、つまり、『所属（dept）がエンジニアではない または 年齢（age）が30歳未満である』と変換されます。（＝ド・モルガンの法則）
=QUERY(B2:I8, "WHERE C = 'Eng' AND G >= 30")
WHERE句×スカラー関数
WHERE句とスカラー関数を組み合わせると、スカラー関数に適用させた値で絞り込みすることができます。
| スカラー関数 | 説明 | 使用できる列のデータ型 | 戻り値のデータ型 |
|---|---|---|---|
YEAR | 年の値を返す | date、datetime | number |
MONTH | ゼロベースの月の値を返す(0〜11) | date、datetime | number |
DAY | 日の値を返す | date、datetime | number |
HOUR | 時間の値を返す | datetime、timeofday | number |
MINUTE | 分の値を返す | datetime、timeofday | number |
SECOND | 秒の値を返す | datetime、timeofday | number |
MILLISECOND | ミリ秒の値を返す | datetime、timeofday | number |
QUARTER | 四半期の値を返す(1〜4) | date、datetime | number |
DAYOFWEEK | 曜日の値を返す、{1:日, … , 7:土} | date、datetime | number |
NOW | 現在の日時を返す | None | None |
DATEDIFF | 2つの日付の日数差を返す(時間は切り捨て) | date、datetime | number |
UPPER | 大文字を返す | string | string |
LOWER | 小文字を返す | string | string |
スカラー関数はこれだけありますが、WHERE句で使うのは日付や時刻を抜き出すYEAR・MONTH・DAY・HOUR・DAYOFWEEKくらいですね。
=QUERY(B2:I8, "WHERE YEAR(F) = 2005")
連載：QUERY関数完全攻略
英語で書かれた公式ドキュメントをベースに、マーケッターの誰もがQUERY関数を完全に使いこなすための連載記事を作成しました。分析するときの辞書代わりとして利用してください。
- データ分析関数『QUERY関数』の使い方をひと通り
- SELECT句でデータから列を抽出する
- WHERE句で条件に一致する行を抽出する
- GROUP BY句で行方向にグループ集計する
- PIVOT句で列方向にグループ集計、クロス集計する
- ORDER BY句で出力されるデータを並び替える
- SKIPPING句で出力されるデータの先頭行から任意の行数ずつスキップする
- LIMIT句で出力されるデータの行数を制限する
- OFFSET句で出力されるデータの先頭行から任意の行数スキップする
- LABEL句で出力される列名を変更する
- FORMAT句で出力される列のデータの書式を変更する
- ARRAYFORMULA関数とVLOOKUP関数で『OUTER JOIN』を実現する
- QUERY関数100本ノック
それと、筆者がQUERY関数についてまとめたスプレッドシートをこちらで公開しています。QUERY関数の理解がより深まること間違いなしです！
