本記事では、SQL における 集約関数 と Window関数の違いについて確認します。
集約関数
集約関数とは、いくつかのグループに分けられたデータ群を対象として、各グループにつき一つつの演算結果を返却する関数です。グルーピングは主にGroupBy句によって行われますが、GroupBy句を用いない場合でも集約関数を使用することができます。その時は、データ全体を一つのデータとみなします。
集約関数の一例
- sum
対象グループについて、指定列中から最大の値を検索し、返却します。 - count
対象グループについて、指定列中から重複しない値の個数を検索し、返却します。 - avg
対象グループについて、指定列中に存在する値の平均値を返却します。
集約関数の実行例
全データ
id | age | sex
—-+—–+—–
1 | 10 | 1
2 | 20 | 1
3 | 30 | 0
4 | 40 | 1
5 | 50 | 0
6 | 60 | 0
7 | 60 | 0
10 | 20 | 1
11 | 30 | 0
12 | 20 | 0
13 | 20 | 0
14 | 10 | 1
(12 rows)
(ex1) age列でグルーピングし、各グループにおける平均を算出する
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
select age, count(age) from pra1 group by age order by age; postgres=select age, count(age) from pra1 group by age order by age; age | count -----+------- 10 | 2 20 | 4 30 | 2 40 | 1 50 | 1 60 | 2 (6 rows) |
(ex2) グルーピングを行わず、データ全体におけるage平均を算出する
1 2 3 4 5 6 7 8 9 10 11 |
select avg(age) from pra1; postgres=select avg(age) from pra1; avg --------------------- 30.8333333333333333 (1 row) |
(ex3) 失敗列:グループから複数の値を取り出そうとする
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
postgres= select age, avg(age), id from pra1 group by age; -- age でグルーピングされたグループにおいて、age は一意であることが保証される。 -- けれどもageグループ内ではage以外の項目については一意では無いため、 -- それをグループから取り出そうとするとエラーになる。 postgres=select age, avg(age), id from pra1 group by age; 2018-11-22 20:02:31.586 JST [31744] ERROR: syntax error at or near "postgres" at character 1 2018-11-22 20:02:31.586 JST [31744] STATEMENT: postgres=select age, avg(age), id from pra1 group by age; ERROR: syntax error at or near "postgres" LINE 1: postgres=select age, avg(age), id from pra1 group by age; |
Window関数
Window関数とは?
Window関数は、対象データをグループ化した上で計算を行う点については集約関数と同じです。違いは、Window関数が返却する値はグループにつき一つ、ではなく、グループ内の各データ一つにつき一つである、という点です。
先ほどの集約関数例では、age列でグルーピングした各グループに対してcountを使用しました。この時、例えば age=20 のグループについて返却される値は 4 (age20のグループには4つのデータが存在する、の意) という単一のものでした。
Window関数では、このような「1グループにつき1つの返却値」という制約がありません。
Window関数の一例
- dense_rank
対象グループ内における各データについて、指定値が大きい順に順序を付与します。
順序に重複があった場合でも、番号を飛ばしません。 - last_value
対象グループについて、指定列が最小であるものの情報を各データに付与します。
Window関数の文法
Window関数名 over (partition by グルーピングのキー列名 order by 順序の基準)
- over : どのグループに対して関数を適用するかを示します
- partition by : グルーピングを行うためのキー列を指定します。group by に等しいです。
- order by : グループ内でのソート条件を指定します。
実行イメージ
(ex1) sexでグルーピングし、その中でageが大きい順に順序を付与する
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
select age, sex , dense_rank() over (partition by sex order by age) from pra1; postgres=select age, sex , dense_rank() over (partition by sex order by age) from pra1; age | sex | dense_rank -----+-----+------------ 20 | 0 | 1 20 | 0 | 1 30 | 0 | 2 30 | 0 | 2 50 | 0 | 3 60 | 0 | 4 60 | 0 | 4 10 | 1 | 1 10 | 1 | 1 20 | 1 | 2 20 | 1 | 2 40 | 1 | 3 (12 rows) |
(ex2) sexでグルーピングし、その中でageが大きい順に連番を付与する
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
select age, sex , row_number() over (partition by sex order by age) from pra1; postgres=select age, sex , row_number() over (partition by sex order by age) from pra1; age | sex | row_number -----+-----+------------ 20 | 0 | 1 20 | 0 | 2 30 | 0 | 3 30 | 0 | 4 50 | 0 | 5 60 | 0 | 6 60 | 0 | 7 10 | 1 | 1 10 | 1 | 2 20 | 1 | 3 20 | 1 | 4 40 | 1 | 5 (12 rows) |
集約関数 と Window関数 の比較
集約関数とWindow関数の大きな違いは、次の点にあると言えます。
- グループからの返却値が単一であるか、複数であるか。
以下の例で再確認します。次の例は、ageグループ内における最大のageを求めるというものです。ageでグルーピングしているので最大ageはそのグループキーのageに常に等しく、これは無意味な処理ですが、性質の確認のためと割り切って行うものとします。
(ex1) ageグループについて、集約関数maxで最大値を求める
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
select age, max(age) from pra1 group by age; postgres=select age, max(age) from pra1 group by age; age | max -----+----- 60 | 60 40 | 40 30 | 30 10 | 10 50 | 50 20 | 20 (6 rows) |
(ex2) ageグループについて、Window関数last_valueで最大値を求める
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
select age, last_value(age) over (partition by sex order by age) from pra1; postgres=select age, last_value(age) over (partition by sex order by age) from pra1; age | last_value -----+------------ 20 | 20 20 | 20 30 | 30 30 | 30 50 | 50 60 | 60 60 | 60 10 | 10 10 | 10 20 | 20 20 | 20 40 | 40 (12 rows) |
集約関数ではageグループにつき一つの最大値が返却されているのに対し、Window関数では各レコードに対して一つの値が返却されていることがわかります。
実行計画
集約関数では最低限グルーピング処理が行えれば計算を行うことができます。対してWindow関数はグルーピングをした上でソート処理をも必ず行わなければならないので、単純な性能比較ではgroup by に軍配が上がります。
(ex1) 集約関数maxにおける実行計画
1 2 3 4 5 6 7 8 9 10 11 12 |
explain select age, max(age) from pra1 group by age; postgres=explain select age, max(age) from pra1 group by age; QUERY PLAN -------------------------------------------------------------- HashAggregate (cost=40.60..42.60 rows=200 width=8) Group Key: age Seq Scan on pra1 (cost=0.00..30.40 rows=2040 width=4) (3 rows) |
(ex2) Window関数における実行計画
1 2 3 4 5 6 7 8 9 10 11 12 13 |
explain select age, last_value(age) over (partition by sex order by age) from pra1; postgres=explain select age, last_value(age) over (partition by sex order by age) from pra1; QUERY PLAN -------------------------------------------------------------------- WindowAgg (cost=142.54..183.34 rows=2040 width=12) Sort (cost=142.54..147.64 rows=2040 width=8) Sort Key: sex, age Seq Scan on pra1 (cost=0.00..30.40 rows=2040 width=8) (4 rows) |
Window関数側の実行計画では、group by に比べて sort 処理が増えていることが確認できます。
Window関数の良いところ
実行結果と実行計画を見た感じでは、集約関数で処理を行った方が適切であるように思われます。実際、そのような場合が多いと思われます。
しかし、Window関数の「1グループに対して複数の返却値を持つことができる」という性質から、要件によってはWindow関数の方が優れた処理を行える場合もあります。
1レコードとしてデータを扱えるということは、他のレコードとの比較ができるという側面があります。
例えば、以下の例のようなことが可能です。
(ex) sexグループ内でage順にソートした上で、各データに「前の人のID」という情報を付与する
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
postgres= select id, sex, age, max(id) over (partition by sex order by age rows between 1 preceding and 1 preceding) as 前の人のID from pra1; id | sex | age | 前の人のid ----+-----+-----+------------ 12 | 0 | 20 | 13 | 0 | 20 | 12 3 | 0 | 30 | 13 11 | 0 | 30 | 3 5 | 0 | 50 | 11 6 | 0 | 60 | 5 7 | 0 | 60 | 6 14 | 1 | 10 | 1 | 1 | 10 | 14 2 | 1 | 20 | 1 10 | 1 | 20 | 2 4 | 1 | 40 | 10 (12 rows) |
このように、Window関数はグルーピングした上で、各レコードの相関を判定することなどもできます。group by とWindow関数、どちらも常に選択肢に入れつつ、適宜適当なものを選択するのが最も良いと思われます。
集約関数 と Widnow関数 の使い分け
- 1グループにつき1つの返却値が欲しいのであれば集約関数を、複数の返却値が欲しいのであればWindow関数を使用する
- Window関数では必ずソートが発生してしまうので、単純な性能比較では、group by に軍配が上がる
- Window関数は各レコードの相関の判定などの処理も行うことができる