【PostgreSQLデータ集計これで縦複数行も横複数列も合計求められる】

Salesforceに集中しても、できない。Salesforceだけで実現できないことが多いから。

今回、外持ちのPostgreSQLの集計について共有していきます。

それは、縦の複数行の集計と横の複数列の集計についての詳細になる

では、ご武運を！

PostgreSQL

読んだら得ること

★　PostgreSQLの縦複数行の集計
★　PostgreSQLの横複数列の集計
★　PostgreSQLの縦複数行の集計と横複数列の集計同時実行

目次

前提

ローカルPCにPostgreSQLをインストールしたこと。

サンプルデータベースとテーブルがあること

下記のデータがあるとします。テーブルに登録したとする。

#	外部キー*（項目名：foreign_key）	区分（division）	料金１（Fee1）	数（qty）	料金２（Fee2）	料金計（FeeTotal）
1	today001	基本	1000	1	1500	2500
2	today001	管理	1100	2	1500	2600
3	today001	応用	1200	3	1500	2700
4	today002	基本	1300	4	1500	2800
5	today002	管理	1200	5	1500	2700
6	today002	応用	2100	6	1500	3600
7	today003	基本	3100	7	1500	4600
8	today003	管理	1600	8	1500	3100
9	today003	応用	1250	9	1500	2750

やりたいこと

外部キーをみれば、わかるので、なにかのデータの明細データである。

この明細データを使い、料金１、数、料金計を集計し、親の項目に設定したいだ。

設定詳細

料金集計（縦の複数行の集計）

区分が基本の場合、料金１を親の基本料金項目に設定する

区分が管理の場合、料金１を親の管理料金項目に設定する

区分が応用の場合、料金１を親の応用料金項目に設定する

数集計（縦の複数行の集計）

区分が基本の場合、数を親の基本数項目に設定する

区分が管理の場合、数を親の管理数項目に設定する

区分が応用の場合、数を親の応用数項目に設定する

料金計の集計（縦の複数行の集計）

区分が基本の場合、料金計を親の基本料金計項目に設定する

区分が管理の場合、料金計を親の管理料金計項目に設定する

区分が応用の場合、料金計を親の応用料金計項目に設定する

料金計の合計集計（横の複数列の集計）

区分が基本の場合、料金計を親の基本料金計項目に設定する

区分が管理の場合、料金計を親の管理料金計項目に設定する

区分が応用の場合、料金計を親の応用料金計項目に設定する

PostgreSQLの縦複数行の集計

前提のデータテーブルを、条件付きで縦の複数行の集計を行う

case when関数で条件付き抽出

Select foreign_key
, division
, case when division = '基本' then Fee1 else null end as basice_Fee1
, case when division = '管理' then Fee1 else null end as manage_Fee1
, case when division = '応用' then Fee1 else null end as app_Fee1
FROM yourtable

実行結果（Step1）

#	foreign_key	division	basice_Fee1	manage_Fee1	app_Fee1
1	today001	基本	1000
2	today001	管理		1100
3	today001	応用			1200
4	today002	基本	1300
5	today002	管理		1300
6	today002	応用			2100
7	today003	基本	3100
8	today003	管理		1600
9	today003	応用			1250

case when関数で条件付き抽出した上で、max集計関数で複数行を１行にマージする

Select foreign_key
, division
, max(case when division = '基本' then Fee1 else null end) as basice_Fee1
, max(case when division = '管理' then Fee1 else null end) as manage_Fee1
, max(case when division = '応用' then Fee1 else null end) as app_Fee1
FROM yourtable
group by foreign_key

実行結果（Step２）

#	foreign_key	basice_Fee1	manage_Fee1	app_Fee1
1	today001	1000	1100	1200
2	today002	1300	1300	2100
3	today003	3100	1600	1250

PostgreSQLの横複数列の集計

スキーマに存在する列の集計

select a+b from yourtable

これでできる

条件付きで抽出した上で、更に集計したテーブルの横複数列の集計

Select foreign_key
, division
, max(case when division = '基本' then Fee1 else null end) as basice_Fee1
, max(case when division = '管理' then Fee1 else null end) as manage_Fee1
, max(case when division = '応用' then Fee1 else null end) as app_Fee1
FROM yourtable
group by foreign_key

上記を例では

１．case whenで条件付きでデータを抽出した

２．max関数で集計した

１と２を実施したテーブルに対する集計する場合、抽出した結果を別名にすれば、できる。

Select newTB.foreign_key
,newTB.basice_Fee1 + newTB.manage_Fee1 + newTB.app_Fee1 AS col_total
FROM
　(Select foreign_key
　　, division
　　, max(case when division = '基本' then Fee1 else null end) as basice_Fee1
　　, max(case when division = '管理' then Fee1 else null end) as manage_Fee1
　　, max(case when division = '応用' then Fee1 else null end) as app_Fee1
　　FROM yourtable
　　group by foreign_key) AS newTB

実行結果

#	foreign_key	col_total
1	today001	3300
2	today002	4700
3	today003	5950

PostgreSQLの縦複数行の集計と横複数列の集計同時実行

前提条件のデータを縦複数行の集計と横複数列の集計同時実行するSQLは下記になる

Select newTB.foreign_key
,newTB.basice_Fee1
,newTB.manage_Fee1
,newTB.app_Fee1
,newTB.basice_Fee1 + newTB.manage_Fee1 + newTB.app_Fee1 AS col_total
FROM
　(Select foreign_key
　　, division
　　, max(case when division = '基本' then Fee1 else null end) as basice_Fee1
　　, max(case when division = '管理' then Fee1 else null end) as manage_Fee1
　　, max(case when division = '応用' then Fee1 else null end) as app_Fee1
　　FROM yourtable
　　group by foreign_key) AS newTB

実行結果

#	foreign_key	basice_Fee1	manage_Fee1	app_Fee1	col_total
1	today001	1000	1100	1200	3300
2	today002	1300	1300	2100	4700
3	today003	3100	1600	1250	5950

サピエンス全史期間限定セット（全２巻セット） 文明の構造と人類の幸福 /河出書房新社/ユヴァル・ノア・ハラリ

楽天市場で購入

Amazonで購入