【BigQuery】EXISTS関数をつかって、目的の行を抜き出してみた

概要

BigQueryでEXISTS関数を紹介します。

以下、Postgresqlドキュメントより引用

EXISTSの引数は、任意のSELECT文、つまり副問い合わせです。 副問い合わせはそれが何らかの行を返すか否かの決定のために評価されます。 もし1つでも行を返すのであれば、EXISTSの結果は「true（真）」となり、副問い合わせが行を返さない場合、EXISTSの結果は「false（偽）」となります。

データ紹介

テーブル1：item_table

user_id	item	price
A	みかん	50
A	もやし	19
A	りんご	100
B	りんご	100
B	りんご	90

テーブル2：item_price_m

product	price
もやし	19
ネギ	298
小松菜	298
みかん	50
りんご	100
メロン	3000

基本1：EXISTS関数の使い方

BigQueryへ取り込んでおいた二つのテーブルを使い、WHERE句で条件を絞ります。

BigQuery　基本１

SELECT
  *
FROM
  test1.item_table AS t1
WHERE
  EXISTS (
  SELECT
    product
  FROM
    test1.item_price_m AS t2
  WHERE
    t1.item = t2.product )
ORDER BY
  user_id ;

出力は以下です。

続いて、複数の条件をいれた場合です。

BigQuery　基本１　複数の条件の場合

SELECT
  *
FROM
  test1.item_table AS t1
WHERE
  EXISTS (
  SELECT
    product
  FROM
    test1.item_price_m AS t2
  WHERE
    t1.item = t2.product
    AND t1.price = t2.price)
ORDER BY
  user_id ;

出力は以下のようになります。

基本2：NOT EXISTS関数の使い方

EXISTS関数と対の条件になるNOT EXISTSを使ってみます。

NOT EXISTSを使った構文は以下になります。

BigQuery　基本２

SELECT
  *
FROM
  test1.item_table AS A
WHERE
  NOT EXISTS (
  SELECT
    item
  FROM
    test1.item_price_m AS B
  WHERE
    A.item = B.product
    AND A.price = B.price )
ORDER BY
   user_id;

出力は以下の様になりました。

条件に一致しない行の抽出がされました。

まとめ

EXISTS関数を使うと、テーブルに目的の行が存在するかどうかの存在チェックができます。
WHERE句のINと似ていますが、SQLの実行挙動が異なり、EXSITS関数の方が、早いと言われています。
是非、習得しておきたい関数だとおもいました。

参考サイト

https://cloud.google.com/bigquery/docs/reference/standard-sql/migrating-from-legacy-sql?hl=ja
https://www.postgresql.jp/document/10/html/functions-subquery.html