トレジャーデータで実践：Window関数（その３）
本記事は移転しました。新サイトにリダイレクトします。
トレジャーデータはクラウドでデータマネージメントサービスを提供しています。
基本形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 句で指定するレンジは必ず「自己」に対していくつ前（後）なのか，という相対的なものである事はとても重要です。
| time | goods_id | amount | moving_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つ後のレコードの値を並べる事で差分を参照することが可能になります。
| time | goods_id | amount | lag1 | lag2 | lag3 | lead1 | lead2 | lead3 |
|---|---|---|---|---|---|---|---|---|
| 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％にランキングするグッズであることを示すことになります。
| d | goods_id | daily_sales | sales_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 の情報を付与しています。
- MAX = MIN の場合，直近1週間で sales_rank が常に同じ値で推移している事を示しています。
- AVG に対して今回の sales_rank が1以上乖離していれば，sub_category 内でのそのグッズの位置づけが大きく変化している傾向を読み取れます。
| d | goods_id | daily_sales | sales_rank | max_rank | min_rank | avg_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 関数は今後の実践シリーズで度々登場していくことになります。
