Window 関数を使いこなす（集約関数系） US Tokyo EU01

はじめに

Window 関数は別名 OLAP（分析）関数と呼ばれ，データウェアハウスにおける分析のために使用されてきた，まさに「データ分析」のための関数と言えます。Hive でも Presto でも Window関数が使えるようになったのはつい最近の事ですが，Window 関数を理解し，使いどころを極めればデータ分析の幅はこれまでの2倍以上に広まるといっても過言ではありません。

概要

Window関数はテーブルを区間ごとに集計する機能です。集約関数 (GROUP BY) に似ていますが，Window関数では複数の行がまとめられることはなく，行それぞれが返却されます。また、処理中の行以外の行の値を読み取ることも可能です。Window関数は以下の構文を使います。

• 関数(...) OVER (PARTITION BY ...) : 区間に分割
• 関数(...) OVER (ORDER BY ...) : 区間ごとに並び替え
• 関数(...) OVER (PARTITION BY ... ORDER BY ...) 

関数には以下に挙げたものと，通常の集約関数 (countやsumなど) が使用できます。lag() や lead() は自己結合 (セルフジョイン; Self-Join) の代わりに使用することで，処理を大幅に効率化できる場合もあります。 また，Windows 関数の入門については以下の記事を参照ください。

• Treasure Data, Inc. > 逆引きSQL大辞典 > HiveQL リファレンス > Window関数

集約関数系

Window 関数の枠組みの中でも集約関数を利用する事ができます。ただし注意点として，GROUP BY によってレコードが「集約」されるのではなく，各々のレコードに集約された値が「付与される」ことが，一般の集約関数とは大きく異なります。

集約関数系では，

• 関数(...) OVER (PARTITION BY ... ORDER BY ... ROWS ... BETWEEN ... )

という過去と先のいくつまでを集約のレンジとするかを指定する書き方がよくされます。「ROWS...BETWEEN」句の書き方のパターンは以下を参照ください。

Running Sum：初めから現在までの累計和を求める

例1：goods_id ごとに，各日の売上額と併せて，月初から当日までの累計和を表示する。

Moving Average：移動平均，n日間の平均値を求める

移動平均もWindow関数+集約関数で実現可能です。

例2：goods_id ごとに，各日の売上額と併せて直近5日間の移動平均を表示する。

Local Max, Min：特定の区間での最大，最小を求める

各々のレコードに，該当するパーティションの最大値/最小値を付与できます。

例3：各 goods_id ごとに，月間の最大売上額/最小売上額を各々のレコードに付与する。