BigQueryレシピ

BigQuery | QUALIFY句の使い方

使い方

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

purchasesテーブル

categoryitem_idpurchases
dramaD00130
dramaD0025
dramaD00370
actionA0010
actionA00220
actionA0030
animeN00180
animeN00230

結果

categoryitem_idpurchases
actionA00220
animeN00180
dramaD00370

解説

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

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

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

ネストせずに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現在)

関連リンク

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

ビッグデータ分析
created by Rinker
¥4,180
(2022/09/26 16:59:15時点 Amazon調べ-詳細)

created by Rinker
技術評論社
¥2,398
(2022/09/26 18:09:01時点 Amazon調べ-詳細)