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

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

本記事では、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列でグルーピングし、各グループにおける平均を算出する

 

(ex2) グルーピングを行わず、データ全体におけるage平均を算出する

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

 

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が大きい順に順序を付与する

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

 

集約関数 と Window関数 の比較

集約関数とWindow関数の大きな違いは、次の点にあると言えます。

  • グループからの返却値が単一であるか、複数であるか。

以下の例で再確認します。次の例は、ageグループ内における最大のageを求めるというものです。ageでグルーピングしているので最大ageはそのグループキーのageに常に等しく、これは無意味な処理ですが、性質の確認のためと割り切って行うものとします。

 

(ex1) ageグループについて、集約関数maxで最大値を求める

(ex2) ageグループについて、Window関数last_valueで最大値を求める

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

 

実行計画

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

 

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

(ex2) Window関数における実行計画

Window関数側の実行計画では、group by に比べて sort 処理が増えていることが確認できます。

 

Window関数の良いところ

実行結果と実行計画を見た感じでは、集約関数で処理を行った方が適切であるように思われます。実際、そのような場合が多いと思われます。

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

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

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

このように、Window関数はグルーピングした上で、各レコードの相関を判定することなどもできます。group by とWindow関数、どちらも常に選択肢に入れつつ、適宜適当なものを選択するのが最も良いと思われます。

 

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

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

SQLカテゴリの最新記事