有表tt, 字段： 日期 date, 数据 price
想实现的功能： 求各个日期前三天的price的平均值，如
表中的数据是
date price
01 10
02 20
03 30
04 40
05 10
06 30
……
想得到的结果：
date avg
01 10
02 10
03 15
04 20
05 30
06 80/3
……
求一种快速且简便的方法
------解决方案--------------------
select tt1.date,
avg = nvl((select avg(price) from
(select nownum,price from tt tt2
where tt2.date < tt1.date order by tt2.date desc)
where rownum < 4),0)
from tt tt1;
------解决方案--------------------
- SQL code
insert into c select 1, 10 ,'A' from dual union allselect 1 ,10, 'B' from dual union allselect 2, 20, 'A' from dual union allselect 3 ,30 ,'A' from dual union allselect 4 ,40, 'A' from dual union allselect 5 ,10 ,'A' from dual union allselect 6 ,30 ,'A' from dual union allselect 2 ,10 ,'B' from dual union allselect 4 ,40 ,'B' from dual union allselect 6 ,30 ,'C' from dual commit;--select * from cselect distinct dt, type, price, decode(avg(price) over(partition by type order by dt range between 3 preceding and 1 preceding), null, price, avg(price) over(partition by type order by dt range between 3 preceding and 1 preceding)) as t from c