管理人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
   ,avg(age) over(partition by dept_name)    dept_avg_age
   ,avg(salary) over(partition by dept_name) dept_avg_salary
   ,avg(age) over()                          all_avg_age
   ,avg(salary) over()                       all_avg_salary
from
    members
order by
    dept_name
;

実行例はこんな感じです。

 dept_name |    dept_avg_age     |   dept_avg_salary   |     all_avg_age     |   all_avg_salary
-----------+---------------------+---------------------+---------------------+---------------------
 人事部    | 29.5000000000000000 | 275000.000000000000 | 34.5000000000000000 | 322500.000000000000
 企画部    | 32.3333333333333333 | 310000.000000000000 | 34.5000000000000000 | 322500.000000000000
 開発部    | 40.0000000000000000 | 366666.666666666667 | 34.5000000000000000 | 322500.000000000000