Verticaでクロス集計したいときに使えそうなSQL文
対象データ
salestable
salesdate | prod | sales | return | total |
---|---|---|---|---|
20221001 | name | 1000 | -500 | 500 |
20221002 | name2 | 800 | -100 | 700 |
20221003 | name | 100 | -100 | 0 |
… | … | … | … | … |
20221101 | name | 1000 | -500 | 500 |
… | … | … | … | … |
20221201 | name | 1000 | -500 | 500 |
というテーブルがあるときに月別にクロス集計したい
※salesdateは日付型ではなくテキスト型で持っています
集計イメージ
salesを月別に集計したい(+sale集計の千円単位も表示したい)
10月 | sales | sales(千) | 11月 | sales | sales(千) | 12月 | sales | sales(千) |
---|---|---|---|---|---|---|---|---|
... | ... | ... | ... | ... | ... |
SQL
SELECT '' as '10月' ,SUM(case SUBSTRING(salesdate,1,6) when '202210' then sales end) as 'sales' --月絞り込み ,round(SUM(case SUBSTRING(salesdate,1,6) when '202210' then sales end)/1000) as 'sales(千)' ,'' as '11月' ,SUM(case SUBSTRING(salesdate,1,6) when '202211' then sales end) as 'sales' ,round(SUM(case SUBSTRING(salesdate,1,6) when '202211' then sales end)/1000) as 'sales(千)' ,'' as '12月' ,SUM(case SUBSTRING(salesdate,1,6) when '202212' then sales end) as 'sales' ,round(SUM(case SUBSTRING(salesdate,1,6) when '202212' then sales end)/1000) as 'sales(千)' from salestable where 20221001 <= salesdate AND salesdate <= 20221231 limit 300000 --負荷軽減
salesdate(日付)がテキスト型ではなく日付型の場合はdate_truncが使えそうです。
SUBSTRING(~)の部分下記のように書き換えでできそうです。
sum(case date_trunc('month',salesdate) when '2022-10-01 00:00:00' then ...