BigQuery QUALIFYの使い方：ウィンドウ関数の結果を直接絞る重複排除と中央値

ユーザーごとの最新1行だけを取り出す。前日から値が変わった行だけを拾う。BigQueryでこの手の集計を書くと、ウィンドウ関数を計算するためだけのWITH句が積み上がります。QUALIFY句はその中間層を消すための構文で、要件はウィンドウ関数がQUALIFY句かSELECTリストのどちらかにあることだけ。ただし読み書きが軽くなる一方で、スキャン量と請求額には何も起きません。2026年8月17日時点の公式リファレンスとリリースノートをもとに、構文と評価順序、重複排除と差分抽出の型、中央値の出し方、そして使うべきでない場面までを整理します。

まとめ：QUALIFYはウィンドウ関数の結果だけを絞る句です

先に結論を置きます。QUALIFY句はウィンドウ関数の結果をフィルタする句で、公式の要件は「ウィンドウ関数がQUALIFY句またはSELECTリストに存在すること」の一点だけです。集約結果を絞るHAVINGの、ウィンドウ関数版だと考えると位置づけが早く掴めます。

評価はFROM、WHERE、GROUP BYと集約、HAVING、WINDOW、QUALIFY、DISTINCT、ORDER BY、LIMITの順に進みます。QUALIFYはWHEREより後ろなので、先にWHEREで行を落としておけばウィンドウ関数の計算対象そのものが減ります。

費用の話は期待とずれます。QUALIFYに書き換えてもスキャンされるバイト数は変わりません。オンデマンド課金は読み取った列のデータ量で決まるためで、削れるのはSQLの行数と中間テーブルの管理コストのほうでした。移植性も落ちます。QUALIFYはSQL標準ではなく、PostgreSQL・MySQL・SQL Serverでは動きません。

QUALIFY句の構文と評価順序をWHEREおよびHAVINGと対比して押さえる

まず構文の輪郭と、公式が明示している評価順序を固定します。ここが曖昧なままだと、後段の重複排除も分位点も組み立てられません。

要件はウィンドウ関数がQUALIFY句かSELECTリストにあることだけ

構文は QUALIFY bool_expression の一行で、真と評価された行だけが残ります。公式リファレンスは「ウィンドウ関数はQUALIFY句またはSELECTリストに存在することが必要」と定めていて、逆に言えばそれ以外の制約はありません。

公式の例は野菜の人気順位を絞るクエリです。SELECTリストで RANK() OVER (PARTITION BY category ORDER BY purchases DESC) as rank と別名を付け、QUALIFY rank <= 3 と書けば上位3件が返ります。別名をそのままQUALIFYで参照できる点が、WHEREにはない挙動でした。

ウィンドウ関数をSELECTリストに出さない書き方も認められています。SELECT item FROM Produce WHERE category = 'vegetable' QUALIFY RANK() OVER (PARTITION BY category ORDER BY purchases DESC) <= 3 のように、QUALIFY句の中で直接呼べば、順位列を結果に出さずに絞り込みだけができる。作業用の列を最後に消す手間がここで消えます。

評価はFROMからWHERE、WINDOW、QUALIFYの順に進む仕組み

公式リファレンスは、QUALIFY句を含むクエリの評価が「典型的にはこの順序で完了する」として9段階を挙げています。FROM、WHERE、GROUP BYと集約、HAVING、WINDOW、QUALIFY、DISTINCT、ORDER BY、LIMITの順です。あわせて「評価順序は構文順と常に一致するとは限らない」とも書かれています。

実務で効くのはWHEREとQUALIFYの前後関係です。WHEREはウィンドウ関数の計算前に働くため、対象期間や対象テナントで先に落としておけば、PARTITION BYで分割される行数が減ります。逆にQUALIFYへ期間条件を書くと、全期間にウィンドウ関数を掛けたあとで捨てる動きになる。

DISTINCTがQUALIFYより後ろに来る点も見落としやすいところです。重複排除をQUALIFYで済ませたつもりでDISTINCTを併記すると、二重に行が畳まれて件数の説明がつかなくなります。

NULLとFALSEの行が落ちる挙動とWHERE TRUEの要否

公式の記述は明快です。bool_expressionがTRUEと評価された行だけが含まれ、NULLまたはFALSEと評価された行は破棄されます。WHEREと同じ三値論理なので、NULLは「条件を満たさない」側へ倒れる。

この挙動はLAGやLEADと組み合わせたときに効いてきます。パーティションの先頭行は前の行を持たないためLAGがNULLを返し、QUALIFY value <> LAG(value) OVER (PARTITION BY id ORDER BY ts) と書くと初回行が丸ごと消える。初回を残したいなら、LAGの第3引数に既定値を渡すか、比較を IS DISTINCT FROM へ替えます。

もうひとつ、日本語の解説記事でよく見かける「QUALIFYを使うにはWHERE句が必要なのでWHERE TRUEを置く」という回避策があります。2026年8月17日時点の公式リファレンスにその要件の記載はなく、明示されている条件はウィンドウ関数の存在だけです。古い制約に由来する記述が残っているとみて、手元のプロジェクトで単独のQUALIFYが通るかを先に確かめてください。

2021年5月のプレビューから2022年2月の一般提供までの経緯

リリースノートのアーカイブに日付が残っています。2021年5月10日、BigQueryがPIVOT演算子・UNPIVOT演算子とあわせてQUALIFY句のサポートを発表し、この時点ではプレビューでした。一般提供に切り替わったのは2022年2月14日で、「Google Standard SQLで分析関数の結果をフィルタできるQUALIFY句が一般提供になった」と記載されています。

構文自体の出自はBigQueryではありません。Teradataが独自拡張として実装していたものが広がった経緯があり、現在はSnowflake・Databricks・DuckDBでも使えます。ただしSQL標準には入っていない。この一点が後述する移植性の判断に直結します。

ROW_NUMBERでの重複排除をQUALIFY句の一行へ畳む書き換え手順

QUALIFYがいちばん効くのは重複排除です。ログや連携データを扱う現場で、同じキーの行が複数回入ってくる状況は避けられません。

最新の1行を残す並べ替えと同着を切る第2キーの決め方と実装条件

型は決まっています。QUALIFY ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY updated_at DESC) = 1 と書けば、user_idごとに更新日時が最も新しい1行が残ります。PARTITION BYに重複の単位を、ORDER BYに残したい行の優先順位を置く構造です。

問題は同着です。更新日時がミリ秒まで同じ行が2本入っていると、ROW_NUMBERはどちらかに1を振りますが、どちらになるかは保証されません。実行のたびに結果が揺れるクエリになります。第2キーに取り込みジョブのタイムスタンプや連番、それも無ければ行のハッシュ値を置いて、順序を一意に確定させてください。

RANKとの取り違えにも注意が要ります。RANKは同着に同じ順位を与えるため、= 1 で絞っても同着の行が両方残る。1行に畳みたいならROW_NUMBER、同着を意図的に残したいならRANKという使い分けです。

サブクエリ版の3手をQUALIFYが1手に減らす具体的な差分

QUALIFYを使わない場合、書く手数は3つに増えます。WITH句でROW_NUMBERを付けた中間結果を作り、外側でWHERE rn = 1 と絞り、最後に作業列のrnを結果から取り除く。3つ目を忘れて、本来存在しない列がテーブルへ書き込まれる事故は珍しくありません。

作業列を落とすときに使うのが SELECT * EXCEPT (rn) です。SELECTリストのアスタリスクに続けてEXCEPT句を書くと、指定した列名だけが出力から外れます。列数の多いテーブルで作業列を1つ消すためだけに全列を列挙せずに済む書き方でした。

QUALIFYならこの3手が消えます。ウィンドウ関数をQUALIFY句の中に直接書けば、順位列がそもそも結果に現れないためです。中間のWITH句が減ることは、レビュー時に読む行数と、後任が壊す確率の両方に効いてきます。

集合演算子のEXCEPT DISTINCTと混同しやすい名前の重なり

同じEXCEPTでも、SELECTリストの中に書くものと、2つのクエリをつなぐ集合演算子は別物です。集合演算子のほうは query_expr EXCEPT DISTINCT query_expr の形で、左のクエリにあって右のクエリに無い行を返します。BigQueryが受け付けるのはEXCEPT DISTINCTだけで、他のデータベースにあるEXCEPT ALLは使えません。

用途は差分の突き合わせです。移行前後のテーブルで結果が一致するかを確かめるとき、両方向のEXCEPT DISTINCTを取って0件になるかを見ます。重複排除の作業列を消す SELECT * EXCEPT (rn) とは目的が違うため、コードレビューでは書かれている位置で読み分けてください。

なお、ネストした配列やSTRUCTを含むテーブルの重複排除では、展開の順番を先に決める必要があります。配列側の扱いはBigQuery UNNESTの書き方とGA4スキーマの展開に分けて整理しました。

LAGとの組み合わせで前後の行を比べ変化した行だけを取り出す

重複排除の次に出番が多いのが、時系列で前の行と比べる処理です。状態が切り替わった瞬間だけを抜き出したい、という要件はほぼ全ての業務データで発生します。

LAGの引数3つとORDER BYを省略できない窓の指定条件と構文

LAGの構文は LAG (value_expression [, offset [, default_expression]]) OVER over_clause です。offsetは何行前を見るかで、既定は1。default_expressionは対象行が存在しないときに返す値で、省略するとNULLが返ります。

窓の指定には制約があります。LAGのover_clauseはPARTITION BYが省略可能である一方、ORDER BYは必須です。「前の行」という概念は並び順が決まらないと定義できないためで、ここを書き忘れると構文エラーになります。時系列の並べ替えキーには、丸め済みの日付ではなく元のタイムスタンプ列を置いてください。丸め方の判断はBigQuery DATE_TRUNCの粒度指定とタイムゾーンにまとめています。

値が切り替わった行だけを残す差分抽出の書き方と初回行の扱いの判断基準

状態の変化点を拾う型はこうなります。QUALIFY status IS DISTINCT FROM LAG(status) OVER (PARTITION BY order_id ORDER BY updated_at) と書けば、直前の行と状態が違う行だけが残る。等価比較の <> ではなくIS DISTINCT FROMを使うのは、NULLを含む比較でも結果がTRUEかFALSEに確定するからです。

初回行の扱いをここで決めておきます。パーティションの先頭はLAGがNULLを返すため、<> で比較するとNULL評価となり行が捨てられます。IS DISTINCT FROMなら、NULLと値が異なるものとして扱われて先頭行が残る。状態遷移の履歴を作る用途では、初回の登録も1件の変化として残したい場面がほとんどでした。

前日比の算出も同じ形です。QUALIFY amount <> LAG(amount, 1, 0) OVER (PARTITION BY shop_id ORDER BY sales_date) のように第3引数へ0を渡せば、初日を「0からの変化」として扱えます。既定値を使うか、IS DISTINCT FROMで受けるか、業務上の意味で選んでください。

MEDIAN関数を持たないBigQueryで中央値と分位点を1行に畳む

「中央値を出したい」という要求は毎回のように来ますが、BigQueryにMEDIANという名前の関数はありません。ここでQUALIFYの出番が2度目に回ってきます。

PERCENTILE_CONTがウィンドウ関数としてしか呼べない仕様

正確な中央値を出す関数はPERCENTILE_CONTです。構文は PERCENTILE_CONT (value_expression, percentile [RESPECT NULLS]) OVER over_clause で、線形補間を伴って指定した分位点の値を返します。percentileは0から1の範囲のリテラルで、中央値なら0.5です。

注意すべきはover_clauseの中身です。PERCENTILE_CONTのwindow_specificationにはPARTITION BYしか書けず、ORDER BYもフレーム指定も取れません。集約関数としてGROUP BYと一緒に呼ぶこともできない。つまり結果は必ずパーティション内の全行に同じ値が並ぶ形で返ってきます。

既定ではNULLを無視し、RESPECT NULLSを付けたときだけNULLを補間の対象に含めます。NULL同士の補間はNULLを返し、NULLと非NULLの補間は非NULL側を返す、という細かい規定まで公式に明記されていました。

行数ぶん並ぶ同じ値をQUALIFYで1行へ畳む手順と代表行の条件

ウィンドウ関数である以上、100万行のテーブルに中央値を求めれば100万行が返ります。ここをDISTINCTで潰すのが定番でしたが、他の列も一緒に持ちたい場合はDISTINCTが効きません。

1. PERCENTILE_CONTをSELECTリストに置き、店舗やカテゴリ単位でPARTITION BYを指定する
2. 同じPARTITION BYでROW_NUMBERを計算し、QUALIFY句で1に等しい行だけを残す
3. 必要なら平均や件数の集約結果を同じクエリに並べ、1行で比較できる形にする

この手順なら、QUALIFY ROW_NUMBER() OVER (PARTITION BY shop_id) = 1 の一行でパーティションごとの代表行が確定します。平均と中央値を並べて外れ値の影響を見る、という分析でそのまま使える形です。

APPROX_QUANTILESとの分岐は正確さとデータ量のどちらを取るか

もうひとつの選択肢がAPPROX_QUANTILESです。APPROX_QUANTILES(expression, number) は近似の分位点境界を返す集約関数で、戻り値は要素数がnumber足す1の配列。中央値が欲しいなら第2引数に2を渡し、配列のオフセット1を取り出します。

使い分けの基準は2つです。第一に、集約関数なのでGROUP BYと素直に組み合わせられ、QUALIFYで畳む工程が要りません。第二に、名前のとおり近似値なので、監査や請求に使う数値には向きません。四分位を一度に4つ取りたいときは第2引数に4を渡せば1回のスキャンで済みます。

PERCENTILE_DISCは補間をせず、実際にデータへ存在する値のいずれかを返します。金額や個数のように「実在しない中間値を返されると困る」列では、こちらを選んでください。

QUALIFYを使う条件と使うべきでない場面を費用と移植性から切り分ける

ここは判断を言い切ります。QUALIFYは読みやすさのための構文であって、性能や費用の改善策ではありません。

スキャン量と請求額は書き換えても1バイトも減らない理由と確認方法

オンデマンド課金は、クエリが読み取った列のデータ量で決まります。QUALIFYへの書き換えで変わるのはSQLの構造だけで、読む列も読む行の範囲も同じ。したがって推定バイト数は一致します。

確かめ方は簡単です。書き換え前と書き換え後の両方をドライランに掛け、推定バイト数を並べてください。実行せずに課金対象のバイト数を見積もる手順はBigQueryの使い方とドライランでのスキャン量の確認で扱っています。スキャン量そのものを落としたいなら、効くのはパーティション分割と絞り込み列の設計のほうで、費目の内訳はBigQueryの料金：オンデマンドとEditionsの分岐点に整理しました。

スロット消費については、実行計画が同じ形に落ちれば差は出ません。差が出るとすれば、中間テーブルを物理的に書き出していたパイプラインをQUALIFY1本にまとめた場合で、これは書き込みと保存が消えたぶんの効果です。QUALIFY自体の効果ではない。

採用してよいのはBigQuery専用のクエリ資産に限るという線引き

採用条件を先に示します。そのSQLがBigQueryでしか動かないことが確定していて、かつ人間が読み書きする資産であること。dbtやDataformのモデル定義、分析チームが管理するクエリ集、定期実行のスケジュールクエリはここに当てはまります。中間層が1つ減る効果は、レビューと保守にそのまま返ってきました。

見送るべき場面も条件で切ります。第一に、PostgreSQLやMySQLと同じSQLを使い回す前提のアプリケーションコード。QUALIFYはSQL標準ではなく、これらのデータベースでは構文エラーになります。第二に、ORMやBIツールが自動生成するクエリへ手で差し込む場面で、ツール側のパーサーがQUALIFYを解釈できず落ちる。第三に、将来Snowflakeや別基盤へ移す可能性が具体的に議論されている資産です。SnowflakeとDuckDBはQUALIFYを持っていますが、移行先が確定していない段階で構文の互換性に賭ける理由はありません。

迷ったときの分岐は1つで足ります。そのクエリを書くのが人間か機械か。人間が読む資産なら使い、機械が生成する資産なら使わない。

外部へ依頼するときに先に渡す3点は重複の単位と優先順位と精度

集計処理の設計や実装を外部に委託するなら、着手前に渡す情報は3点に絞れます。第一に、重複を判定するキーの単位。第二に、同じキーの中で残す行の優先順位と、同着になったときのタイブレーク。第三に、分位点や集計値に求める精度が正確値か近似で足りるかです。

この3点が決まれば、PARTITION BYとORDER BYの列、ROW_NUMBERとRANKの選択、PERCENTILE_CONTとAPPROX_QUANTILESの分岐がほぼ自動的に決まります。曖昧なまま着手すると、結果が実行のたびに揺れる集計が本番へ入り、原因の切り分けに時間を取られる。データ基盤の設計から集計処理の実装までまとめて任せたい場合は、データ分析基盤構築・MLOps構築支援で要件整理から運用まで請け負っています。

見積書では、重複排除の判定ロジックを誰が決めるか、同着の扱いが仕様書に書かれているか、ドライランでスキャン量を検証する工程が入っているかを確認してください。製品そのものの採用可否から詰めたい段階なら、BigQueryとは：サーバーレスDWHの仕組みと採用判断から読み進めるほうが早く進みます。

よくある質問

QUALIFY句の実装で問い合わせが多い5点をまとめました。いずれも2026年8月17日時点の公式リファレンスとリリースノートで確認しています。

QUALIFY句はWHERE句なしでも使えますか？

現行の公式リファレンスが定める要件は、ウィンドウ関数がQUALIFY句またはSELECTリストに存在することだけで、WHERE句を必須とする記述はありません。日本語の解説記事にある「WHERE TRUEを置く」という回避策は、過去の制約に由来する記述とみられます。手元のプロジェクトでWHERE句を外したクエリが通るかを一度試し、通るなら余計なWHERE TRUEは削ってください。

QUALIFYとHAVINGはどう違いますか？

絞る対象が違います。HAVINGはGROUP BYで作られた集約結果を絞る句で、評価はGROUP BYの直後。QUALIFYはウィンドウ関数の結果を絞る句で、評価はWINDOW句の直後です。集約は行をまとめて件数を減らしますが、ウィンドウ関数は行数を保ったまま列を増やす。行を残したまま計算結果で絞りたいときがQUALIFYの領分です。

QUALIFYを使うとスキャン量や料金は減りますか？

減りません。オンデマンド課金は読み取った列のデータ量で決まるため、SQLの構造を変えても読むバイト数は同じです。読み取り量そのものを落としたいなら、パーティション分割と必須フィルタ、絞り込みに使う列でのクラスタリングを見直してください。

BigQueryに中央値を返すMEDIAN関数はありますか？

ありません。正確な中央値はPERCENTILE_CONTに0.5を渡して求めます。ただしこれはウィンドウ関数で、over_clauseにはPARTITION BYしか書けず、結果はパーティション内の全行に同じ値が並ぶ形で返る。1行に畳むにはQUALIFYでROW_NUMBERを1に絞るか、DISTINCTを使います。近似で足りるならAPPROX_QUANTILESに2を渡し、配列のオフセット1を取るほうが簡潔です。

QUALIFYで書いたクエリはPostgreSQLでも動きますか？

動きません。QUALIFYはSQL標準に含まれない拡張構文で、PostgreSQL・MySQL・SQL Serverはサポートしていません。もともとTeradataの独自拡張として実装されたもので、現在はBigQueryのほかSnowflake・Databricks・DuckDBで使えます。複数のデータベースで同じSQLを使い回す前提のコードでは、WITH句とWHEREの組み合わせで書いてください。

関連記事

• BigQueryとは：サーバーレスDWHの仕組みと採用判断：列指向とスキャン課金の前提
• BigQueryの使い方：コンソールとbqコマンドの初回操作：ドライランとGoogleSQLの基礎
• BigQueryの料金：オンデマンドとEditionsの分岐点：スキャン量が課金へ効く仕組み
• BigQuery UNNESTの書き方とGA4スキーマの展開：配列とネスト列の取り出し
• BigQuery DATE_TRUNCの粒度指定とタイムゾーン：時系列を丸める側の判断
• BigQuery無料枠の内訳とサンドボックスの制限：検証環境を無料で回す設計
• BigQuery APIの選び方とリトライ設計：集計結果を外へ渡す経路
• BigQuery MLでSQLだけで学習を回す手順：集計の先にある予測モデル
• データ分析基盤の構築：5層アーキテクチャと実装手順：収集から可視化までの全体像
• BigQuery導入の手順と体制・外注の判断：PoCから本番移行までの進め方