SQLでグループごとの平均と、全体の平均を同時に出力する。
検証環境
postgres=# select version();
version
------------------------------------------------------------------------------------------------------------------
PostgreSQL 12.4 (Debian 12.4-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
(1 行)
今回作成するテーブル
create table members( name varchar(20), age integer, salary integer, dept_name varchar(20) ); insert into members values('青山一郎',23,'200000','人事部'); insert into members values('赤井美紀',26,'230000','企画部'); insert into members values('緑山三郎',30,'250000','開発部'); insert into members values('黄山信二',36,'350000','人事部'); insert into members values('紫五郎', 34,'320000','企画部'); insert into members values('灰谷六郎',40,'400000','開発部'); insert into members values('黒田良治',37,'380000','企画部'); insert into members values('白井聡', 50,'450000','開発部');
データ例はこんな感じです。
testdb=# select * from members; name | age | salary | dept_name ----------+-----+--------+----------- 青山一郎 | 23 | 200000 | 人事部 赤井美紀 | 26 | 230000 | 企画部 緑山三郎 | 30 | 250000 | 開発部 黄山信二 | 36 | 350000 | 人事部 紫五郎 | 34 | 320000 | 企画部 灰谷六郎 | 40 | 400000 | 開発部 黒田良治 | 37 | 380000 | 企画部 白井聡 | 50 | 450000 | 開発部 (8 行)
SQLでグループ毎の平均と、全体の平均を同時に出力する
Window関数を利用します。
select /* dept_nameでGroup byしないので、重複排除はdistinctで行う */ distinct dept_name ,round(avg(age) over(partition by dept_name) ,2) dept_avg_age ,round(avg(salary) over(partition by dept_name),2) dept_avg_salary ,round(avg(age) over() ,2) all_avg_age ,round(avg(salary) over() ,2) all_avg_salary from members order by dept_name ;
実行例はこんな感じです。
dept_name | dept_avg_age | dept_avg_sal | all_avg_age | all_avg_sal -----------+--------------+--------------+-------------+------------- 開発部 | 40.00 | 366666.67 | 34.50 | 322500.00 企画部 | 32.33 | 310000.00 | 34.50 | 322500.00 人事部 | 29.50 | 275000.00 | 34.50 | 322500.00 (3 行)
