管理人Kのひとりごと

デジモノレビューやプログラミングや写真など

SQLでグループ毎の平均と、全体の平均を同時に出力する

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 行)