業務でデータの不整合を調査したり、リリース後のデータ確認を行ったりする際、毎回同じようなクエリを書いていることに気づきました。
そこで、いざという時に焦らずコピペで使えるよう、データ調査でよく使うBigQueryのクエリパターンをまとめました。
【タイムトラベル】過去のある時点のデータを参照する
データを誤って更新・削除してしまった時に使います。
バックアップテーブルからリストアしなくても、クエリに一行足すだけで過去の状態を参照できます。
※タイムトラベル期間（デフォルト7日間）内であれば参照可能です。
SELECT *
FROM `[プロジェクトID].[データセット名].[テーブル名]`
-- 以下の時刻時点のデータを表示します
FOR SYSTEM_TIME AS OF TIMESTAMP("2024-01-01 10:00:00+09"); -- ここに戻りたい日時を指定（JST）【重複チェック】プライマリキーが重複していないか探す
Step1：まずは重複の有無を検知する（GROUP BY）
「ユニーク（一意）であるべき項目」でグループ化し、数が2つ以上あるものを探します。
SELECT
`[重複チェックしたいカラム名]`, -- 例: order_id, user_code など
count(*) as duplicate_count -- 重複している件数
FROM `[プロジェクトID].[データセット名].[テーブル名]`
GROUP BY
`[重複チェックしたいカラム名]` -- SELECTと同じカラムを指定
HAVING count(*) > 1; -- 「件数が1より大きい（重複）」のみ抽出GROUP BY: 指定したカラムの値が同じデータを1行にまとめる機能です。
Step2：重複行の全カラムを表示して差異を確認する（QUALIFY）
重複が見つかった後、「行ごとの登録日時はいつか？」「他のカラムに違いはあるか？」を確認するために、行を1行にまとめずに全カラムを表示します。
SELECT *
FROM `[プロジェクトID].[データセット名].[テーブル名]`
-- 「指定したカラム」ごとに集計し、2件以上ある行だけをフィルタリングして表示
QUALIFY count(*) OVER(
PARTITION BY `[重複チェックしたいカラム名]`
) > 1
ORDER BY
`[重複チェックしたいカラム名]`; -- 重複行が隣り合うように並べ替えQUALIFY: 集計した結果に対して、WHERE のように条件を指定して絞り込む句です。
PARTITION BY: 行を1行にまとめず、グループ集計するための機能です。
ORDER BY: 結果を並べ替えます。これがないと重複行がバラバラに表示されて比較しにくいため、指定します。
【異常値検知】データの偏りや異常値を調べる
「特定の値しか入っていないはずなのにエラーが出る」といった場合、想定外の値（空文字やNULLなど）が入っていないか確認します。
カラムに入っている値ごとの件数を集計します。
SELECT
`[値の内訳を見たいカラム名]`, -- 例: status, category など
count(*) AS count -- その値が何件あるか
FROM `[プロジェクトID].[データセット名].[テーブル名]`
GROUP BY
`[値の内訳を見たいカラム名]` -- SELECTと同じカラムを指定
ORDER BY count DESC; -- 件数が多い順に並べる（異常値を見つけやすくする）DESC（降順）: 多い順に並べます。主要な値を把握したい時に使います。
ASC（昇順）: 少ない順に並ぶため、「数件だけ混じっているレアな異常値」を見つけたい時に便利です。
＜Tips＞異常値が見つかった場合の修正例
本来 ‘0’ であるべき場所に、空文字（何も表示されない行）が見つかった場合の対処法です。
UPDATE `[プロジェクトID].[データセット名].[テーブル名]`
SET `[修正したいカラム名]` = "0" -- 正しい値（例: "0"）に書き換える
WHERE `[修正したいカラム名]` = ""; -- 異常値（例: 空文字）を条件指定【環境比較】本番と検証でデータ件数に差がないか確認する
リリース作業の前後や、本番環境と検証環境で「データが正しく移行できているか」を比較します。
2つのクエリ結果を結合して表示します。もし count の数が合わなければ、データ移行漏れや更新処理の不備が疑われます。
※タイムトラベル期間（デフォルト7日間）内であれば参照可能です。
-- 環境A（例：本番）のデータ件数
SELECT '本番環境' AS env, count(*) AS count FROM `[プロジェクトID].[データセット名].[本番テーブル]`
UNION ALL
-- 環境B（例：検証）のデータ件数
SELECT '検証環境' AS env, count(*) AS count FROM `[プロジェクトID].[データセット名].[検証テーブル]`;最後に
これまではその場しのぎで都度調べてクエリを書いていましたが、こうしてまとめてみると それぞれの句の意味を改めて復習する良い機会になりました。
データ調査をスピード感をもって実施できるよう、皆様もぜひこのテンプレートを活用してみてください。