DataScience100Knock-S43

S-043: レシート明細テーブル（receipt）と顧客テーブル（customer）を結合し、性別（gender）と年代（ageから計算）ごとに売上金額（amount）を合計した売上サマリテーブル（sales_summary）を作成せよ。性別は0が男性、1が女性、9が不明を表すものとする。

ただし、項目構成は年代、女性の売上金額、男性の売上金額、性別不明の売上金額の4項目とすること（縦に年代、横に性別のクロス集計）。また、年代は10歳ごとの階級とすること。

最終的に次のようにした:

code:final.sql

with recursive ls(l,u) as (

select 0,10

union all

select l+10, u+10 from ls

), levels as (

select l,u, cast(l as text) || '-' || cast(u as text) as title

from ls

limit 20

),agesummary as (

select

c.age,

coalesce(sum(r.amount) filter (where c.gender_cd = '1'),0) as women,

coalesce(sum(r.amount) filter (where c.gender_cd = '0'),0) as men,

coalesce(sum(r.amount) filter (where c.gender_cd = '9'),0) as unknown

from receipt r

inner join customer c

on r.customer_id = c.customer_id

group by age

)

select

l.title,

sum(a.women) as women,

sum(a.men) as men,

sum(a.unknown) as unknown

from agesummary a

inner join levels l

on l.l <= a.age and a.age < l.u

group by l.title

order by l.title;

https://gyazo.com/e52091dd30fecc0fc4c58b353daf116e

過程

これ結構大変。階級を作る良いやり方がわからずとりあえず次のようにやってみた

code:firstdraft.sql

with agesummary as (

select

c.age,

coalesce(sum(r.amount) filter (where c.gender_cd = '1'),0) as women,

coalesce(sum(r.amount) filter (where c.gender_cd = '0'),0) as men,

coalesce(sum(r.amount) filter (where c.gender_cd = '9'),0) as unknown

from receipt r

inner join customer c

on r.customer_id = c.customer_id

group by age

), levels as (

select

row_number() over() as idx,

(row_number() over()) * 10 as upper,

(row_number() over() - 1) * 10 as lower,

cast((row_number() over() - 1) * 10 as text) || '-' as title

from receipt

)

select

l.title,

sum(a.women) as women,

sum(a.men) as men,

sum(a.unknown) as unknown

from levels l

inner join agesummary a

on l.lower <= a.age and a.age < l.upper

group by l.title

order by l.title;

levels定義の改良を試みる

code:second.sql

with recursive ls(l,u) as (

select 0,10

union all

select l+10, u+10 from ls

), levels as (

select l,u, cast(l as text) || '-' || cast(u as text) as title

from ls

limit 20

),agesummary as (

select

c.age,

coalesce(sum(r.amount) filter (where c.gender_cd = '1'),0) as women,

coalesce(sum(r.amount) filter (where c.gender_cd = '0'),0) as men,

coalesce(sum(r.amount) filter (where c.gender_cd = '9'),0) as unknown

from receipt r

inner join customer c

on r.customer_id = c.customer_id

group by age

)

select

l.title,

sum(a.women),

sum(a.men),

sum(a.unknown)

from agesummary a

inner join levels l

on l.l <= a.age and a.age < l.u

group by l.title

order by l.title;