データ分析の際に使えるSQLの分析関数(Window関数)とそのメリットについて

はじめに

久しぶりの投稿です．今年の4月に社会人デビューし，CETチームで主にデータ分析業務を中心に仕事をしています．
今回は，データ分析の際に必要なSQLでの分析関数と，分析関数を使うことによるデータ分析のメリット等を備忘録として共有します

TL;DR

• 分析関数とは，行を落とさず(GROUP BY等で行をまとめることなく)，列に情報を持たせる関数である
• 分析関数を使うことで，ひとつの中間テーブルに様々な情報を持たせることができる
  • 例えば，ECサイトのログ等の分析の際に，CVRやCTRや閲覧ログの遷移や購入情報等の分析を，全て一つの中間テーブルから算出することが可能になる．
• Tableauのデータソース等にデータを持たせる場合，このように列に情報を付与していくことで，より早く高速に分析を行うことができる
  • 他のBIツールでも同じようなテクニックを利用できると思われる

分析関数とは

SQLの集計は，GROUP　BYを使って特定のキーごとに合計や平均などを算出することも可能ですが，これで返されるのは元のテーブルの行をキーで集約したものです．
分析関数は，対象のデータはそのままに，各行ごとに情報を付与します．そのため，元のテーブルの全てのレコードは保持されます．

分析関数(Window関数)が分析に有用であることは，

分析関数の衝撃
分析関数（ウインドウ関数）をわかりやすく説明してみた
[SQL] 最強の分析ツールと言われるWindow関数について私が学んだこと

この辺りにも書いてあります

##　分析関数の例
簡単な例を紹介します．
実行環境は，GoogleのBigqueryです．（他のDBでも一部構文が変わるだけなので，適宜書き換えていただければ再現可能です）

まず，このようなデータを用意します

WITH data AS(
                 SELECT 0  AS num,  ''   AS str
    UNION ALL    SELECT 1  AS num,  'A'  AS str
    UNION ALL    SELECT 2  AS num,  null AS str
    UNION ALL    SELECT 3  AS num,  null AS str
    UNION ALL    SELECT 4  AS num,  'B'  AS str
    UNION ALL    SELECT 5  AS num,  null AS str
    UNION ALL    SELECT 6  AS num,  'C'  AS str
    UNION ALL    SELECT 7  AS num,  ''   AS str
    UNION ALL    SELECT 8  AS num,  'A'  AS str
    UNION ALL    SELECT 9  AS num,  ''   AS str
    UNION ALL    SELECT 10 AS num,  'a'  AS str
)

SELECT
  *
FROM
  data
ORDER BY
  num

このようなデータを想定します．strのカラムは，NULLと空文字が混在しています．

分析関数のサンプルクエリ(若干複雑なものを用意しました)を用いるとこのようなデータを得ることができます

WITH data AS(
                 SELECT 0  AS num,  ''   AS str
    UNION ALL    SELECT 1  AS num,  'A'  AS str
    UNION ALL    SELECT 2  AS num,  null AS str
    UNION ALL    SELECT 3  AS num,  null AS str
    UNION ALL    SELECT 4  AS num,  'B'  AS str
    UNION ALL    SELECT 5  AS num,  null AS str
    UNION ALL    SELECT 6  AS num,  'C'  AS str
    UNION ALL    SELECT 7  AS num,  ''   AS str
    UNION ALL    SELECT 8  AS num,  'A'  AS str
    UNION ALL    SELECT 9  AS num,  ''   AS str
    UNION ALL    SELECT 10 AS num,  'a'  AS str
)

SELECT
  *
  ,　LAST_VALUE(NULLIF(str, '') IGNORE NULLS) 
    OVER(ORDER BY num ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS before_str
  ,　FIRST_VALUE(NULLIF(str, '') IGNORE NULLS) 
    OVER(ORDER BY num ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS after_str
  ,　LAST_VALUE(NULLIF(str, '') IGNORE NULLS) 
    OVER(ORDER BY num ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_str
  ,　FIRST_VALUE(NULLIF(str, '') IGNORE NULLS) 
    OVER(ORDER BY num ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_str
FROM
  data
ORDER BY 
  num;

このクエリを実行すると，以下のような結果が返ります

• before_strは，空文字とNULL以外の文字列に対して，直前に現れた文字を返します
• after_strは，空文字とNULL以外の文字列に対して，次に出現する文字を返します
• last_strは，空文字とNULL以外の文字列に対して，最後に出現した文字を返します
• first_strは，空文字とNULL以外の文字列に対して，最初に出現した文字を返します

このようなことができるのが分析関数です．もっと細かい情報はありますが，あるデータに対して，行を落とすことなくデータを返すものだと理解していただければいいと思います．これは複雑なクエリですが，実際はMAXやMIN，AVGなどのよく使われる集計関数ももちろん利用可能です

何が嬉しいのか

「分析関数が行を落とさないということはよくわかった」
「結局集計するならGROUP BYでいいのでは？」
「WITH句でGROUP BYしたものをJOINすればいいじゃん」

ということを以前の私は思っていました．この分析関数のメリットは，
ひとつの中間テーブルから様々な集計を算出可能にすることです．
例えば，ECサイト等のログを使う時に，この分析関数をうまく利用すると，
初回購入者のみの分析も，アイテムごとのCVRも，アイテムごとのCTRも，ユーザごとの閲覧数も，初回ユーザの閲覧数も，全てこの中間テーブルのみで算出が可能になります
分析関数を使わないと，あるマスタデータに対して，CVR用のクエリ（WITH句で複数のテーブルを作ってJOINして算出）するものと，CTR用のクエリなど，出したい数字ごとにクエリや中間テーブルが増え，再利用可能なクエリが減ってしまうことになります．
定期的なバッチ実行であったり，機械学習用の学習データを持って来るだけなら，このようなテーブルはいらないと思いますが，仮説を出すための分析であれば，様々な軸で分析を可能とするテーブルをひとつ作っておくと非常に便利です．

サンプルデータを使ってメリットを実感する

ここからは，実際に擬似的なデータを想定してユースケースを考えていきます．
ECサイトのログデータを簡略化したものを想定して，以下のようなデータを作ります

WITH data AS(
                 SELECT 0  AS num, 'u1' AS user, 'search' AS page, NULL AS item
    UNION ALL    SELECT 1  AS num, 'u1' AS user, 'item'   AS page, 'i2' AS item
    UNION ALL    SELECT 2  AS num, 'u2' AS user, 'item'   AS page, 'i1' AS item
    UNION ALL    SELECT 3  AS num, 'u1' AS user, 'item'   AS page, 'i1' AS item
    UNION ALL    SELECT 4  AS num, 'u1' AS user, 'search' AS page, NULL AS item
    UNION ALL    SELECT 5  AS num, 'u2' AS user, 'search' AS page, NULL AS item
    UNION ALL    SELECT 6  AS num, 'u2' AS user, 'item'   AS page, 'i2' AS item
    UNION ALL    SELECT 7  AS num, 'u2' AS user, 'search' AS page, NULL AS item
    UNION ALL    SELECT 8  AS num, 'u1' AS user, 'item'   AS page, 'i3' AS item
    UNION ALL    SELECT 9  AS num, 'u2' AS user, 'item'   AS page, 'i1' AS item
    UNION ALL    SELECT 10 AS num, 'u2' AS user, 'buy'    AS page, 'i2' AS item
)

SELECT
  *
FROM
  data
ORDER BY
  num

• numは，レコードの記録順です
• userは，ユーザIDを想定します
• pageは，検索(search)と，商品ページ(item)と，購入ページ（buy）です
• itemは，商品IDです（検索ページでは商品情報は入っていないと仮定します）

これを使って，例えば，ユーザの閲覧アイテム数，アイテムごとの購入率，検索ページからのクリック率などを算出しようとすると，このテーブルから複数の中間テーブルを作っていくことになります．

しかし，分析関数を利用すると，上記の分析を全て一つのテーブルから算出可能になります
例として，以下のようなクエリを実行して見ます

WITH data AS(
                 SELECT 0  AS num, 'u1' AS user, 'search' AS page, NULL AS item
    UNION ALL    SELECT 1  AS num, 'u1' AS user, 'item'   AS page, 'i2' AS item
    UNION ALL    SELECT 2  AS num, 'u2' AS user, 'item'   AS page, 'i1' AS item
    UNION ALL    SELECT 3  AS num, 'u1' AS user, 'item'   AS page, 'i1' AS item
    UNION ALL    SELECT 4  AS num, 'u1' AS user, 'search' AS page, NULL AS item
    UNION ALL    SELECT 5  AS num, 'u2' AS user, 'search' AS page, NULL AS item
    UNION ALL    SELECT 6  AS num, 'u2' AS user, 'item'   AS page, 'i2' AS item
    UNION ALL    SELECT 7  AS num, 'u2' AS user, 'search' AS page, NULL AS item
    UNION ALL    SELECT 8  AS num, 'u1' AS user, 'item'   AS page, 'i3' AS item
    UNION ALL    SELECT 9  AS num, 'u2' AS user, 'item'   AS page, 'i1' AS item
    UNION ALL    SELECT 10 AS num, 'u2' AS user, 'buy'    AS page, 'i2' AS item
)

SELECT
  *
  , ROW_NUMBER() OVER(PARTITION BY user ORDER BY num) AS user_row_num
  , ROW_NUMBER() OVER(PARTITION BY user, page ORDER BY num) AS user_page_row_num
  , LAG(page) OVER(PARTITION BY user ORDER BY num) AS lag_page
  , LAG(item) OVER(PARTITION BY user ORDER BY num) AS lag_item
  , COUNT(DISTINCT(CASE WHEN page = 'item' THEN item ELSE NULL END)) 
    OVER(PARTITION BY user) AS user_item_view_cnt
  , STRING_AGG(CASE WHEN page = 'item' THEN item ELSE NULL END) 
    OVER(PARTITION BY user ORDER BY num 
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS user_view_item_stragg
  , MAX(CASE WHEN page = 'buy' THEN 1 ELSE 0 END) 
    OVER(PARTITION BY user 
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS buy_user_flg
  , LAST_VALUE(CASE WHEN page = 'buy' THEN item ELSE NULL END) 
    OVER(PARTITION BY user ORDER BY num 
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS user_buy_item
  , LAST_VALUE(NULLIF(item, '') IGNORE NULLS) 
    OVER(PARTITION BY user ORDER BY num 
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS last_view_item
  , FIRST_VALUE(NULLIF(item, '') IGNORE NULLS) 
    OVER(PARTITION BY user ORDER BY num 
      ROWS BETWEEN UNBOUNDED PRECEDING  AND UNBOUNDED FOLLOWING) AS first_view_item
FROM
  data
ORDER BY 
  user
  , num

すると，ひとつのテーブルでこのようなデータを持つことが可能です

• user_row_numは，ユーザごとのページの閲覧順です（トータルで何ページ踏んだかを分析可能です．また，何ページ目なのかも抽出可能です）
• user_page_row_numは，ユーザ，ページ別の閲覧順です(ページ別で何回閲覧したか，ページ別で見て何ページ目なのかを抽出可能です)
• lag_page，lag_itemは，１ページ前の情報です（検索からのクリック率を算出可能です）
• user_item_view_cntは，アイテムページのユニークアイテム数を持っています(平均閲覧アイテム数などを分析可能です)
• user_view_item_straggは，アイテムページにおけるアイテム閲覧を文字列で順序として保持しています(似たような閲覧をされているアイテム等を分析可能です)
• buy_user_flgは，購入ページを踏んだユーザのログ全てにフラグをつけています（購入ユーザと非購入ユーザでログを抽出することが可能になります）
• user_buy_itemは，ユーザの購入アイテムを購入者のログに持たせています（アイテムごとのCVRを出すことができます）
• last_view_itemは，直前に閲覧したアイテムを保持しています(購入直前に見られたアイテムや，アイテム間の遷移率も分析可能です)
• first_view_itemは，ユーザーの最初の閲覧アイテムを持っています（購入アイテムが最初に閲覧したものであるかどうかなどの分析ができます）

このように，分析関数をうまく利用することで，データを落とさずに，様々な分析に必要な情報を持たせることができます．

このような分析関数を利用したテーブルを用意すると，ひとつのテーブルからできる分析の幅は広がります

Tableau等のBIツールで威力を発揮する分析関数

分析では主にTableauを使っているのですが，Tableauのデータソースに，GROUP BY等で集計させてしまったデータを乗せてしまうと，それ以上の分析ができなくなります．Tableauでは，メジャーやディメンジョンを様々に入れ替えながらデータを分析，可視化するものなので，なるべく元のデータは落とさずに，分析関数で情報を列として持たせることで，上記のような多様な分析を１つのデータソースで行うことが可能になります．
高速で柔軟な分析をTableau上で行うためには，このような分析関数は必要不可欠と言えると思います．

おそらく，他のBIツールでも,
DBのテーブルをGUIで簡単に可視化できるツールはあると思います．その際にも，分析関数をうまく使うことで高速に分析を回すことができると思います

終わりに

今回は，分析関数が何をできるのかと，分析関数を使うメリットを共有しました．
もっと細かいSQLのテクニック等を見たいときは，
ビッグデータ分析・活用のためのSQLレシピ
『ビッグデータ分析・活用のためのSQLレシピ』はデータ分析でSQLクエリを叩く人なら必読の黒魔術大全
この本が非常に参考になります．今回説明したような分析関数も含め，様々なユースケースに対応したクエリが乗っているので，基本的に分析でSQLを使う場合，この本があれば大抵解決しています．