2022.01.11

BigQuery解説 ~Window Functionによる解析~

次世代システム研究室の Y.I です。　Google Cloud Platform (GCP) の BigQueryについてまとめます。今回は SQL の group by だけでは難しい集計分析を可能とする Window Function についてまとめます。BigQueryでは早い段階から、 MySQL も 8 から対応して、Oracleは確か15年以上前から Window Fucntion に対応していた記憶があります。筆者は MySQL 5.7 以前を使うことが多かっため、 Window Function をあまり使わずにいましたが、改めて学び直したので内容をまとめます。

目次
  1. Window Function について
    1. 概要
    2. 基本構文
  3. 代表的な Window Function 紹介
  4. 集計/分析例
    1. ランキング
    2. ゴールデン導線
    3. 前後のレコードの値を取得
    4. 初回/最後の値
    5. 指定した順番の値
    6. デシル分析
  6. 最後に

Window Function について

概要

Window Function は、指定したグループに対して元のレコードや値を持ったまま集計できる関数です。 sum()やavg()など集計関数を group by で取得すると group by で指定したカラムでレコードがまとめられますが Window Function はレコードや値を保持したまま集計結果を取得することができます。
- テーブル
+----+-------+----------+
| id | name  | quantity |
+----+-------+----------+
|  1 | name1 |        1 |
|  1 | name2 |        1 |
|  1 | name3 |        1 |
|  2 | name1 |        1 |
+----+-------+----------+

- group by sum()
レコード数が2件にまとめられ、quantityが合計されている
+----+-------+
| id | sum_q |
+----+-------+
|  1 |     3 |
|  2 |     1 |
+----+-------+

- window function sum()
レコード数が4件のままで、各レコードにquantityが合計されたカラムが追加されている
+----+-------+----------+-------+
| id | name  | quantity | sum_q |
+----+-------+----------+-------+
|  1 | name1 |        1 |     3 |
|  1 | name2 |        1 |     3 |
|  1 | name3 |        1 |     3 |
|  2 | name1 |        1 |     1 |
+----+-------+----------+-------+

基本構文

OVERで集計するグループ(partition by)、並び順(order by)、該当のレコード範囲(Window)を指定して、先頭の関数を計算します。
partition by は group by と同じで集計するグループを指定します。
関数 OVER (
  PARTITION BY 
  ORDER BY
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

windowについて

Window は partition by で使用するレコード範囲をします。
ROWS BETWEEN {FROM} AND {TO}
FROM と TO にはこちらを指定できます。
UNBOUNDED PRECEDING =上限（際限なく前に続く）
N PRECEDING =現在の行からN行前
CURRENT ROW　=現在の行
N FOLLOWING　=現在の行からN行後
UNBOUNDED FOLLOWING　=下限（際限なく後に続く）
なお、window を指定しない場合は、ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW が指定されたものとして動作します。

代表的な Window Function 紹介

  1. RANK ランキングを取得する（1位が2件の場合2位をスキップして次は3位）
  2. DENSE_RANK　ランキングを取得する（1位が2件の場合スキップせずに次は2位）
  3. ROW_NUMBER　シーケンシャル番号を取得する
  4. NTILE　均等な数に分割する
  5. FIRST_VALUE　最初の値を取得する
  6. LAST_VALUE　最後の値を取得する
  7. NTH_VALUE　N番目の値を取得する
  8. LEAD　直後の値を取得する
  9. LAG　直前の値を取得する
  10. PERCENTILE_CONT　指定のパーセント値を取得する/0.5の場合中央値
  11. SUM　合計を取得する
  12. AVG　平均を取得する

集計/分析例

ランキング

各レコードを保持したままランキングを取得することができます。
- score 毎にランキングする
WITH DUAL AS (
  select 1 as id, 100 as score
  union all
  select 2 as id, 90 as score
  union all
  select 3 as id, 90 as score
  union all
  select 4 as id, 97 as score
)
SELECT
  *,
  RANK() OVER(
    ORDER BY score DESC
  )
  as rank
FROM DUAL
;
+----+-------+------+
| id | score | rank |
+----+-------+------+
|  1 |   100 |    1 |
|  4 |    97 |    2 |
|  2 |    90 |    3 |
|  3 |    90 |    3 |
+----+-------+------+

ゴールデン導線

ゴールデン導線とは、webサイトのログデータにおいて、ユーザーがどのようにページを移動してコンバージョンしたかを集計し、コンバージョンに至りやすい導線を探す分析です。ゴールデン分析は以下のように行えます。
WITH DUAL AS (
  select 1 as id, TIMESTAMP('2022-01-01 00:00:00', 'Asia/Tokyo') as ts, '/a.html' page
  union all
  select 1 as id, TIMESTAMP('2022-01-01 01:00:00', 'Asia/Tokyo') as ts, '/b.html' page
  union all
  select 1 as id, TIMESTAMP('2022-01-01 02:00:00', 'Asia/Tokyo') as ts, '/cv.html' page
  union all
  select 2 as id, TIMESTAMP('2022-01-01 00:00:00', 'Asia/Tokyo') as ts, '/a.html' page
)
SELECT
  *,
  ROW_NUMBER() OVER(
    PARTITION BY id
    ORDER BY ts
  ) as row_num
FROM DUAL
;
+----+---------------------+----------+---------+
| id |         ts          |   page   | row_num |
+----+---------------------+----------+---------+
|  1 | 2021-12-31 15:00:00 | /a.html  |       1 |
|  1 | 2021-12-31 16:00:00 | /b.html  |       2 |
|  1 | 2021-12-31 17:00:00 | /cv.html |       3 |
|  2 | 2021-12-31 15:00:00 | /a.html  |       1 |
+----+---------------------+----------+---------+

前後のレコードの値を取得

LEAD/LAG関数で前後のレコードの値を取得することができます。使用例としては、日付カラムを元に前後を比較すると前日や翌日との差分などを取得することができます。
WITH DUAL AS (
  select 1 as id, TIMESTAMP('2022-01-01 00:00:00', 'Asia/Tokyo') as ts, 100 as quantity
  union all
  select 1 as id, TIMESTAMP('2022-01-02 00:00:00', 'Asia/Tokyo') as ts, 200 as quantity
  union all
  select 1 as id, TIMESTAMP('2022-01-03 00:00:00', 'Asia/Tokyo') as ts, 300 as quantity
  union all
  select 1 as id, TIMESTAMP('2022-01-04 00:00:00', 'Asia/Tokyo') as ts, 400 as quantity
)
SELECT
  *,
  LEAD(quantity) OVER(
    ORDER BY ts
  ) as lead_quantity,
  LAG(quantity) OVER(
    ORDER BY ts
  ) as lag_quantity
FROM DUAL
;
+----+---------------------+----------+---------------+--------------+
| id |         ts          | quantity | lead_quantity | lag_quantity |
+----+---------------------+----------+---------------+--------------+
|  1 | 2021-12-31 15:00:00 |      100 |           200 |         NULL |
|  1 | 2022-01-01 15:00:00 |      200 |           300 |          100 |
|  1 | 2022-01-02 15:00:00 |      300 |           400 |          200 |
|  1 | 2022-01-03 15:00:00 |      400 |          NULL |          300 |
+----+---------------------+----------+---------------+--------------+

初回/最後の値

FIRST_VALUE/LAST_VALUEで初回/最後の値を取得できます。使用例としては、ユーザー毎の初回購入日や最後の購入日等を調べる事ができます。
WITH DUAL AS (
  select 1 as id, TIMESTAMP('2022-01-01 00:00:00', 'Asia/Tokyo') as ts, 100 as quantity
  union all
  select 1 as id, TIMESTAMP('2022-01-02 00:00:00', 'Asia/Tokyo') as ts, 200 as quantity
  union all
  select 1 as id, TIMESTAMP('2022-01-03 00:00:00', 'Asia/Tokyo') as ts, 300 as quantity
  union all
  select 2 as id, TIMESTAMP('2022-01-01 00:00:00', 'Asia/Tokyo') as ts, 400 as quantity
)
SELECT
  *,
  FIRST_VALUE(ts) OVER(
    ORDER BY id
  ) as first_ts,
  LAST_VALUE(ts) OVER(
    ORDER BY id
  ) as last_ts,
FROM DUAL
;
+----+---------------------+----------+---------------------+---------------------+
| id |         ts          | quantity |      first_ts       |       last_ts       |
+----+---------------------+----------+---------------------+---------------------+
|  1 | 2021-12-31 15:00:00 |      100 | 2021-12-31 15:00:00 | 2022-01-02 15:00:00 |
|  1 | 2022-01-01 15:00:00 |      200 | 2021-12-31 15:00:00 | 2022-01-02 15:00:00 |
|  1 | 2022-01-02 15:00:00 |      300 | 2021-12-31 15:00:00 | 2022-01-02 15:00:00 |
|  2 | 2021-12-31 15:00:00 |      400 | 2021-12-31 15:00:00 | 2021-12-31 15:00:00 |
+----+---------------------+----------+---------------------+---------------------+

指定した順番の値

NTH_VALUEにより任意の指定した順番の値を取得できます。使用として、初回購入から何日後に2回目の購入をしたかなど順番を意識した分析が可能です。
WITH DUAL AS (
  select 1 as id, TIMESTAMP('2022-01-01 00:00:00', 'Asia/Tokyo') as ts, 100 as quantity
  union all
  select 1 as id, TIMESTAMP('2022-01-02 00:00:00', 'Asia/Tokyo') as ts, 200 as quantity
  union all
  select 1 as id, TIMESTAMP('2022-01-03 00:00:00', 'Asia/Tokyo') as ts, 300 as quantity
  union all
  select 2 as id, TIMESTAMP('2022-01-01 00:00:00', 'Asia/Tokyo') as ts, 400 as quantity
), DUAL2 AS (
SELECT
  *,
  NTH_VALUE(ts, 1) OVER(
    ORDER BY id
  ) as first_ts,
  NTH_VALUE(ts, 2) OVER(
    ORDER BY id
  ) as second_ts,
FROM DUAL
)
SELECT 
  *,
  TIMESTAMP_DIFF(second_ts, first_ts, DAY) AS day_diff
FROM DUAL2
;
+----+---------------------+----------+---------------------+---------------------+----------+
| id |         ts          | quantity |      first_ts       |      second_ts      | day_diff |
+----+---------------------+----------+---------------------+---------------------+----------+
|  1 | 2021-12-31 15:00:00 |      100 | 2021-12-31 15:00:00 | 2022-01-01 15:00:00 |        1 |
|  1 | 2022-01-01 15:00:00 |      200 | 2021-12-31 15:00:00 | 2022-01-01 15:00:00 |        1 |
|  1 | 2022-01-02 15:00:00 |      300 | 2021-12-31 15:00:00 | 2022-01-01 15:00:00 |        1 |
|  2 | 2021-12-31 15:00:00 |      400 | 2021-12-31 15:00:00 | 2022-01-01 15:00:00 |        1 |
+----+---------------------+----------+---------------------+---------------------+----------+

デシル分析

NTILE関数を使うことでデシル分析ができます。例は5等分ですが、膨大なレコードがある状態を10等分にして上位10％が売上の40%を締めているなど分析する事が可能です。
WITH DUAL AS (
  select 1 as id, TIMESTAMP('2022-01-01 00:00:00', 'Asia/Tokyo') as ts, 100 as quantity
  union all
  select 2 as id, TIMESTAMP('2022-01-02 00:00:00', 'Asia/Tokyo') as ts, 200 as quantity
  union all
  select 3 as id, TIMESTAMP('2022-01-03 00:00:00', 'Asia/Tokyo') as ts, 300 as quantity
  union all
  select 4 as id, TIMESTAMP('2022-01-01 00:00:00', 'Asia/Tokyo') as ts, 400 as quantity
  union all
  select 5 as id, TIMESTAMP('2022-01-01 00:00:00', 'Asia/Tokyo') as ts, 500 as quantity
  union all
  select 6 as id, TIMESTAMP('2022-01-01 00:00:00', 'Asia/Tokyo') as ts, 600 as quantity
  union all
  select 7 as id, TIMESTAMP('2022-01-01 00:00:00', 'Asia/Tokyo') as ts, 700 as quantity
  union all
  select 8 as id, TIMESTAMP('2022-01-01 00:00:00', 'Asia/Tokyo') as ts, 800 as quantity
  union all
  select 9 as id, TIMESTAMP('2022-01-01 00:00:00', 'Asia/Tokyo') as ts, 900 as quantity
  union all
  select 10 as id, TIMESTAMP('2022-01-01 00:00:00', 'Asia/Tokyo') as ts, 1000 as quantity
  union all
  select 1 as id, TIMESTAMP('2022-01-02 00:00:00', 'Asia/Tokyo') as ts, 1100 as quantity
)
SELECT
  *,
  NTILE(5) OVER(
    ORDER BY quantity DESC
  ) as ntile_5  
FROM DUAL
WHERE FORMAT_TIMESTAMP('%Y-%m-%d', ts, 'Asia/Tokyo') = '2022-01-01'
;
+----+---------------------+----------+---------+
| id |         ts          | quantity | ntile_5 |
+----+---------------------+----------+---------+
| 10 | 2021-12-31 15:00:00 |     1000 |       1 |
|  9 | 2021-12-31 15:00:00 |      900 |       1 |
|  8 | 2021-12-31 15:00:00 |      800 |       2 |
|  7 | 2021-12-31 15:00:00 |      700 |       2 |
|  6 | 2021-12-31 15:00:00 |      600 |       3 |
|  5 | 2021-12-31 15:00:00 |      500 |       3 |
|  4 | 2021-12-31 15:00:00 |      400 |       4 |
|  1 | 2021-12-31 15:00:00 |      100 |       5 |
+----+---------------------+----------+---------+

最後に

次世代システム研究室では、グループ全体のインテグレーションを支援してくれるアーキテクトを募集しています。アプリケーション開発の方、次世代システム研究室にご興味を持って頂ける方がいらっしゃいましたら、ぜひ募集職種一覧からご応募をお願いします。

  • Twitter
  • Facebook
  • はてなブックマークに追加

グループ研究開発本部の最新情報をTwitterで配信中です。ぜひフォローください。

 
  • AI研究開発室
  • 大阪研究開発グループ

関連記事