トレジャーデータで実践：Window関数（その１）
本記事は移転しました。新サイトにリダイレクトします。
トレジャーデータはクラウドでデータマネージメントサービスを提供しています。
はじめに
トレジャーデータでは最新のアップデートにより，バッチクエリ（Hive）でもWindow関数をサポートするようになりました。また，アドホッククエリ（Presto）では元々Window関数をサポートしています。
本記事で紹介したほとんどのクエリは Aggregate Functions（集約関数）のみを用いてきました。Window関数 は前述の集約関数とは似て非なる概念であり，この違いを理解し，うまく活用することでデータ分析の世界はさらに広まります。
使用するデータセット
今回使用するデータセットは「トレジャーデータで実践：Basket 分析」シリーズでも紹介した，以下の項目を持ったEC購買ログになります。このログにおいて，
「category」⊃ 「sub_category」⊃「goods_id」
の階層関係が成り立っているとします。
| member_id | goods_id | category | sub_category | ship_date | amount | price | time |
|---|---|---|---|---|---|---|---|
| 0 | 100808 | Home and Garden and Tools | Pet Supplies | 2004-12-04 | 1 | 5980 | 1104861075 |
| 519254 | 100808 | Home and Garden and Tools | Pet Supplies | 2004-12-04 | 1 | 5980 | 1104951180 |
| 393023 | 100812 | Home and Garden and Tools | Lamps and Light Fixtures | 2004-12-04 | 1 | 9980 | 1103993339 |
| 71148 | 100812 | Home and Garden and Tools | Lamps and Light Fixtures | 2004-12-04 | 1 | 9980 | 1104156968 |
| 71148 | 100812 | Home and Garden and Tools | Lamps and Light Fixtures | 2004-12-04 | 1 | 9980 | 1104158175 |
集約関数
ここで改めて集約関数について説明しておきます。集約関数とは複数のレコードに基づいて
- SUM や COUNT などの集計値，
- AVG や STDDEV（標準偏差）などの統計値，
- MAX や MIN などの順序値
といった値に「集約」することを目的にしています。
例えばとある高校の期末模試では，各先生は担任クラスのサマリとして生徒「全体」の模試結果を，平均点，標準偏差，最高得点などの「単一」の値に集約します。
Window 関数
一方，Window 関数は複数のレコードを全く集約することなく，全体や一部に基づいた傾向値を各々のレコードに「付与」します。
例えば先ほどの学期末テストでは，各学生に
- 自己の模試結果が全校で「何位」なのか（Rank），
- 最高／最低得点はいくらなのか（First Value, Last Value），
- 志望校合格可能性判定 A〜E（Ntile），
- 自己の過去5回の模試の平均点（Moving Average：移動平均）
といった全校生徒に基づいて算出された傾向値が自己の点数に付与され，模試結果として配られます。
さて，ここで集約関数の MAX／MIN と Window関数の First／Last Value がともに最高／最低値を表すものですが，前者は「単一の」値が返って来るに対し，後者は「各レコードに」最高／最低値が「付与される」という意味で異なる概念です。
ウォーミングアップ
それでは先ほどのサンプルデータを使って具体的にWindow関数の特徴を見ていきましょう。ここでは over や partition_by という句の意味は気にしないことにします。
カテゴリ別：集約関数
SELECT COUNT(), SUM(), ... GROUP BY のお馴染みのクエリ文でカテゴリ別の集約値を求めます。
| category | record_number | total_amount | total_sales |
|---|---|---|---|
| Automotive and Industrial | 605 | 623 | 4732710 |
| Beauty and Health and Grocery | 1422 | 1518 | 6660085 |
| Books and Audible | 493 | 540 | 3077120 |
| Clothing and Shoes and Jewelry | 296 | 307 | 3400700 |
| Electronics and Computers | 1600 | 1715 | 9686540 |
| Home and Garden and Tools | 2659 | 3060 | 12659242 |
| Movies and Music and Games | 1190 | 1338 | 6575600 |
| Sports and Outdoors | 876 | 896 | 9780344 |
| Toys and Kids and Baby | 859 | 905 | 4204960 |
カテゴリ別：Window関数
| goods_id | sub_category | category | record_number | total_amount | total_sales |
|---|---|---|---|---|---|
| 100808 | Pet Supplies | Home and Garden and Tools | 2659 | 3060 | 12659242 |
| 100808 | Pet Supplies | Home and Garden and Tools | 2659 | 3060 | 12659242 |
| 100812 | Lamps and Light Fixtures | Home and Garden and Tools | 2659 | 3060 | 12659242 |
| 100812 | Lamps and Light Fixtures | Home and Garden and Tools | 2659 | 3060 | 12659242 |
| 100812 | Lamps and Light Fixtures | Home and Garden and Tools | 2659 | 3060 | 12659242 |
| 100820 | Grocery and Gourmet Food | Beauty and Health and Grocery | 1422 | 1518 | 6660085 |
| 101364 | Home | Home and Garden and Tools | 2659 | 3060 | 12659242 |
Window関数では，個々のレコードに全体に関する情報（record_number, total_amount, total_sales）が「付与」されています。
集約関数の記述で上記のように個々のレコードに情報を付与する場合は，Join を使用します：
| goods_id | sub_category | category | record_number | total_amount | total_sales |
|---|---|---|---|---|---|
| 100808 | Pet Supplies | Home and Garden and Tools | 2659 | 3060 | 12659242 |
| 100808 | Pet Supplies | Home and Garden and Tools | 2659 | 3060 | 12659242 |
| 100812 | Lamps and Light Fixtures | Home and Garden and Tools | 2659 | 3060 | 12659242 |
| 100812 | Lamps and Light Fixtures | Home and Garden and Tools | 2659 | 3060 | 12659242 |
| 100812 | Lamps and Light Fixtures | Home and Garden and Tools | 2659 | 3060 | 12659242 |
| 100820 | Grocery and Gourmet Food | Beauty and Health and Grocery | 1422 | 1518 | 6660085 |
| 101364 | Home | Home and Garden and Tools | 2659 | 3060 | 12659242 |
今回の例において Window 関数は，上述のように集約関数に書換可能で，その特徴をうまく表せていません。次の例を見てみましょう。
sub_category 別占有率：集約関数
次に category の下位に位置する sub_category に関して集約してみます。簡単ですね。
| sub_category | category | record_number | total_amount | total_sales |
|---|---|---|---|---|
| Automotive Parts and Accessories | Automotive and Industrial | 59 | 68 | 102000 |
| Automotive Tools and Equipment | Automotive and Industrial | 51 | 51 | 297600 |
| Car/Vehicle Electronics and GPS | Automotive and Industrial | 53 | 53 | 416300 |
| Industrial Supplies | Automotive and Industrial | 10 | 10 | 135660 |
| Janitorial | Automotive and Industrial | 302 | 308 | 2871500 |
| Lab and Scientific | Automotive and Industrial | 78 | 79 | 704220 |
この結果レコードは sub_category において単一ですが，上位の category に対しては単一に集約されていません。
占有率
各sub_categoryが上位のcategoryに対してどの程度の割合を占めるか（＝占有率）は良く使われる指標です。
↑ sub_category ごとの売上（sales）がcategoryの総売上に対してどの割合かをツリーマップで表現しています。
categoryに付いても集約する必要のあるこの手のケースは，上位階層の集計はExcelやBI上で求めていました。これをSQL上の集計関数で一気にやろうとするとやや面倒です。
| sub_category | category | record_number_subcat | record_number_cat | percent_of_record_number | ... |
|---|---|---|---|---|---|
| Action Sports | Sports and Outdoors | 68 | 876 | 0.0776255707762557 | ... |
| All Beauty | Beauty and Health and Grocery | 363 | 1422 | 0.255274261603376 | ... |
| All Sports and Outdoors | Sports and Outdoors | 41 | 876 | 0.0468036529680365 | ... |
| Appliances | Home and Garden and Tools | 196 | 2659 | 0.0737119217751034 | ... |
sub_category別占有率：Window関数
一方，Window関数ではシンプルなクエリで記述できます。
| goods_id | sub_category | category | record_number_subcat | record_number_cat | ... |
|---|---|---|---|---|---|
| 101510 | Action Sports | Sports and Outdoors | 68 | 876 | ... |
| 101510 | Action Sports | Sports and Outdoors | 68 | 876 | ... |
| ... | ... | ... | ... | ... | ... |
| 101474 | All Beauty | Beauty and Health and Grocery | 363 | 1422 | ... |
| 101474 | All Beauty | Beauty and Health and Grocery | 363 | 1422 | ... |
いかがでしょうか？Window関数の特徴はまだ触れていませんが，集約関数とは違ったものである事は少し理解して頂けたと思います。
次回はWindow関数の記法や関数について言及して行きます。
