有表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
……求一种快速且简便的方法
表中的数据是
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
……求一种快速且简便的方法
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;
表中的数据是
date price type
01 10 A
01 10 B
02 20 A
03 30 A
04 40 A
05 10 A
06 30 A
02 10 B
03 20 A
04 40 B
05 10 A
06 30 C……想得到的结果:
date avg type
01 10 A
02 10 A
03 15 A
04 20 A
05 30 A
06 80/3 A
01 B
……求一种快速且简便的方法
insert into c
select 1, 10 ,'A' from dual union all
select 1 ,10, 'B' from dual union all
select 2, 20, 'A' from dual union all
select 3 ,30 ,'A' from dual union all
select 4 ,40, 'A' from dual union all
select 5 ,10 ,'A' from dual union all
select 6 ,30 ,'A' from dual union all
select 2 ,10 ,'B' from dual union all
select 4 ,40 ,'B' from dual union all
select 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
哥们,你别胡乱用关键字做字段名好不?上面应该是你想要的,,,日期,我懒得用了,我用整数来替代.
(
dt int,
price int,
type varchar2(2)
);
建表忘记贴上了,汗