テック

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テーブル

categoryitem_idpurchases
dramaD00130
dramaD0025
dramaD00370
actionA0010
actionA00220
actionA0030
animeN00180
animeN00230

結果

categoryitem_idpurchases
actionA00220
animeN00180
dramaD00370

解説

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

  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

関連クエリ

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

ビッグデータ分析
この記事に関連する記事