sql求各季度总和,已知每月数据

原始数据

目标数据

实现思路

1.由于元数据中没有季度标记,需对数据处理,添加上正确的季度,
2.按shop和季度进行分组 再进行聚合

实现代码

添加季度字段

select
shop,
month,
dz,
fz,
sp,
case 
  when month between '2019-01' and '2019-03' then '1季度'
  when month between '2019-04' and '2019-06' then '2季度'
  when month between '2019-07' and '2019-09' then '3季度'
  when month between '2019-10' and '2019-12' then '4季度'
 else null
 end as `季度`
from
tablename;

查询各季度总和代码

select
shop,
`季度`,
sum(dz) as dz_sum,
sum(fz) as fz_sum,
sum(sp) as sp_sum
from    
(
select
shop,
month,
dz,
fz,
sp,
case 
  when month between '2019-01' and '2019-03' then '1季度'
  when month between '2019-04' and '2019-06' then '2季度'
  when month between '2019-07' and '2019-09' then '3季度'
  when month between '2019-10' and '2019-12' then '4季度'
 else null
 end as `季度`
from
tablename
)t1
group by shop,`季度`;