Hive における Window関数 を真面目に理解する - 集約関数編

Aug 8, 2018 19:29 · 3182 words · 7 minutes read #hive

モチベーション

お仕事でのデータ分析基盤における ETL 処理でも Window関数 を多用しているのですが、なんとなーく挙動を理解している程度だったので、ちゃんと公式ドキュメントを読んで1から学び直したいと思いました。

そもそも Window関数( = 窓関数, window function)とはなにか?

天下の Wikipedia 様によると、、、

窓関数もしくはウィンドウ関数 (英: window function) は結果セットを部分的に切り出した領域に集約関数を適用できる、拡張された SELECT ステートメントである。SQL:2003 以降の標準SQLで規定されている。分析関数やOLAP機能と呼ばれる場合もある。

引用:窓関数 (SQL) - Wikipedia

「結果セットを部分的に切り出した領域に集約関数を適用できる」がとてもわかりやすいですね。

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-9534HIVE-13453HIVE-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 しました…。やっぱり習うより慣れろですね。

ということで集約関数編は終わり!次回は分析関数編です!