统计店铺月累计收入

sid,dt,money
shop1,2019-01-18,500
shop1,2019-02-10,500
shop1,2019-02-10,200
shop1,2019-02-11,600
shop1,2019-02-12,400
shop1,2019-02-13,200
shop1,2019-02-15,100
shop1,2019-03-05,180
shop1,2019-04-05,280
shop1,2019-04-06,220
shop2,2019-02-10,100
shop2,2019-02-11,100
shop2,2019-02-13,100
shop2,2019-03-15,100
shop2,2019-04-15,100

第一步 将日期转成年月，将金额转成 double

select
	sid,
	date_format(dt,'yyyy-MM') mth ,
	cast(money as double) money
from
v_shop

第二步 分组聚合

select
  sid,
  mth,
  sum(money) mth_income
from
(
	select
	  sid,
	date_format(dt,'yyyy-MM') mth ,
	  cast(money as double) money
	from
v_shop
) t1 group by sid,mth

第三步 搞个窗口函数 进行聚合排序

select 
sid,
mth,
mth_income,
sum(mth_income) over(partition by sid order by mth rows between unbounded preceding and current row) total_money
from
(
select
sid,
mth,
sum(money) mth_income 
from
(
	select
	  sid,
	date_format(dt,'yyyy-MM') mth ,
	cast(money as double) money
	from
       v_shop
  )t1 group by sid,mth
)t2