購買履歴を顧客IDごとにsumで集計した結果を顧客マスタにある都道府県でカウントしてクロス集計したい

Question

下記のような顧客マスタと購買履歴があり、購買履歴を顧客IDごとにsumで集計した結果を顧客マスタにある都道府県でクロス集計したいです。

顧客マスタ
|顧客ID|都道府県|氏名|…|
|:--|:--:|--:|
|001|東京都|山田太郎|…|
|002|大阪府|鈴木一郎|…|
|003|北海道|佐藤華子|…|

購買履歴
|顧客ID|購入日|金額|…|
|:--|:--:|--:|
|001|2021/1/1|1000|…|
|001|2021/1/2|2000|…|
|002|2021/1/1|1500|…|
|002|2021/1/2|2500|…|
|003|2021/1/1|1200|…|
|003|2021/1/2|1000|…|

購買履歴を顧客IDでグループ化するところまではできました。

select 顧客ID,sum(金額) 
from 購買履歴
group by 顧客id order by 顧客id;

↓

顧客ID	合計金額
001	3000
002	4000
003	2200

これを合計金額を範囲別に顧客マスタにある都道府県と件数としてクロス集計したいのですが、下記のSQLでは

select 都道府県
,sum(case when sum(金額) >= 3000000 then 1 else 0 end) as "300万以上"
,sum(case when sum(金額) >= 2000000 then 1 else 0 end) as "200万以上"
,sum(case when sum(金額) >= 1000000 then 1 else 0 end) as "100万以上"
,sum(case when sum(金額) >= 500000 then 1 else 0 end) as "50万以上"
,sum(case when sum(金額) >= 200000 then 1 else 0 end) as "20万以上"
,sum(case when sum(金額) < 200000 then 1 else 0 end) as "20万未満"
from 購買履歴 left outer join 顧客マスタ on 顧客マスタ.顧客ID = 購買履歴.顧客id
group by 顧客id,都道府県 order by 顧客id

下のエラーになってしまいます。

aggregate function calls cannot be nested

得たい結果としては下記のようになります。（単位は人）
|都道府県|300万以上|200万以上|100万以上|…|
|:--|--:|--:|--:|
|北海道|150|253|567|…|
|青森|222|678|780|…|
|岩手|242|198|345|…|
|宮城|888|876|323|…|

#環境
macローカル　PostgreSQL 13.4(pgadmin4)
レコード数は
顧客マスタ：約130万行
購買履歴：約240万行
です。

ご教示いただけないでしょうか。

Answer 1

aggregate function calls cannot be nested

select 都道府県
,sum(case when sum(金額) >= 3000000

sum関数のネストがエラーの原因ですね。
クエリを多段にして、階層ごとに集計を実施すればいいでしょう。

SELECT x.都道府県
     , sum(y.over300)  _300万以上
     , sum(y.over200)  _200万以上
     , sum(y.over100)  _100万以上
     , sum(y.over50)   _50万以上
     , sum(y.over20)   _20万以上
     , sum(y.under20)  _20万未満
FROM 顧客マスタ x
JOIN
(
    SELECT 顧客ID
         , case when sum(金額) >= 3000000 then 1 else 0 end over300
         , case when sum(金額) >= 2000000 then 1 else 0 end over200
         , case when sum(金額) >= 1000000 then 1 else 0 end over100
         , case when sum(金額) >=  500000 then 1 else 0 end over50
         , case when sum(金額) >=  200000 then 1 else 0 end over20
         , case when sum(金額) <   200000 then 1 else 0 end under20
     FROM 購買履歴
    GROUP BY 顧客ID
) y
USING (顧客ID)
GROUP BY x.都道府県;

Comments

[shintaro1001]

mayu-さま
いつもありがとうございます。
示していただいたサンプルを実際のテーブル、カラム名に変えて実行してみたものの、
column "顧客id" specified in USING clause does not exist in left table
というエラーになってしまいました。
最初の質問の際に書きそびれていたのですが、「顧客ID」のカラム名が顧客マスタと購買履歴テーブルで異なっているのですが、そのせいでしょうか？
自分でいくつか試してみたもののうまくいかないため、ご教示いただけますと幸いです。

[sazi]

@SHINTARO1001 さん
USINGはJOINの省略形ですので、それに関した部分を訂正すれば良いと思います。
https://www.postgresql.jp/document/13/html/queries-table-expressions.html

[shintaro1001]

sazi様
ありがとうございます！
onに書き換えて、結合するカラムを指定したら結果が得られました。

Answer 2

解決済みですが、shintaro1001 さんがチャレンジされた方法に近い実現方法を紹介します。

create view 顧客別売上 as
select 顧客ID,sum(金額) AS 金額
from 購買履歴
group by 顧客id;

select a.都道府県
,sum(case when z.金額 >= 3000000 then 1 else 0 end) as "300万以上"
,sum(case when z.金額 between 2000000 and 2999999 then 1 else 0 end) as "200万以上"
,sum(case when z.金額 between 1000000 and 1999999 then 1 else 0 end) as "100万以上"
,sum(case when z.金額 between  500000 and  999999 then 1 else 0 end) as "50万以上"
,sum(case when z.金額 between  200000 and  499999 then 1 else 0 end) as "20万以上"
,sum(case when z.金額 < 200000 then 1 else 0 end) as "20万未満"
from 顧客別売上 as z left outer join 顧客マスタ as a on z.顧客ID = a.顧客id
group by a.都道府県

余計な補足ですが、200万以上のところに表示したいのは、200万以上300万未満の値ではないでしょうか？