読者です 読者をやめる 読者になる 読者になる

トレジャーデータ(Treasure Data)公式ブログ

トレジャーデータ(Treasure Data)公式ブログです。

トレジャーデータで実践:Window関数(その2)

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

 

前回は集約関数と比する形で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」

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

f:id:doryokujin:20150528155103p:plain

MAX, MIN 関数

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

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

goods_idpricemax_pricemin_priceupdown
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_idsub_categoryrankrow_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 関数を利用した好例です。

f:id:doryokujin:20150528112920p:plain

コンバージョンパスにおいては,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
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関数を紹介していきます。