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

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

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

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

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

 

はじめに

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

本記事で紹介したほとんどのクエリは Aggregate Functions(集約関数)のみを用いてきました。Window関数 は前述の集約関数とは似て非なる概念であり,この違いを理解し,うまく活用することでデータ分析の世界はさらに広まります。

使用するデータセット

今回使用するデータセットは「トレジャーデータで実践:Basket 分析」シリーズでも紹介した,以下の項目を持ったEC購買ログになります。このログにおいて,

「category」⊃ 「sub_category」⊃「goods_id」

の階層関係が成り立っているとします。

サンプルデータ@gist(10,000件)

member_idgoods_idcategorysub_categoryship_dateamountpricetime
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 のお馴染みのクエリ文でカテゴリ別の集約値を求めます。

categoryrecord_numbertotal_amounttotal_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_idsub_categorycategoryrecord_numbertotal_amounttotal_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_idsub_categorycategoryrecord_numbertotal_amounttotal_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_categorycategoryrecord_numbertotal_amounttotal_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_categorycategoryrecord_number_subcatrecord_number_catpercent_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_idsub_categorycategoryrecord_number_subcatrecord_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関数の記法や関数について言及して行きます。