ウィンドウ関数を使用してグループ毎の合計との比率を出すSQL

ウィンドウ関数を使う機会があまり多くなく、たまにウィンドウ関数を使った集約のSQLを書こうとして、いつも「ウィンドウ関数ってどうやって使うんだっけ？」と調べるのに飽きたので、自分用メモとして以下にまとめる。

尚、テスト環境はPostgreSQL 12である。

下記のような給与テーブル SAL がある。

payday	divcd	empcd	salary
2023-11-20	1	1001	9000
2023-11-20	1	1002	8000
2023-11-20	1	1003	9000
2023-11-20	2	2001	7000
2023-11-20	2	2002	8000
2023-10-20	1	1001	9000
2023-10-20	1	1002	8000
2023-10-20	1	1003	9000
2023-10-20	2	2001	7000
2023-10-20	2	2002	8000

この時、社員毎の給与合計を出すSQLは次のようになる。

社員毎の給与合計

SELECT
	divcd,
	empcd,
	SUM(salary) AS total_salary
FROM
	SAL
GROUP BY
	divcd, empcd

divcd	empcd	total_salary
1	1001	18000
1	1002	16000
1	1003	18000
2	2001	14000
2	2002	16000

この結果に、「その部署の給与合計に対するその社員の給与合計の割合」を追加したい。
つまり、各部署毎に発生している給与支払いの合計に対して、各社員の給与がどの程度の割合なのかを知りたい。

出力自体はdivcd+empcd単位なので、GROUP BY句をdivcdにするわけにはいかない。
divcd+empcd単位の処理の中で、divcd単位の処理を行いたい、ということになる。

この時、ウィンドウ関数を使用して次のようなSQLで抽出できる。

部署毎の給与合計に対する割合を追加

SELECT
	divcd,
	empcd,
	SUM(salary) AS total_salary,
	SUM(salary) / SUM(SUM(salary)) OVER (PARTITION BY divcd) AS salary_ratio
FROM
	SAL
GROUP BY
	divcd, empcd

divcd	empcd	total_salary	salary_ratio
1	1001	18000	0.346153846
1	1002	16000	0.307692308
1	1003	18000	0.346153846
2	2001	14000	0.466666667
2	2002	16000	0.533333333

上記で用いられているウィンドウ関数についてもう少し詳しく解説する。

ウィンドウ関数の部分

SUM(salary) / SUM(SUM(salary)) OVER (PARTITION BY divcd) AS salary_ratio

通常、SELECT句に表示されるデータは、FROM句、WHERE句、GROUP BY句、ORDER BY句等で抽出・集約・ソートがされた後のデータとなる。
例えば上記の例でいえば、divcdとempcdで集約（グルーピング）が行われており、divcd+empcd毎に異なる行が出力される。salaryは集約されているので、そのままでは出力できず、「グループ内の合計」を計算する集約関数SUMを使ってSUM(salary)とすることで出力できる。

しかし今やろうとしているのは、divcd毎のsalaryの合計値を使って割合を計算したい、という話である。これはdivcd+empcd毎のデータではない為、そのままでは表現することができない。

そこで、ウィンドウ関数の登場である。

ウィンドウ関数は、今出力しようとしているデータの並びに対して何らかの単位でまとめて扱う、という機能を持つ。PARTITION BY divcdとすれば、divcd単位で結果をまとめて扱う。これはGROUP BY句による集約のさらに上位の集約であるが、SELECT句で出力しようとしているデータ行そのものには影響を与えない仮想的な集約である為、複数のPARTITION BYを指定することができる。

ウィンドウ関数は、まずウィンドウを定義（グループ単位、並び順、範囲等）を指定した後、その範囲に対して何らかの関数を指定する。SUMもそこで利用できる関数の一つである。

ウィンドウ関数で扱うことができる列は、現在のSELECT句で扱える列に限られる。
よって、次のように直接salaryをSUMするウィンドウ関数を書くと、「salaryがGROUP BYに指定されてないから使えないよ」と怒られる。

エラー「salaryがGROUP BYに指定されていない」

SELECT
	divcd,
	empcd,
	SUM(salary) AS total_salary,
	SUM(salary) / SUM(salary) OVER (PARTITION BY divcd) AS salary_ratio
FROM
	SAL
GROUP BY
	divcd, empcd

このSELECT句でsalaryを扱いたいのであれば、何らかの集約関数に入れる必要がある。この場合はdivcd+empcd単位の集約結果がSELECT句で扱われる入力データなので（GROUP BY句で指定している為）、その結果をさらにdivcd単位で合計したい、ということならば、SUM(salary)をさらにPARTITION BYでSUMする、という形になる。つまり、

誤

	SUM(salary) OVER (PARTITION BY divcd)

ではなく、

正

	SUM(SUM(salary)) OVER (PARTITION BY divcd)

が正しい。

ここで、SUM(salary)自体は、GROUP BY句によってdivcd+empcd単位でグルーピングされる際に計算される集約関数の結果であり、ウィンドウ関数の影響を受けていないことを意識しておくと理解が早まる。