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

トレジャーデータで実践:Window関数(その2)
前回は集約関数と比する形でWindow関数の紹介をしました。今回はWindow関数の記述および代表的な関数を分かりやすく説明していきます。

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

基本形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
110390072810136711
110393153810136711
110401451010136711
110402465210136711
110402968310136721.25
110406035910136711.25
110407685710136711.25
110407810710136731.75
110409411410136742.25
110409473710136753.25
110409896210136723.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
11041569681008121111111
110415817510081211110
11039002071013641111111
11039028161013641111111
11039272291013641111444

基本形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-241015341056001
2004-12-241014741032601
2004-12-241015431010201
2004-12-241015491006002
2004-12-24101423856002
2004-12-24101462851702
2004-12-24101483850002

 

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-2410151837300010101010
2004-12-2410149522560010101010
2004-12-2410152621785010101010
2004-12-2410153410560010101010
2004-12-2410147410326010101010
2004-12-241015431010209999
2004-12-241015491006009999

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

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

をご参照下さい。

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