技術

[bigquery]テーブルの差集合を出す方法

PR

NOT EXISTS ：

　table1のレコードからtable2に存在する情報をKEYで比較して抽出対象外にする方法　

SELECT
  t1.id
FROM
  dataset.table AS t1
WHERE
  NOT EXISTS
  (
     SELECT
       id
     FROM
       dataset.table AS t2
     WHERE
       t1.id = t2.id
   )

EXCEPT DISTINCT ：

　table1のレコードからtable2に存在する情報を全項目で比較して抽出対象外にする方法　

SELECT 
    *
FROM 
    `dataset.table1`

EXCEPT DISTINCT

SELECT 
    *
FROM
    `dataset.table2`

EXCEPT DISTINCTは他のSQLだとoracleとかだとMINUS、mysqlなどだとEXCEPTという感じで使えましたが、bigqeuryの場合は、 EXCEPTとDISTINCTになります。

利用用途は、同じテーブルで片方は履歴用テーブル（バックアップなど）とデイリーで作成する情報を比較して不正なデータを探す。といった場合などに使ったりします。

▼応用的な使い方として、新旧テーブルのそれぞれの項目差分のみを抽出する方法もあります

目黒で働く分析担当の作業メモ
2023.12.25
[bigquery]修正前後のテーブル検証用クエリ
https://apl-py.com/tec/query_for_table_validation_before_and_after_modification
テーブルの改修を行った時の検証用クエリメモです旧テーブルと新テーブルについて月別で件数差分を出力する時のサンプルコードwith oldBase as ( select substr(createdAt, 1, 7) as ym, -- レコード作成日時情報（項目名は適宜変更） count(1) as old_count from dataset.oldtable　 -- 旧テーブルを指定 group by 1), newBase as ( select substr(createdAt, 1, 7) as ym, -- レコード作成日時情報（項目名は適宜変更） count(1) as new_count from dataset.newtable　 -- 新テーブルを指定 group by 1)se...
おすすめの記事