004aggregate.sql

select 
顧客番号, 
substring(to_char(日付) from 1 for 7) as 年月, 
count(*) as 売上回数, 
sum(売上金額) as 売上金額合計, 
avg(売上金額) as 売上金額平均, 
min(売上金額) as 売上金額最小, 
max(売上金額) as 売上金額最大, 
stddev_pop(売上金額) as 売上金額標準偏差, 
stddev_pop(売上金額) / avg(売上金額) as 売上金額変動係数, 
sum(case when カテゴリー='A' then 売上金額 else 0.8*売上金額 end) as 重みづけ売上金額, 
cast(sum(case when カテゴリー='A' then 売上金額 else 0 end) as float) / nullifzero(cast(sum(売上金額) as float)) as 売上金額A割合, 
cast(sum(case when カテゴリー='B' then 売上金額 else 0 end) as float) / nullifzero(cast(sum(売上金額) as float)) as 売上金額B割合, 
sum(case when カテゴリー='A' then 売上金額 else 0 end) as 売上金額A合計, 
sum(case when カテゴリー='B' then 売上金額 else 0 end) as 売上金額B合計 
from データベース名.テーブル名 
group by 1,2 
;