业务代码 serv_id 时间 date 资产 charge 表名 tbl 如果每天都有记录的话好办 select serv_id,date,sum(charge) over(partition by serv_id,date order by date rows between 6 preceding and 0 following) from tbl where mod(('20050530'-to_char(date,'yyyymmdd')),7)=0 order by date,serv_id 如果中间空缺了几天我暂时还没有想到方法。
上面错了,改正如下: select * from (select serv_id,date, sum(charge) over(partition by serv_id,date order by date rows between 6 preceding and 0 following) from tbl) as sumcharge where mod(('20050530'-to_char(date,'yyyymmdd')),7)=0 order by date,serv_id
时间 date
资产 charge
表名 tbl
如果每天都有记录的话好办
select serv_id,date,sum(charge) over(partition by serv_id,date order by date
rows between 6 preceding and 0 following) from tbl
where mod(('20050530'-to_char(date,'yyyymmdd')),7)=0
order by date,serv_id
如果中间空缺了几天我暂时还没有想到方法。
select * from (select serv_id,date,
sum(charge) over(partition by serv_id,date order by date rows between 6 preceding and 0 following) from tbl) as sumcharge
where mod(('20050530'-to_char(date,'yyyymmdd')),7)=0
order by date,serv_id
2 /业务代码 时间 资产
---------- ------------------- ----------
001 2005-05-16 00:00:00 1000
002 2005-05-16 00:00:00 800
003 2005-05-16 00:00:00 900
001 2005-05-19 00:00:00 700
002 2005-05-19 00:00:00 800
003 2005-05-19 00:00:00 1000
001 2005-05-24 00:00:00 600
002 2005-05-22 00:00:00 900
003 2005-05-22 00:00:00 1500
001 2005-05-23 00:00:00 1200
002 2005-05-23 00:00:00 700业务代码 时间 资产
---------- ------------------- ----------
003 2005-05-23 00:00:00 1300
001 2005-05-25 00:00:00 1000
002 2005-05-25 00:00:00 500
003 2005-05-25 00:00:00 2000
001 2005-05-27 00:00:00 1100
002 2005-05-27 00:00:00 900
003 2005-05-27 00:00:00 1200
001 2005-05-29 00:00:00 1100
002 2005-05-29 00:00:00 900
003 2005-05-29 00:00:00 1050
001 2005-05-30 00:00:00 1300业务代码 时间 资产
---------- ------------------- ----------
002 2005-05-30 00:00:00 400
003 2005-05-30 00:00:00 1800
001 2005-05-22 00:00:00 2100
002 2005-05-24 00:00:00 1100
003 2005-05-24 00:00:00 1400已选择27行。SQL> select * from (
2 select t.*,
3 sum(资产) over(partition by 业务代码 order by 时间 range between 6 PRECEDING and 0 FOLLOWING) sum_money
4 from table1 t order by 时间 desc,业务代码
5 ) where 时间 <= to_date('2005-05-30','yyyy-mm-dd')
6 and to_char(时间,'d') = to_char(to_date('2005-05-30','yyyy-mm-dd'),'d');业务代码 时间 资产 SUM_MONEY
---------- ------------------- ---------- ----------
001 2005-05-30 00:00:00 1300 5100
002 2005-05-30 00:00:00 400 3800
003 2005-05-30 00:00:00 1800 7450
001 2005-05-23 00:00:00 1200 4000
002 2005-05-23 00:00:00 700 2400
003 2005-05-23 00:00:00 1300 3800
001 2005-05-16 00:00:00 1000 1000
002 2005-05-16 00:00:00 800 800
003 2005-05-16 00:00:00 900 900已选择9行。SQL>