[ SQL ] 集約関数と Window関数の差異イメージ

本記事では、SQL における集約関数と Window関数の違いについて確認します。

1. 集約関数
- 1.1. 集約関数の一例
- 1.2. 集約関数の実行例
2. Window関数
3. 集約関数と Window関数の比較
- 3.1. 実行計画
- 3.2. Window関数の良いところ
4. 集約関数と Widnow関数の使い分け

集約関数

集約関数とは、いくつかのグループに分けられたデータ群を対象として、各グループにつき一つつの演算結果を返却する関数です。グルーピングは主に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列でグルーピングし、各グループにおける平均を算出する

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)

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平均を算出する

select avg(age) 
from pra1;

postgres=select avg(age) from pra1;

avg
---------------------
30.8333333333333333
(1 row)

select avg(age)

from pra1;

postgres=select avg(age) from pra1;

avg

---------------------

30.8333333333333333

(1 row)

(ex3) 失敗列：グループから複数の値を取り出そうとする

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;

postgres= select age,

avg(age),

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つの返却値」という制約がありません。

Window関数の一例

dense_rank
対象グループ内における各データについて、指定値が大きい順に順序を付与します。
順序に重複があった場合でも、番号を飛ばしません。
last_value
対象グループについて、指定列が最小であるものの情報を各データに付与します。

Window関数の文法

Window関数名 over (partition by グルーピングのキー列名 order by 順序の基準)

over : どのグループに対して関数を適用するかを示します
partition by : グルーピングを行うためのキー列を指定します。group by に等しいです。
order by : グループ内でのソート条件を指定します。

実行イメージ

(ex1) sexでグルーピングし、その中でageが大きい順に順序を付与する

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)

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

30 | 0 | 2

50 | 0 | 3

60 | 0 | 4

10 | 1 | 1

20 | 1 | 2

40 | 1 | 3

(12 rows)

(ex2) sexでグルーピングし、その中でageが大きい順に連番を付与する

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)

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で最大値を求める

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)

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で最大値を求める

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)

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

30 | 30

50 | 50

60 | 60

10 | 10

20 | 20

40 | 40

(12 rows)

集約関数ではageグループにつき一つの最大値が返却されているのに対し、Window関数では各レコードに対して一つの値が返却されていることがわかります。

実行計画

集約関数では最低限グルーピング処理が行えれば計算を行うことができます。対してWindow関数はグルーピングをした上でソート処理をも必ず行わなければならないので、単純な性能比較ではgroup by に軍配が上がります。

(ex1) 集約関数maxにおける実行計画

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)

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関数における実行計画

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)

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関数の「１グループに対して複数の返却値を持つことができる」という性質から、要件によってはWindow関数の方が優れた処理を行える場合もあります。

１レコードとしてデータを扱えるということは、他のレコードとの比較ができるという側面があります。
例えば、以下の例のようなことが可能です。

(ex) sexグループ内でage順にソートした上で、各データに「前の人のID」という情報を付与する

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)

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関数の使い分け

１グループにつき１つの返却値が欲しいのであれば集約関数を、複数の返却値が欲しいのであればWindow関数を使用する
Window関数では必ずソートが発生してしまうので、単純な性能比較では、group by に軍配が上がる
Window関数は各レコードの相関の判定などの処理も行うことができる

集約関数

集約関数の一例

集約関数の実行例

Window関数

Window関数とは？

Window関数の一例

Window関数の文法

実行イメージ

集約関数と Window関数の比較

実行計画

Window関数の良いところ

集約関数と Widnow関数の使い分け

SQLカテゴリの最新記事

[ SQL ] [ Window関数 ] 前後レコードとの演算結果をselectする