BigQuery | QUALIFY句の使い方

使い方

SELECT
  category,
  item_id,
  purchases
FROM
  dataset.purchase
WHERE
  true
QUALIFY RANK() OVER (PARTITION BY category ORDER BY purchases DESC) = 1

purchasesテーブル

category	item_id	purchases
drama	D001	30
drama	D002	5
drama	D003	70
action	A001	0
action	A002	20
action	A003	0
anime	N001	80
anime	N002	30

結果

category	item_id	purchases
action	A002	20
anime	N001	80
drama	D003	70

解説

以下の手順で取得しています。

1. categoryごとの購入数が高い順にランク付け
2. ランク１位の商品を取得

従来のクエリと比較して、直感的に理解しやすくなりました。

ネストせずにWHERE句に近いところで条件指定できる、またwith句またはサブクエリなしで条件指定でき、クエリがスッキリした印象ですね。

WITH ranked_data AS (
  SELECT
    category,
    item_id,
    purchases,
    RANK() OVER (PARTITION BY category ORDER BY purchases DESC) rn
  FROM
    dataset.purchase
)
SELECT 
  * EXCEPT (rn)
FROM
  ranked_data
WHERE
  rn = 1

SELECT
  category,
  item_id,
  purchases
FROM
  dataset.purchase
WHERE
  true
QUALIFY RANK() OVER (PARTITION BY category ORDER BY purchases DESC) = 1

制限事項

QUALIFY 句には、少なくとも次の句の 1 つと組み合わせて使用する必要があります。

• WHERE
• GROUP BY
• HAVING

WHERE true とすることでこの制限を抜けることができます。

プレビュー版のため改善される余地はあるかもしれませんね。（2021/06/27現在）

関連リンク

• QUALIFY 句
• EXCEPT
• RANK
• OVER
• PARTITION BY

より実務的なクエリを身につけるには「ビッグデータ分析・活用のためのSQLレシピ」がおすすめ。

ビッグデータ分析

ビッグデータ分析・活用のためのSQLレシピ

created by Rinker

• Amazon

図解即戦力　ビッグデータ分析のシステムと開発がこれ1冊でしっかりわかる教科書

created by Rinker

技術評論社

• Amazon