BigQueryで売上の前日比や前週比を計算したり、ユーザーの最新ステータスを取得しようとした際、同じテーブルを自己結合（SELF JOIN）してクエリが非常に重くなってしまった経験はないでしょうか。
また、サブクエリやWITH句が何層にもネストしてしまい、後からSQLを読み返すのが困難になっているケースも少なくありません。
BigQueryでは、ウィンドウ関数の「LEAD」「LAG」「ROW_NUMBER」と、独自の「QUALIFY句」を組み合わせることで、自己結合や深いサブクエリを一切使わずに、高速かつシンプルな1つのクエリで前後のデータ比較や重複排除が可能です。
この記事では、BigQueryにおけるウィンドウ関数とQUALIFY句の基本的な使い方から、実務で初心者が必ずハマる「日付欠損の罠」の回避策、PostgreSQLやMySQLなど他製品との書き方の違いまで、実務ですぐに使えるサンプルクエリ付きで詳しく解説します。
この記事で行うこと
前提条件・対象読者
なぜ自己結合（SELF JOIN）を避けるべきなのか？
時系列データの前後の比較を行う際、昔ながらのRDBMS手法として「同じテーブルを日付マイナス1日で結合する（自己結合）」という書き方がよく使われていました。
しかし、BigQueryのような分散並列処理型のクラウドデータウェアハウスにおいて、自己結合は重大なパフォーマンス低下とコスト増を招くアンチパターンとされています。
自己結合がBigQueryでアンチパターンとされる理由
自己結合を行うと、BigQueryの内部では以下のような問題が発生します。
- データの全シャッフルが発生する BigQueryはデータを複数のノードに分散して保存・処理しています。結合キー（日付やユーザーID）で結合を行う場合、ノード間で大量のデータをネットワーク経由で転送（シャッフル）し直す必要があります。
- 計算量が爆発的に増加する 結合条件によってはデータの組み合わせが急増し、スロット時間（CPUリソース消費量）が跳ね上がります。データ量が数千万〜数億行に達すると、「Resources exceeded during query execution: Not enough memory for shuffle of data」というメモリ不足エラーでクエリが停止する原因になります。
- スキャン費用とクエリ課金 テーブルを2回読み込むことになるため、オンデマンド料金モデルの場合、余計なデータスキャンが発生してコストが無駄に増大します。
ウィンドウ関数が高速かつ低コストな仕組み
一方、LEADやLAGといったウィンドウ関数を使う場合、BigQueryはテーブルを1回スキャンするだけで済みます。
指定したパーティション（PARTITION BY）ごとにデータをソートし、同一ノード上でメモリ内の前後の行を直接参照するため、ノード間の重いデータシャッフルを最小限に抑えられます。その結果、自己結合と比べて圧倒的に高速かつ低コストで処理が完了します。
ウィンドウ関数とQUALIFY句の基本
まずは、前後のデータを参照するLEAD・LAG関数と、行番号を振るROW_NUMBER関数、そしてそれらを絞り込むQUALIFY句の基本構文を押さえましょう。
前後のデータを参照する「LEAD」と「LAG」の構文
- LAG: 現在の行より「前（過去）」の行の値を取得する
- LEAD: 現在の行より「後（未来）」の行の値を取得する
構文は以下の通りです。
-- 1行前の値を取得
LAG(対象列, 1) OVER (PARTITION BY グループ列 ORDER BY 並び順列)
-- 1行後の値を取得
LEAD(対象列, 1) OVER (PARTITION BY グループ列 ORDER BY 並び順列)
第2引数のオフセット（何行離れているか）を省略した場合は「1」がデフォルトになります。第3引数には、前後の行が存在しない場合（先頭行や末尾行）のデフォルト値（デフォルトはNULL）を指定できます。
重複排除に欠かせない「ROW_NUMBER」の構文
グループごとに指定した順序で1から始まる連番を振る関数です。
ROW_NUMBER() OVER (PARTITION BY グループ列 ORDER BY 並び順列 DESC)
例えば、ユーザーIDごとに更新日時が新しい順にソートして1を振れば、各ユーザーの最新レコードを特定できます。
サブクエリを不要にする「QUALIFY句」とは？
通常のSQLでは、WHERE句の中でウィンドウ関数を使うことはできません。
そのため、従来は一度サブクエリやWITH句で「rn」などの順位列を作成してから、外側のクエリで「WHERE rn = 1」とフィルタリングする必要がありました。
BigQueryの「QUALIFY句」を使うと、サブクエリを書くことなく、ウィンドウ関数の条件を直接指定して行を絞り込むことができます。
-- サブクエリなしで最新1件を取得できる
SELECT
user_id,
user_name,
updated_at
FROM `my_project.my_dataset.users`
QUALIFY ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY updated_at DESC) = 1;
このように、SELECT句にROW_NUMBER()を書く必要すらなく、1つのクエリで完結します。
SQLの実行順序（なぜWHERE句ではなくQUALIFY句なのか）
なぜWHERE句でウィンドウ関数が使えず、QUALIFY句が必要なのでしょうか。その理由は、SQLの内部的な評価順序にあります。
SQLは記述順（SELECT → FROM → WHERE…）ではなく、以下の順番で実行されます。
- FROM（テーブルの取得・結合）
- WHERE（行の事前絞り込み）
- GROUP BY（グループ化）
- HAVING（集計後の絞り込み）
- WINDOW（ROW_NUMBER、LEAD、LAGなどの計算）
- QUALIFY（ウィンドウ関数の結果による絞り込み）
- SELECT（出力列の決定・展開）
- DISTINCT（重複の除去）
- ORDER BY（最終出力のソート）
- LIMIT（取得件数の制限）
WHERE句は2番目に評価されるため、5番目で計算されるウィンドウ関数の結果を評価することはできません。
BigQueryのQUALIFY句は、5番目のWINDOW処理の直後（6番目）に差し込まれる絞り込み専用の句であるため、ウィンドウ関数の計算結果をそのまま条件に指定できるのです。
【比較表】QUALIFY句が使えるDB・使えないDB一覧
QUALIFY句は非常に強力ですが、すべてのデータベースで使えるわけではありません。開発現場で別のDBを扱う場合や、他DBからBigQueryへ移行する際は以下の対応状況を把握しておくと便利です。
| データベース | QUALIFY句の対応 | 備考 |
|---|---|---|
| Google BigQuery | 対応 | 2021年より標準サポート |
| Snowflake | 対応 | 標準サポート |
| Databricks (Spark SQL) | 対応 | Spark 3.2以降でサポート |
| DuckDB | 対応 | ローカル分析エンジンでも利用可能 |
| Oracle Database | 対応 | Oracle 23ai / 23c以降で対応 |
| PostgreSQL | 非対応 | CTE（WITH句）またはサブクエリが必要 |
| MySQL | 非対応 | MySQL 8.0以降でも非対応（CTEが必要） |
| Amazon Redshift | 非対応 | CTEまたはサブクエリが必要 |
| Microsoft SQL Server | 非対応 | CTEまたはサブクエリが必要 |
QUALIFY句が使えないPostgreSQLやMySQLでは、以下のようにWITH句（CTE）で一度ランキングを計算してから外側でWHERE指定するのが標準的な書き方になります。
-- PostgreSQLやMySQLでの標準的な書き方
WITH ranked AS (
SELECT
user_id,
user_name,
updated_at,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY updated_at DESC) AS rn
FROM users
)
SELECT
user_id,
user_name,
updated_at
FROM ranked
WHERE rn = 1;
実践パターン1：日付欠損に対応した安全な「売上前日比・前週比」集計
実務で最も頻出する「売上推移の前日比計算」を実装します。ここでは初心者が必ず直面する落とし穴とその回避策を取り上げます。
初心者がハマる「前データ日比」の罠（土日・欠損日の誤認）
よくある失敗例として、単に売上テーブルをORDER BY dateしてLAGを適用してしまうケースがあります。
-- 危険なクエリ例（日付に抜けがあると狂う）
SELECT
order_date,
sales_amount,
LAG(sales_amount) OVER (ORDER BY order_date) AS prev_sales
FROM daily_sales;
もし「土日祝日は店舗が休みでデータが存在しない」、あるいは「平日に売上が0件の日がありレコード自体が記録されていない」場合、金曜日の次の行である月曜日は「金曜日の売上」を前日売上として取得してしまいます。
つまり、「前日比」を計算しているつもりが、実態は「直近の営業日比（3日前比）」になってしまうという深刻な集計ミスが発生します。
これを防ぐための2つの安全策を紹介します。
対策A：DATE_DIFFで前日データのみを安全に比較する
カレンダーテーブルを用意せず、現在のテーブルのまま安全に計算したい場合は、LAGで「直前の日付」も一緒に取得し、DATE_DIFFで本当に1日前かどうかを検証します。
WITH daily_sales AS (
-- テスト用モックデータ（2026-09-02と2026-09-05が欠損しているデータ）
SELECT DATE '2026-09-01' AS order_date, 10000 AS amount UNION ALL
SELECT DATE '2026-09-03', 15000 UNION ALL
SELECT DATE '2026-09-04', 12000 UNION ALL
SELECT DATE '2026-09-06', 18000
)
SELECT
order_date,
amount AS current_amount,
-- 1行前の日付と売上を取得
LAG(order_date) OVER (ORDER BY order_date) AS prev_date,
LAG(amount) OVER (ORDER BY order_date) AS raw_prev_amount,
-- 日付差がちょうど1日の場合のみ前日売上として採用し、それ以外はNULLにする
CASE
WHEN DATE_DIFF(order_date, LAG(order_date) OVER (ORDER BY order_date), DAY) = 1
THEN LAG(amount) OVER (ORDER BY order_date)
ELSE NULL
END AS safe_prev_amount,
-- 前日比成長率（%）
ROUND(
SAFE_DIVIDE(
amount - CASE WHEN DATE_DIFF(order_date, LAG(order_date) OVER (ORDER BY order_date), DAY) = 1 THEN LAG(amount) OVER (ORDER BY order_date) END,
CASE WHEN DATE_DIFF(order_date, LAG(order_date) OVER (ORDER BY order_date), DAY) = 1 THEN LAG(amount) OVER (ORDER BY order_date) END
) * 100, 1
) AS growth_rate_pct
FROM daily_sales
ORDER BY order_date;
この方法を用いれば、9月3日の前日は存在しないためsafe_prev_amountはNULLとなり、誤った前日比が算出されるのを完全に防ぐことができます。
対策B：GENERATE_DATE_ARRAYでカレンダー結合して0埋めする
欠損日も含めて「連続した日付のタイムライン」として前日比をグラフ化したい場合は、BigQueryのGENERATE_DATE_ARRAY関数で日付マスタを動的生成し、LEFT JOINするのが最も確実です。
WITH raw_sales AS (
-- 元の売上データ（日付飛びあり）
SELECT DATE '2026-09-01' AS order_date, 10000 AS amount UNION ALL
SELECT DATE '2026-09-03', 15000 UNION ALL
SELECT DATE '2026-09-04', 12000
),
calendar AS (
-- 期間内の全日付を1日刻みで生成
SELECT calendar_date
FROM UNNEST(GENERATE_DATE_ARRAY('2026-09-01', '2026-09-05', INTERVAL 1 DAY)) AS calendar_date
),
filled_sales AS (
-- カレンダーと左外部結合し、売上のない日はCOALESCEで0埋め
SELECT
c.calendar_date,
COALESCE(s.amount, 0) AS amount
FROM calendar c
LEFT JOIN raw_sales s ON c.calendar_date = s.order_date
)
SELECT
calendar_date,
amount,
-- 連続した日付テーブルになっているため、単純なLAG(1)で正確な前日売上が取れる
LAG(amount, 1) OVER (ORDER BY calendar_date) AS prev_day_amount,
-- 前日との差分
amount - LAG(amount, 1) OVER (ORDER BY calendar_date) AS diff_amount,
-- 前日比成長率（%）
ROUND(
SAFE_DIVIDE(amount - LAG(amount, 1) OVER (ORDER BY calendar_date), LAG(amount, 1) OVER (ORDER BY calendar_date)) * 100,
1
) AS growth_rate_pct
FROM filled_sales
ORDER BY calendar_date;
すべての連続した日付が存在するため、単純なLAG(amount, 1)で必ず「本来の前日」が取れるようになります。
ゼロ除算エラーを防ぐSAFE_DIVIDEの併用テクニック
SQLで比率を計算する際、前日の売上が0円だった場合に通常の除算記号（/）を使うと、「division by zero」エラーでクエリ全体が落ちてしまいます。
BigQueryでは「SAFE_DIVIDE(分子, 分母)」関数が用意されています。分母が0またはNULLのときはエラーを発生させずに安全にNULLを返してくれるため、成長率計算では必須のテクニックです。
実践パターン2：ユーザー行動ログの「直前・直後アクション・離脱」分析
Webサイトやアプリのアクセスログ（GA4イベントデータなど）を分析する際、ユーザーが特定のアクションの「直前に何を見ていたか」「直後にどこへ遷移したか」を調べるケースです。
自己結合を行うとアクセスログのような超大規模テーブルは一瞬でスロット上限に達しますが、LAGとLEADを使えば一撃で集計できます。
WITH web_logs AS (
-- テスト用アクセスログ（ユーザーごとの行動履歴）
SELECT 'user_A' AS user_id, TIMESTAMP '2026-09-11 10:00:00' AS event_time, 'home' AS page_name UNION ALL
SELECT 'user_A', TIMESTAMP '2026-09-11 10:02:00', 'product_list' UNION ALL
SELECT 'user_A', TIMESTAMP '2026-09-11 10:05:00', 'cart' UNION ALL
SELECT 'user_A', TIMESTAMP '2026-09-11 10:08:00', 'purchase_complete' UNION ALL
SELECT 'user_B', TIMESTAMP '2026-09-11 11:00:00', 'home' UNION ALL
SELECT 'user_B', TIMESTAMP '2026-09-11 11:03:00', 'cart'
)
SELECT
user_id,
event_time,
page_name AS current_page,
-- 直前の閲覧ページ
LAG(page_name) OVER (PARTITION BY user_id ORDER BY event_time) AS prev_page,
-- 次の閲覧ページ
LEAD(page_name) OVER (PARTITION BY user_id ORDER BY event_time) AS next_page,
-- 滞在時間（秒）：次のアクションまでの秒数
TIMESTAMP_DIFF(
LEAD(event_time) OVER (PARTITION BY user_id ORDER BY event_time),
event_time,
SECOND
) AS stay_duration_seconds
FROM web_logs
ORDER BY user_id, event_time;
次のアクションがないユーザー（離脱ログ）をQUALIFYで一発抽出する
もし「カート画面（cart）まで進んだものの、購入完了に至らずそのままサイトを離脱した（＝次のページが存在しない）イベントだけを抽出したい」場合、QUALIFY句を組み合わせると極めてシンプルになります。
WITH web_logs AS (
SELECT 'user_A' AS user_id, TIMESTAMP '2026-09-11 10:05:00' AS event_time, 'cart' AS page_name UNION ALL
SELECT 'user_A', TIMESTAMP '2026-09-11 10:08:00', 'purchase_complete' UNION ALL
SELECT 'user_B', TIMESTAMP '2026-09-11 11:03:00', 'cart'
)
SELECT
user_id,
event_time,
page_name
FROM web_logs
WHERE page_name = 'cart'
-- 次のイベントが存在しない（LEADがNULL）行だけをQUALIFYで絞り込む
QUALIFY LEAD(event_time) OVER (PARTITION BY user_id ORDER BY event_time) IS NULL;
サブクエリを作成することなく、「cart画面で離脱したレコード」のみを即座に抽出できます。
実践パターン3：更新履歴・変更ログから「最新の1レコード」を抽出
実務のデータパイプラインでは、更新ログやCDC（Change Data Capture）テーブルから、IDごとの最新ステータスだけを取り出す重複排除（デデュプリケーション）が頻繁に求められます。
ROW_NUMBER()とQUALIFY句を組み合わせた重複排除
WITH user_updates AS (
-- 会員ステータスの更新履歴
SELECT 101 AS user_id, '佐藤' AS user_name, '無料会員' AS plan, TIMESTAMP '2026-08-01 10:00:00' AS updated_at UNION ALL
SELECT 101, '佐藤', '有料会員', TIMESTAMP '2026-09-01 15:00:00' UNION ALL
SELECT 102, '鈴木', '無料会員', TIMESTAMP '2026-09-05 12:00:00' UNION ALL
SELECT 101, '佐藤', 'プレミアム会員', TIMESTAMP '2026-09-10 18:30:00'
)
SELECT
user_id,
user_name,
plan,
updated_at
FROM user_updates
-- 各ユーザーIDごとに更新日時が最新の1件のみを抽出
QUALIFY ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY updated_at DESC) = 1;
実行結果は以下のようになり、ID:101の佐藤さんは最新の「プレミアム会員」の1レコードだけが残ります。
| user_id | user_name | plan | updated_at |
|---|---|---|---|
| 101 | 佐藤 | プレミアム会員 | 2026-09-10 18:30:00 UTC |
| 102 | 鈴木 | 無料会員 | 2026-09-05 12:00:00 UTC |
他DB（WITH句やFROM句サブクエリ）とのコード比較
このクエリをPostgreSQLやMySQLなどのQUALIFY句非対応DBで書く場合、以下のように行数が約2倍に増えてしまいます。
-- PostgreSQL等で書く場合（サブクエリが必須）
SELECT
user_id,
user_name,
plan,
updated_at
FROM (
SELECT
user_id,
user_name,
plan,
updated_at,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY updated_at DESC) AS rn
FROM user_updates
) sub
WHERE sub.rn = 1;
BigQueryのQUALIFY句を使うことで、ネストが解消され、可読性と保守性が大幅に向上することが実感できます。
QUALIFY句を使う際の注意点とトラブルシューティング
日常業務でQUALIFY句をフル活用するにあたり、知っておくべき注意点と対処法をまとめました。
BIツール（Looker Studio等）のカスタムクエリでの考慮事項
Looker StudioなどのBIツールからBigQueryへ直接カスタムクエリを発行する際、ツールの仕様によってQUALIFY句を含むクエリが構文エラーとして弾かれたり、意図しないサブクエリでラップされてパフォーマンスが落ちることがあります。
BIツール側でエラーが出る場合は、以下の対処法が有効です。
- BigQuery側で「ビュー（VIEW）」または「テーブル」として保存し、BIツールからはそのビューを参照する
- クエリ全体を一度かっこで囲んでサブクエリ化する
大規模テーブルでのORDER BYによるメモリ消費への配慮
ウィンドウ関数の中でORDER BYを指定する場合、ソート処理のためにメモリを消費します。
特にPARTITION BYを指定せず、テーブル全体に対してORDER BYしてしまうと、1つの分散ノードに全行が集約されてしまい、メモリ不足（Resources exceeded）の原因になります。
億単位の大規模テーブルを扱う際は、必ずPARTITION BYでパーティションキー（ユーザーIDや日付など）を指定して並列分散させるように設計してください。
よくある質問（FAQ）
Q. QUALIFY句はWHERE句の代わりに何でも使える？
A. 使えません。QUALIFY句はあくまで「ウィンドウ関数の計算結果に基づいてフィルタリングを行うため」の句です。通常のカラム値の絞り込み（例: WHERE status = ‘active’ など）は、WINDOW計算の前にデータを削減できるWHERE句に書くのが原則です。不要な行をWHERE句で事前に落としてからQUALIFY句を実行する方が、BigQueryのリソース消費を最小化できます。
Q. LEADやLAGのデフォルト値（NULL以外）はどう指定する？
A. 第3引数に指定します。例えば、1行前の値が存在しない場合に0を入れたい場合は LAG(amount, 1, 0) OVER (…) と記述します。
まとめ
BigQueryにおけるウィンドウ関数（LEAD / LAG / ROW_NUMBER）とQUALIFY句のポイントを整理します。
これらを使いこなすことで、クエリの実行速度向上、BigQuery利用コストの削減、そして誰が見てもわかりやすい清潔なSQLの保守が実現できます。
次に読むおすすめ記事
BigQueryで日付欠損を埋めるためのカレンダーテーブル生成テクニックや、結合の仕組みをより深く理解したい方は、以下の記事もあわせて参考にしてください。
BigQuery SQLサンプル － １ヶ月分の日付レコード作成 https://snowsystem.net/bigquery/sql-002/
BigQuery SQLサンプル － 相関クロス結合 https://snowsystem.net/bigquery/sql-001/
