oracle求日累计数据

1、需求：

     有一表数据accu，有日、业务月、数量三个字段，业务月是指上个月28号至当月27号。

     通过日累计求和得到某一天的业务月累计数量。例如 20211001 这一天的月累计是20210928-20211001的日数量求和得到的。

2、表数据

create table accu
(
    day_code varchar2(10),
    buss_month varchar2(10),
    fnum int
)
insert into accu values('20210927','202109',1);
insert into accu values('20210928','202110',2);
insert into accu values('20210927','202109',2);
insert into accu values('20210929','202110',3);
insert into accu values('20210930','202110',4);
insert into accu values('20211001','202110',5);

 

 3、利用over (partition by ... order by ... )求月累计数量

select day_code,buss_month,fnum,
       sum(fnum) over (partition by buss_month order by day_code) m_fnum
from accu
order by day_code

 

 

 从结果中发现有两个27号的数据，通过distinct也无法筛选掉，因为fnum是不同的，所以在累计之前通过group by将同一天的数据求和

select day_code,buss_month,fnum,
       sum(fnum) over (partition by buss_month order by day_code) m_fnum
from (select day_code,buss_month,sum(fnum) fnum from accu group by day_code,buss_month)
order by day_code