Hive における Window関数 を真面目に理解する - 集約関数編
Written by @ryysud Aug 8, 2018 19:29 · 3182 words · 7 minutes read
モチベーション
お仕事でのデータ分析基盤における ETL 処理でも Window関数 を多用しているのですが、なんとなーく挙動を理解している程度だったので、ちゃんと公式ドキュメントを読んで1から学び直したいと思いました。
そもそも Window関数（ = 窓関数, window function）とはなにか？
天下の Wikipedia 様によると、、、
窓関数もしくはウィンドウ関数 (英: window function) は結果セットを部分的に切り出した領域に集約関数を適用できる、拡張された SELECT ステートメントである。SQL:2003 以降の標準SQLで規定されている。分析関数やOLAP機能と呼ばれる場合もある。
「結果セットを部分的に切り出した領域に集約関数を適用できる」がとてもわかりやすいですね。
Hive の公式ドキュメントを見ながらガシガシとクエリを実行していく
公式ドキュメント
LanguageManual WindowingAndAnalytics - Apache Hive
検証環境
EMR を起動して Hive 2.3.3 で検証を実施していきます。
基本的な Window関数 の使い方
キモとなる over 構文の基本的な使い方をまとめておきます。
-- 指定されたカラムの値を元にレコードを分割して関数を実行
func() over(partition by ...)
-- 指定されたカラムの値でレコードを並び替えて処理されるレコードの順序を制御しながら関数を実行
func() over(order by ...)
-- 合わせ技も可能
func() over(partition by ... order by ...)
Hive 2.x系 からサポートされた HIVE-9534 や HIVE-13453 や HIVE-13475 などは、理解がまだ及ばないため今回は割愛します。まずは基本的なところを学ぶことから。
検証に利用するサンプルテーブル
挙動がわかりやすいようにシンプルなテーブルを用意しました。
> select * from students;
+--------------+----------------+-----------------+-----------------+
| students.id | students.name | students.class | students.score |
+--------------+----------------+-----------------+-----------------+
| 1 | taro | a | 45 |
| 2 | keita | b | 30 |
| 3 | saori | c | 90 |
| 4 | manami | a | 100 |
| 5 | takeshi | c | 75 |
| 6 | shinya | a | 80 |
| 7 | yui | b | 55 |
| 8 | kenta | c | 75 |
| 9 | asami | b | 65 |
| 10 | takaya | a | 10 |
+--------------+----------------+-----------------+-----------------+
集約関数編 count(), sum(), min(), max(), avg()
1. count() でレコード数を求める
count(1) over(partition by class) とすることで class 毎にレコードが分割され、各 class 内のそれぞれのレコードを 1 として count() 関数に投げられるので、class 毎の人数を求めることが可能になります。カラムの値を元にレコード数を求めたければ count(id) のようにすれば良いです。（但し id が null の場合には count() されない注意が必要）
> select *, count(1) over(partition by class) as class_members_num from students;
+--------------+----------------+-----------------+-----------------+--------------------+
| students.id | students.name | students.class | students.score | class_members_num |
+--------------+----------------+-----------------+-----------------+--------------------+
| 1 | taro | a | 45 | 4 |
| 4 | manami | a | 100 | 4 |
| 6 | shinya | a | 80 | 4 |
| 10 | takaya | a | 10 | 4 |
| 2 | keita | b | 30 | 3 |
| 7 | yui | b | 55 | 3 |
| 9 | asami | b | 65 | 3 |
| 3 | saori | c | 90 | 3 |
| 5 | takeshi | c | 75 | 3 |
| 8 | kenta | c | 75 | 3 |
+--------------+----------------+-----------------+-----------------+--------------------+
count(1) over(partition by class order by id) のように over 構文で order by を使用した場合には、処理されるレコードの順序を制御することが可能になります。この例だと、1つ前のクエリと同じように class 毎にレコードが分割され、各 class 内のそれぞれのレコードが 1 として count() 関数に投げられるのですが、 id の値でレコードが並び替えられ、処理されるレコードの順序が制御された上で都度 count() が実行される形となります。
calculation_process_of_class_members_num カラムの値を見ると、id で並び替えられた上で都度 count() が実行されていることがわかります。
> select *, count(1) over(partition by class order by id) as calculation_process_of_class_members_num from students;
+--------------+----------------+-----------------+-----------------+-------------------------------------------+
| students.id | students.name | students.class | students.score | calculation_process_of_class_members_num |
+--------------+----------------+-----------------+-----------------+-------------------------------------------+
| 1 | taro | a | 45 | 1 |
| 4 | manami | a | 100 | 2 |
| 6 | shinya | a | 80 | 3 |
| 10 | takaya | a | 10 | 4 |
| 2 | keita | b | 30 | 1 |
| 7 | yui | b | 55 | 2 |
| 9 | asami | b | 65 | 3 |
| 3 | saori | c | 90 | 1 |
| 5 | takeshi | c | 75 | 2 |
| 8 | kenta | c | 75 | 3 |
+--------------+----------------+-----------------+-----------------+-------------------------------------------+
2. sum() で合計値を求める
1.count() でレコード数を求める と説明が重複する箇所がありますが、おさらい的に改めて説明していますので悪しからず。
sum(score) over(partition by class) とすることで class 毎にレコードが分割され、各 class 内のそれぞれの score カラムの値が sum() 関数に投げられるので、class 毎の合計値を求めることが可能になります。
> select *, sum(score) over(partition by class) as total_score from students;
+--------------+----------------+-----------------+-----------------+--------------+
| students.id | students.name | students.class | students.score | total_score |
+--------------+----------------+-----------------+-----------------+--------------+
| 1 | taro | a | 45 | 235 |
| 4 | manami | a | 100 | 235 |
| 6 | shinya | a | 80 | 235 |
| 10 | takaya | a | 10 | 235 |
| 2 | keita | b | 30 | 150 |
| 7 | yui | b | 55 | 150 |
| 9 | asami | b | 65 | 150 |
| 3 | saori | c | 90 | 240 |
| 5 | takeshi | c | 75 | 240 |
| 8 | kenta | c | 75 | 240 |
+--------------+----------------+-----------------+-----------------+--------------+
sum(score) over(partition by class order by id) のように over 構文で order by を使用した場合には、処理されるレコードの順序を制御することが可能になります。この例だと class 毎にレコードが分割され、各 class 内のそれぞれの score カラムの値が sum() 関数に投げられるのですが、 id の値でレコードが並び替えられ、処理されるレコードの順序が制御された上で都度 sum() が実行される形となります。
calculation_process_of_total_score カラムの値を見ると、id で並び替えられた上で都度 score 値が sum() に投げられ実行されていることがわかります。（わかりやすいように括弧書きで sum() される数値を記載しているが実際には表示されません）
> select *, sum(score) over(partition by class order by id) as calculation_process_of_total_score from students;
+--------------+----------------+-----------------+-----------------+-------------------------------------+
| students.id | students.name | students.class | students.score | calculation_process_of_total_score |
+--------------+----------------+-----------------+-----------------+-------------------------------------+
| 1 | taro | a | 45 | 45 |
| 4 | manami | a | 100 | 145 (45+100) |
| 6 | shinya | a | 80 | 225 (145+80) |
| 10 | takaya | a | 10 | 235 (225+10) |
| 2 | keita | b | 30 | 30 |
| 7 | yui | b | 55 | 85 (30+55) |
| 9 | asami | b | 65 | 150 (85+65) |
| 3 | saori | c | 90 | 90 |
| 5 | takeshi | c | 75 | 165 (90+75) |
| 8 | kenta | c | 75 | 240 (165+75) |
+--------------+----------------+-----------------+-----------------+-------------------------------------+
3. min() で最小値を求める
class 毎の score の最小値を求めることが可能になります。
> select *, min(score) over(partition by class) as min_score from students;
+--------------+----------------+-----------------+-----------------+------------+
| students.id | students.name | students.class | students.score | min_score |
+--------------+----------------+-----------------+-----------------+------------+
| 1 | taro | a | 45 | 10 |
| 4 | manami | a | 100 | 10 |
| 6 | shinya | a | 80 | 10 |
| 10 | takaya | a | 10 | 10 |
| 2 | keita | b | 30 | 30 |
| 7 | yui | b | 55 | 30 |
| 9 | asami | b | 65 | 30 |
| 3 | saori | c | 90 | 75 |
| 5 | takeshi | c | 75 | 75 |
| 8 | kenta | c | 75 | 75 |
+--------------+----------------+-----------------+-----------------+------------+
id で並び替えて都度 min() が実行されていることがわかります。
> select *, min(score) over(partition by class order by id) as calculation_process_of_min_score from students;
+--------------+----------------+-----------------+-----------------+-----------------------------------+
| students.id | students.name | students.class | students.score | calculation_process_of_min_score |
+--------------+----------------+-----------------+-----------------+-----------------------------------+
| 1 | taro | a | 45 | 45 |
| 4 | manami | a | 100 | 45 |
| 6 | shinya | a | 80 | 45 |
| 10 | takaya | a | 10 | 10 |
| 2 | keita | b | 30 | 30 |
| 7 | yui | b | 55 | 30 |
| 9 | asami | b | 65 | 30 |
| 3 | saori | c | 90 | 90 |
| 5 | takeshi | c | 75 | 75 |
| 8 | kenta | c | 75 | 75 |
+--------------+----------------+-----------------+-----------------+-----------------------------------+
4. max() で最大値を求める
class 毎の score の最大値を求めることが可能になります。
> select *, max(score) over(partition by class) as max_score from students;
+--------------+----------------+-----------------+-----------------+------------+
| students.id | students.name | students.class | students.score | max_score |
+--------------+----------------+-----------------+-----------------+------------+
| 1 | taro | a | 45 | 100 |
| 4 | manami | a | 100 | 100 |
| 6 | shinya | a | 80 | 100 |
| 10 | takaya | a | 10 | 100 |
| 2 | keita | b | 30 | 65 |
| 7 | yui | b | 55 | 65 |
| 9 | asami | b | 65 | 65 |
| 3 | saori | c | 90 | 90 |
| 5 | takeshi | c | 75 | 90 |
| 8 | kenta | c | 75 | 90 |
+--------------+----------------+-----------------+-----------------+------------+
id で並び替えて都度 max() が実行されていることがわかります。
> select *, max(score) over(partition by class order by id) as calculation_process_of_max_score from students;
+--------------+----------------+-----------------+-----------------+-----------------------------------+
| students.id | students.name | students.class | students.score | calculation_process_of_max_score |
+--------------+----------------+-----------------+-----------------+-----------------------------------+
| 1 | taro | a | 45 | 45 |
| 4 | manami | a | 100 | 100 |
| 6 | shinya | a | 80 | 100 |
| 10 | takaya | a | 10 | 100 |
| 2 | keita | b | 30 | 30 |
| 7 | yui | b | 55 | 55 |
| 9 | asami | b | 65 | 65 |
| 3 | saori | c | 90 | 90 |
| 5 | takeshi | c | 75 | 90 |
| 8 | kenta | c | 75 | 90 |
+--------------+----------------+-----------------+-----------------+-----------------------------------+
5. avg() で平均値を求める
class 毎の score の平均値を求めることが可能になります。
> select *, avg(score) over(partition by class) as avg_score from students;
+--------------+----------------+-----------------+-----------------+------------+
| students.id | students.name | students.class | students.score | avg_score |
+--------------+----------------+-----------------+-----------------+------------+
| 1 | taro | a | 45 | 58.75 |
| 4 | manami | a | 100 | 58.75 |
| 6 | shinya | a | 80 | 58.75 |
| 10 | takaya | a | 10 | 58.75 |
| 2 | keita | b | 30 | 50.0 |
| 7 | yui | b | 55 | 50.0 |
| 9 | asami | b | 65 | 50.0 |
| 3 | saori | c | 90 | 80.0 |
| 5 | takeshi | c | 75 | 80.0 |
| 8 | kenta | c | 75 | 80.0 |
+--------------+----------------+-----------------+-----------------+------------+
id で並び替えて都度 avg() が実行されていることがわかります。
> select *, avg(score) over(partition by class order by id) as calculation_process_of_total_score from students;
+--------------+----------------+-----------------+-----------------+-------------------------------------+
| students.id | students.name | students.class | students.score | calculation_process_of_total_score |
+--------------+----------------+-----------------+-----------------+-------------------------------------+
| 1 | taro | a | 45 | 45.0 |
| 4 | manami | a | 100 | 72.5 |
| 6 | shinya | a | 80 | 75.0 |
| 10 | takaya | a | 10 | 58.75 |
| 2 | keita | b | 30 | 30.0 |
| 7 | yui | b | 55 | 42.5 |
| 9 | asami | b | 65 | 50.0 |
| 3 | saori | c | 90 | 90.0 |
| 5 | takeshi | c | 75 | 82.5 |
| 8 | kenta | c | 75 | 80.0 |
+--------------+----------------+-----------------+-----------------+-------------------------------------+
おわりに
ブログにまとめるまでは理解が浅かったですが、実際に手を動かしてトライアンドエラーを重ねたことで理解が深まったので本当に良かったです。まとめる中で嫌になるぐらい over 構文を type しました…。やっぱり習うより慣れろですね。
ということで集約関数編は終わり！次回は分析関数編です！
