BigQuery分析関数 | OVER PARTITION BY 使い方

使い方

例として、もっとも人気のあるアイテムをカテゴリーごとに取得してみます。

SELECT
  * EXCEPT(rn)
FROM
(
  SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY purchases DESC) rn
  FROM
    data_set.item_purchases
)
WHERE
  rn = 1

item_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. カテゴリーごとにパーティション
2. パーティションごとに購入数の多い順で順位付け
3. 順位＝1のレコードだけ取得

①、②
ROW_NUMBER() OVER (PARTITION BY category ORDER BY purchases DESC) rn

③
WHERE rn = 1

最終的なクエリは以下となります。

SELECT
  * EXCEPT(rn)
FROM
(
  SELECT
    *,
    -- ①：カテゴリーごとにパーティション
    -- ②：パーティションごとに購入数の多い順に順位付け
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY purchases DESC) rn
  FROM
    data_set.item_purchases
)
WHERE
  -- ③：順位＝1のレコードだけ取得
  rn = 1

関連クエリ

• OVER PARTITION BY
• ROW_NUMBER
• QUALIFY 句
• EXCEPT 演算子

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

ビッグデータ分析

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

created by Rinker

• Amazon

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

created by Rinker

技術評論社

• Amazon