请问一个累计求和的sql
按年累计值的sql一般是这样:
with t as
(select 201401 as month_id, 1 as region_id, 10 as cal_data
from dual
union
select 201401, 2, 20
from dual
union
select 201402, 1, 30 from dual
union
select 201403, 1, 40 from dual)
select month_id, region_id, cal_data, sum(cal_data) over(partition by region_id,substr(month_id,1, 4)
order by month_id) lj
from t
查询结果是:
MONTH_IDREGION_IDCAL_DATALJ
420140122020
120140111010
220140213040
320140314080
现在的问题是,由于2014年2月、3月只有region_id为1的数据,所以累计时,没有region_id为2的数据,我想要的数据是这样的:
20140122020
20140111010
20140213040
2014022020
20140314080
2014032020
请问,应该怎么写sql
------解决方案--------------------
引用:按年累计值的sql一般是这样:
with t as
(select 201401 as month_id, 1 as region_id, 10 as cal_data
from dual
union
select 201401, 2, 20
from dual
union
select 201402, 1, 30 from dual
union
select 201403, 1, 40 from dual)
select month_id, region_id, cal_data, sum(cal_data) over(partition by region_id,substr(month_id,1, 4)
order by month_id) lj
from t
查询结果是:
MONTH_IDREGION_IDCAL_DATALJ
420140122020
120140111010
220140213040
320140314080
现在的问题是,由于2014年2月、3月只有region_id为1的数据,所以累计时,没有region_id为2的数据,我想要的数据是这样的:
20140122020
20140111010
20140213040
2014022020
20140314080
2014032020
请问,应该怎么写sql
with t as
(select 201401 as month_id, 1 as region_id, 10 as cal_data
from dual
union
select 201401, 2, 20
from dual
union
select 201402, 1, 30
from dual
union
select 201403, 1, 40
from dual)
select month_id,
region_id,
cal_data,
sum(cal_data) over(partition by region_id, substr(month_id, 1, 4) order by month_id) lj
from (select tt1.month_id, tt1.region_id, nvl(tt2.cal_data, 0) cal_data
from (select month_id, region_id
from (select distinct month_id from t) t1,
(select distinct region_id from t) t2) tt1,
t tt2
where tt1.month_id = tt2.month_id(+)
and tt1.region_id = tt2.region_id(+))