トレジャーデータはクラウドでデータマネージメントサービスを提供しています。

トレジャーデータで実践:Window関数(その1)
トレジャーデータでは最新のアップデートにより,バッチクエリ(Hive)でもWindow関数をサポートするようになりました。また,アドホッククエリ(Presto)では元々Windo...

前回は集約関数と比する形でWindow関数の紹介をしました。今回はWindow関数の記述および代表的な関数を分かりやすく説明していきます。

基本形1:AGGR_FUNC() OVER (PARTITION BY c)

↑ Window 関数の最もシンプルな記法ですが,集約関数(ここでは COUNT) が OVER 句を伴って記述されています。また,GROUP BY 句がありません。

まずこの OVER 句は肝で,直前の集約関数がこの OVER上のグループに対して実行され,レコードに付与されています。

さらにOVER内の ( PARTITION BY c ) は GROUP BY c と同様にカラム c の取り得る値でグルーピングします。つまり Window 関数は SELECT 内で局所的に 「GROUP BY c で集約関数を実行」していることになります。

複数の Window 関数は同時に記述可能で,かつグルーピングされるカラムも個々に別のものを記述できます。前回で紹介した

では 3 つの WIndow 関数,

  • COUNT(1) OVER (PARTITION BY category) AS record_number
  • SUM(amount) OVER (PARTITION BY category) AS total_amount
  • SUM(price*amount) OVER (PARTITION BY category) AS total_sales

が個々に OVER上のグループで集約関数を実行しています。上の例では全て category で PARTITION BY されていますが,

では category と sub_category の異なるグルーピングカラムが指定されています。

Percent of Total(占有率)

グループ全体の値に対して,個々の値が全体のどれくらいを占めるか(占有率=percent of total)は重要な指標です。本サンプルデータセットにおいては,

「category」⊃ 「sub_category」⊃「goods_id」

の階層構造があるので,上位階層に対する占有率を求めることに意義を見いだせます。

MAX, MIN 関数

ORVER 上でグルーピングされた集合の中でMAX/MINを求めるケースが最もシンプルなケースです。集約関数では無く,Window 関数の枠組みでMAX/MIN関数を使うことは,各々のレコードが自己の属するグループ内でのMAX/MINを付与するということになります。一つ例を挙げます。

ECサイト上の商品は,価格が自由に変更可能であるという性質を持っています。それ故,価格変更のあった商品には,過去の最高プライス・最低プライスを付与しておくことは有用です。

goods_idpricemax_pricemin_priceupdown
1016885000500034801520
1016883480500034801520
1016902480350024801020
1016903000350024801020

基本形2:AGGR_FUNC() OVER (PARTITION BY c ORDER BY d)

Window 関数ではグループ内での順番が重視されるケースが結構あります。これを内包した代表的なWindow関数として,RANK() と ROW_NUMBER() があります。

RANK 関数,ROW_NUMBER 関数

RANK 関数および ROW_NUMNER 関数は OVER 上でグルーピング(PARTITION BY)かつソーティング(ORDER BY)されたレコードに順番に番号を割り振っていく関数です。両者の違いは順序が同じレコードに対して同じ番号を振る(=RANK),順に異なる番号を振る(=ROW_NUMBER)にあります。

以下のクエリは sub_category によってグルーピングされたレコードを,goods_id の順に並び替えた上で番号付けを行っています。

ECの購買ログであるこのサンプルデータセットにおいては同じ goods_id が複数存在しうるので,RANK 関数と ROW_NUMBER 関数は異なる番号を割り振ります。

goods_idsub_categoryrankrow_number
101416Action Sports11
101416Action Sports12
101416Action Sports
101416Action Sports113
101416Action Sports114
101510Action Sports1515
101510Action Sports1516
コンバージョンパス

トレジャーデータで実践:Path 分析(前編)
トレジャーデータで実戦可能なパス分析ソリューションは,主にWebや広告業界向けのコンバージョン分析の応用(コンバージョン「パス」に主眼をおいた分析を行うもの)と...

過去紹介した Path 分析で登場したコンバージョンパスは,無機質なアクセスログや購買ログからUserIDとTimeで適切に並び替え,番号を「付与する」ことで有機的なコンバージョンパスを作り出す Rank 関数を利用した好例です。

コンバージョンパスにおいては,Time 順で UserID ごとにソートされたアクセスログに対して,

  1. そのユーザーの何回目のコンバージョンか(cv_id)
  2. そのコンバージョンパスの中で何番目に登場したか(node_id)

という2つの階層で Rank 関数を使っています。

FIRST_VALUE 関数,LAST_VALUE 関数

順序付けられたグループに対しては,時として「始め」と「終わり」が意識されることがあります。PARTITION BY でグルーピングされ,ORDER BY で並べ替えられた集合に対して,FIRST_VALUE および LAST_VALUE 関数は,最初と最後のレコードの特定のカラム値情報を付与します。

goods_idpricestart_pricelast_price
100812998099809980
100812998099809980
100812998099809980
101367240024002400
コンバージョンセット

前述のコンバージョンパスは,レコードの集約は行われませんでしたが,一方で1つのコンバージョンパスを1レコードに集約した「コンバージョンセット」では,パスの最初と最後の「カテゴリ名」および「ページ名」を求めて保存しています。(青掛けのカラム)

カラム名説明
userユーザーID “1”
cv_idコンバージョンID 910
landing_categoryランディングしたカテゴリ名 “press-releases”
last_categoryコンバージョン直前のカテゴリ名 “home”
cv_categoryコンバージョンしたカテゴリ名 “signup”
category_setパス内に存在するカテゴリ集合 [ “home”, “signup”,” press-releases”, “products” ]
landing_nodeランディングしたノード名 “/treasuredata.com/jp/home
last_nodeコンバージョン直前のノード名 “/treasuredata.com/jp/press-releases
cv_nodeコンバージョンしたノード名 “/treasuredata.com/jp/signup
node_setパス内に存在するノード集合 [ “/treasuredata.com/jp/home“, “/treasuredata.com/jp/press-releases“, “/treasuredata.com/jp/products“,  “/treasuredata.com/en/home” ]
path_lengthパス長104
landing_timeランディングタイム 1421689018
last_timeコンバージョンタイム 1421690908

↑ テーブルは,コンバージョンセットの1つのレコードを載せています。複数のレコードに跨がっていたカテゴリやページが集合となって1つのレコードに集約されています。その際に,それらの集合の最初と最後を保持するようなカラムを付与しています。
次回はさらに ROW 句を用いたWindow関数を紹介していきます。