ウインドウ関数について(Window関数について）

こんにちは

本日はウインドウ関数についてサンプルを用いて色々操作していきます。

よくウインドウ関数を使うシーンとして
･同一ユーザの前回･次回の予約日を追加したい
･同一ユーザーの最終予約日を全ての行に追加したい
･同一ユーザーの属性を判定するが、JOINを使わずデータテーブルから計算する

等々活用するシーンは多岐に渡っていて、組合せることも可能ですので
色々と試していきます。

まずはいつも通り操作可能なデータテーブルを作成します。

-- 
CREATE OR REPLACE TABLE
`BQを使ってるプロジェクト名.データセット名.テーブル名` (
USER_NAME STRING,
SHOP_NAME STRING,
ITEM STRING,
PRICE INT64,
BUY_DATE DATE,
);

-- 作成したテーブルに値を設定していく
INSERT INTO
`BQを使ってるプロジェクト名.データセット名.テーブル名`
(USER_NAME, SHOP_NAME, ITEM, PRICE, BUY_DATE)
VALUES
("ホゲ太郎", "水屋", "水2リットル10本入り", 1080, "2021-08-24"),
("ホゲ二郎", "肉屋", "牛ランプ肉500グラム", 3500, "2021-07-21"),
("ホゲ太郎", "魚屋", "お刺身盛り合わせ5種", 2160, "2021-08-15"),
("ホゲ太郎", "八百屋", "きんぴらごぼうセット", 324, "2021-07-31"),
("ホゲ二葉", "水屋", "水2リットル10本入り", 108, "2021-06-01"),
("ホゲ二郎", "八百屋", "ブロッコリー", 432, "2021-08-22"),
("ホゲ三木", "肉屋", "牛切り落とし300グラム", 1000, "2021-09-01"),
("ホゲ二郎", "魚屋", "お刺身盛り合わせ5種", 108, "2021-09-06")

作成したテーブルを用いていくつかの課題をこなしていきましょう


1.そのユーザーがトータル何回購入したかを日を追うごとに積み上げる

今回は購入日を昇順にその購入が何回目なのかユーザー毎に積み上げていきます。

SELECT
USER_NAME,
SUM(1) OVER (PARTITION BY USER_NAME ORDER BY BUY_DATE) AS TUMIAGE
FROM `BQを使ってるプロジェクト名.データセット名.作成したテーブル名`


2.ユーザーが水屋で何回購入したのかを日を追うごとに積み上げる

水屋での購入時のみ1を日毎に足していくというのをウインドウ関数を使ってやってみましょう。

SELECT
USER_NAME,
SUM(CASE WHEN SHOP_NAME = "水屋" THEN 1 ELSE 0 END) OVER (PARTITION BY USER_NAME ORDER BY BUY_DATE)
FROM `BQを使ってるプロジェクト名.データセット名.作成したテーブル名`

または

SELECT
USER_NAME,
SUM(IF(SHOP_NAME = "水屋", 1, 0)) OVER (PARTITION BY USER_NAME ORDER BY BUY_DATE)
FROM `BQを使ってるプロジェクト名.データセット名.作成したテーブル名`


3.ユーザーが次に購入した日がいつかをレコードに追加する
よく使うシーンとして次に購入するのがどの程度間が開くのかを集計するときに使います。

SELECT
*,
LEAD(BUY_DATE, 1) OVER (PARTITION BY USER_NAME ORDER BY BUY_DATE)
FROM `BQを使ってるプロジェクト名.データセット名.作成したテーブル名`

1 と書いている箇所を 2 にすると次の次の購入日をそのレコードに持ってきます。
前回の購入日の場合はLEADをLAGにすると参照することが可能です。


4.最も最近の購入日をそのレコードに追加する
ユーザーごとに最も最新の購入日がいつだったのか確認する

SELECT
*,
FIRST_VALUE(BUY_DATE) OVER (PARTITION BY USER_NAME ORDER BY BUY_DATE DESC)
FROM `BQを使ってるプロジェクト名.データセット名.作成したテーブル名`

または

SELECT
*,
LAST_VALUE(BUY_DATE) OVER (PARTITION BY USER_NAME ORDER BY BUY_DATE)
FROM `BQを使ってるプロジェクト名.データセット名.作成したテーブル名`


ユーザーと最新の購入日だけを抽出したい場合は

SELECT DISTINCT
USER_NAME,
LAST_VALUE(BUY_DATE) OVER (PARTITION BY USER_NAME ORDER BY BUY_DATE)
FROM `BQを使ってるプロジェクト名.データセット名.作成したテーブル名`


ウインドウ関数についていくつかの活用例を挙げてみました。
ウインドウ関数は単体で活用することも条件として使う事も可能なので
どのような活用ができるのか確認してみてください。