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

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

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

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

基本形3:AGGR_FUNC() OVER (PARTITION BY c ORDER BY d ROWS BETWEEN m PRECEDING AND n FOLLOWING)

基本形3 はPARTITION BY でグループ化され,ORDER BY で順序づけられた集合をさらに絞り込むために使用します。Window 関数における絞り込みは普通のSQLで記述されるWHERE句やLIMIT句とは異なる種のものである事の認識は重要です。

Window 関数では,ROWS BETWEEN で絞り込む条件として自分のレコードの前後関係を指定する事が大きな特徴です。例を見てみましょう。

移動平均

自分の過去 m 回分のレコードを遡って求められる平均を移動平均と呼びます。

下の文では自身の値+過去の自己3レコード前までの範囲を ROW 句で指定してします。ROW 句で指定するレンジは必ず「自己」に対していくつ前(後)なのか,という相対的なものである事はとても重要です。

timegoods_idamountmoving_avg
1103900728 101367 1 1
1103931538 101367 1 1
1104014510 101367 1 1
1104024652 101367 1 1
1104029683 101367 2 1.25
1104060359 101367 1 1.25
1104076857 101367 1 1.25
1104078107 101367 3 1.75
1104094114 101367 4 2.25
1104094737 101367 5 3.25
1104098962 101367 2 3.5

パターン

ROWS BETWEEN 句の取り得るパターンは覚えておきましょう:

1. ROWS BETWEEN m PRECEDING AND n FOLLOWING

自己レコードに対して「m個前」から「n個先」までの範囲を指定します。

2. ROWS BETWEEN UNBOUNDED PRECEDING AND n FOLLOWING

自己レコードに対して「過去全て」から「n個先」までの範囲を指定します。

3. ROWS BETWEEN m PRECEDING AND UNBOUNDED FOLLOWING

自己レコードに対して「m個前」から「先全て」までの範囲を指定します。

4. ROWS BETWEEN m PRECEDING AND CURRENT ROW

自己レコードに対して「m個前」から「自己」までの範囲を指定します。

5. ROWS BETWEEN CURRENT ROW AND n FOLLOWING

自己レコードに対して「自己」から「n個先」までの範囲を指定します。

6. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

自己レコードに対して「過去全て」から「自己」までの範囲を指定します。

7. ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

自己レコードに対して「自己」から「先全て」までの範囲を指定します。

基本形3':LEAD/LAG() OVER (PARTITION BY c ORDER BY d ROWS BETWEEN m PRECEDING AND n FOLLOWING)

LEAD関数,LAG関数

基本形3では自己を中心とした前後のレコードを抽出し,集約関数を実行していました。この亜種として,集約関数ではなく LEAD, LAG 関数を実行するものがあります。LEAD関数は自己から n 個先の「値(集約値ではない)」を取得し,付与します。LAG関数は自己から m 個前の「値」を取得し,付与します。

この2つの関数はとても良く使われます。特に自己の1つ前,1つ後のレコードの値を並べる事で差分を参照することが可能になります。

timegoods_idamountlag1lag2lag3lead1lead2lead3
1104156968 100812 1 1 1 1 1 1 1
1104158175 100812 1 1 1 1     0
1103900207 101364 1 1 1 1 1 1 1
1103902816 101364 1 1 1 1 1 1 1
1103927229 101364 1 1 1 1 4 4 4

基本形4:Other

NTILE関数

NTILE(n) 関数は,指定したグループ内で n 等分した上でどのタイルに入るかの番号を返す関数です。任意の値で等分してレコードごとの所属を決定してくれるこの関数は,ユーザーやアイテムのセグメンテーションの1つの簡単な方法としてポテンシャルの高い関数です。

↑ のクエリではグッズごとの1日の売上額を求め,一つ上の sub_category の中で,個々のグッズの売上額に応じた10段階の中の区分の中でどこに所属するかを付与します。

10段階で分類するということは,例えば sales_rank=1 ならば上位10%,sales_rank=2 ならば上位10%〜20%にランキングするグッズであることを示すことになります。

dgoods_iddaily_salessales_rank
2004-12-24 101534 105600 1
2004-12-24 101474 103260 1
2004-12-24 101543 101020 1
2004-12-24 101549 100600 2
2004-12-24 101423 85600 2
2004-12-24 101462 85170 2
2004-12-24 101483 85000 2
Sales Rank の推移

NTILE(10) の値を sales_rank とした時,当日の sales_rank が過去1週間の sales_rank の動向に対して大きな変化があったかどうかを確認する方法を考えてみましょう。ここでは過去一週間の sales_rank の MAX, MIN, AVG, VAR の情報を付与しています。

  1. MAX = MIN の場合,直近1週間で sales_rank が常に同じ値で推移している事を示しています。
  2. AVG に対して今回の sales_rank が1以上乖離していれば,sub_category 内でのそのグッズの位置づけが大きく変化している傾向を読み取れます。

dgoods_iddaily_salessales_rankmax_rankmin_rankavg_rank
2004-12-24 101518 373000 10 10 10 10
2004-12-24 101495 225600 10 10 10 10
2004-12-24 101526 217850 10 10 10 10
2004-12-24 101534 105600 10 10 10 10
2004-12-24 101474 103260 10 10 10 10
2004-12-24 101543 101020 9 9 9 9
2004-12-24 101549 100600 9 9 9 9

↑ 青掛けしたカラムは,過去一週間の sales_rank の統計情報です。

以上で一通りのWindow関数を紹介しました。その他リファレンスは,

をご参照下さい。

Window 関数は今後の実践シリーズで度々登場していくことになります。