トレジャーデータで実践：Window関数（その２）

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

前回は集約関数と比する形で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_id	price	max_price	min_price	updown
101688	5000	5000	3480	1520
101688	3480	5000	3480	1520
101690	2480	3500	2480	1020
101690	3000	3500	2480	1020
...	...	...	...	...

基本形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_id	sub_category	rank	row_number
101416	Action Sports	1	1
101416	Action Sports	1	2
101416	Action Sports	...	...
101416	Action Sports	1	13
101416	Action Sports	1	14
101510	Action Sports	15	15
101510	Action Sports	15	16

コンバージョンパス

過去紹介した 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_id	price	start_price	last_price
100812	9980	9980	9980
100812	9980	9980	9980
100812	9980	9980	9980
101367	2400	2400	2400

コンバージョンセット

前述のコンバージョンパスは，レコードの集約は行われませんでしたが，一方で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関数を紹介していきます。