トレジャーデータで実践：Window関数（1）

この記事は最終更新から3年以上が経過しています。最新情報は担当のカスタマーサクセスにご確認ください。

はじめに

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

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

使用するデータセット

今回使用するデータセットは以下の項目を持ったEC購買ログになります。このログにおいて，

「category」⊃ 「sub_category」⊃「goods_id」

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

サンプルデータ@gist（10,000件）

集約関数

ここで改めて集約関数について説明しておきます。集約関数とは複数のレコードに基づいて

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

カテゴリ別：Window関数

Window関数では，個々のレコードに全体に関する情報（record_number, total_amount, total_sales）が「付与」されています。

集約関数の記述で上記のように個々のレコードに情報を付与する場合は，Join を使用します：

今回の例において Window 関数は，上述のように集約関数に書換可能で，その特徴をうまく表せていません。次の例を見てみましょう。

sub_category 別占有率：集約関数

次に category の下位に位置する sub_category に関して集約してみます。簡単ですね。

この結果レコードは sub_category において単一ですが，上位の category に対しては単一に集約されていません。

占有率

各sub_categoryが上位のcategoryに対してどの程度の割合を占めるか（＝占有率）は良く使われる指標です。

↑ sub_category ごとの売上（sales）がcategoryの総売上に対してどの割合かをツリーマップで表現しています。

categoryに付いても集約する必要のあるこの手のケースは，上位階層の集計はExcelやBI上で求めていました。これをSQL上の集計関数で一気にやろうとするとやや面倒です。

sub_category別占有率：Window関数

一方，Window関数ではシンプルなクエリで記述できます。

いかがでしょうか？Window関数の特徴はまだ触れていませんが，集約関数とは違ったものである事は少し理解して頂けたと思います。

次回はWindow関数の記法や関数について言及して行きます。