【SQL】分析関数のOVER句を使ってデータを集計する

SQLで集計を行う際に、行を束ねるのではなくて、全ての行に追加で集計結果のデータを持たせたい場合はありませんか？

指定したカラムごとで行を束ねたい場合はGROUP BY句を使えば実現できますが、行を束ねずに集計したい場合は別の方法を用いる必要があります。

それが、今回紹介する分析関数のOVER句です。

本記事では分析関数とは何かから始まり、OVER句の具体的な使い方まで解説します。

分析関数とは

SQLの分析関数は、グルーピングされたデータの集計結果を一つ一つの行に持たせることができる文法です。

分析関数はグルーピングされたデータに対して処理をするという点において、SUMやCOUNTのような集約関数と考え方は似ています。

集約関数との決定的な違いは、集約関数は同一の値を持つ行をグルーピングして結果を取得するという点です。例えば、下記の図のようにCOUNT関数を用いてaddressごとの数をカウントしようとすると、同じaddressの値を持つ行はグルーピングされます。

一方で、分析関数は行ごとで集計したデータを取得できます。下記の図のように、行の数は変わらないままCOUNT関数の結果を取得することができるのです。

では次に、分析関数を実現するためのクエリの書き方を見ていきましょう。

SQLで分析関数を使用するにはOVER句を使います。

OVER句を使った基本フォーマットが以下になります。

SQL

OVER (PARTITION BY カラム名 ORDER BY カラム名）

PARTITION BYはGROUP BYのようなイメージで、指定した列でグルーピングを行います。GROUP BYはクエリ全体に対してグルーピングを行いますが、PARTITION BYは特定の列に対してのみ処理が行えます。

先ほどの例のように、addressごとの人数をカウントして各行にその結果を持たせたい場合は、下記のようなクエリを書きます。

SQL

COUNT(*) OVER (PARTITION BY address）

ORDER BYは並び替えを行うことができる文法です。OVER句の中でもORDER BYを使うことができます。集計結果を並び替えたい場合はORDER BYも併せて使いましょう。

例えば、集計結果が多い順に並べ替えたい場合は、下記のようにORDER BYを指定するといった具合です。

SQL

COUNT(*) OVER (PARTITION BY address ORDER BY COUNT(*) DESC）

ここからは具体的な使用例を見て、理解を深めていきましょう。

まずは、サンプルデータとして以下のusersテーブルを用意します。

id	name	age	address
1	山田	25	東京都
2	鈴木	34	神奈川県
3	佐藤	28	神奈川県
4	大野	46	大阪府
5	小池	56	東京都
6	近藤	24	東京都
7	佐々木	38	大阪府
8	宮田	32	福岡県
9	田中	51	大阪府
10	木村	28	神奈川県

usersテーブル

usersテーブルに新しいカラム（avg_age_by_address）を作成して、都道府県ごとの平均年齢を追加してください

SQL

SELECT
  id,
  name,
  address,
  age,
  AVG(age) OVER (PARTITION BY address) AS avg_age_by_address
FROM users
ORDER BY id ASC;

ポイントは6行目です。

まず、平均年齢を求めるAVG関数を使用して、AVG(age)とします。加えて、今回は都道府県ごとの平均年齢を算出する必要があるため、OVER句とPARTITION BYを使って都道府県でグルーピングします。

この時、GROUP BY句を使ってしまうとクエリ全体でグルーピングされてしまうため、AVG(age)に対してのみ処理ができるOVER句を使います。

これによって、全ての行に対して都道府県ごとの平均年齢を持たせるカラムを作成することができました。

id	name	address	age	avg_age_by_address
1	山田	東京都	25	35
2	鈴木	神奈川県	34	30
3	佐藤	神奈川県	28	30
4	大野	大阪府	46	45
5	小池	東京都	56	35
6	近藤	東京都	24	35
7	佐々木	大阪府	38	45
8	宮田	福岡県	32	32
9	田中	大阪府	51	45
10	木村	神奈川県	28	30