如题。数据大致如下：
日期 数值
20121129 2
20121129 1
20121129 3
20121130 4
20121130 3
20121130 1
20121130 2
20121201 3
20121201 2
20121201 1
20121202 3
20121202 4
20121202 2
20121202 1
20121211 3
20121211 2
20121211 1
20121212 1
20121212 2
20121212 3
20121212 4
使用窗口函数写法如下：
SELECT tef.日期,AVG(SUM(tef.数值)) OVER (PARTITION BY tef.日期 ORDER BY TRUNC(TO_DATE(tef.日期,'yyyy-mm-dd'))
RANGE BETWEEN INTERVAL '30' DAY preceding AND INTERVAL '0' DAY following ) tstst
FROM 表 tef
GROUP BY tef.日期
;
使用这种写法向前30天由于日期不连续，只能获取连续日期的数据，比如20121212只能汇总20121211+20121212的数据无法汇总到20121202的数据，请教如何修改语句获取连续日期的数据汇总满足需求？
------解决方案--------------------
------解决方案--------------------
比较好懂的方法 获取前30天每天的平均值
- SQL code
with t1 as (select '1' 编号,'20121127' 日期,'2' 数值 from dual union allselect '2', '20121128','1' from dual union allselect '3', '20121129','3' from dual union allselect '4', '20121130','4' from dual union allselect '5', '20121130','3' from dual union allselect '6', '20121130','1' from dual union allselect '7', '20121130','2' from dual union allselect '8', '20121201','3' from dual union allselect '9', '20121201','2' from dual union allselect '10', '20121202','2' from dual union allselect '11', '20121203','2' from dual union allselect '12', '20121204','2' from dual union allselect '13', '20121205','2' from dual union allselect '14', '20121206','4' from dual union allselect '15', '20121207','3' from dual union allselect '16', '20121208','6' from dual union allselect '17', '20121209','7' from dual union allselect '18', '20121210','3' from dual union allselect '19', '20121211','5' from dual union allselect '20', '20121212','2' from dual union allselect '21', '20121213','2' from dual union allselect '22', '20121214','2' from dual union allselect '23', '20121215','2' from dual union allselect '24', '20121216','2' from dual union allselect '25', '20121217','2' from dual union allselect '26', '20121218','2' from dual union allselect '27', '20121219','2' from dual union allselect '28', '20121220','2' from dual union allselect '29', '20121221','2' from dual union allselect '30', '20121222','2' from dual union allselect '31', '20121223','2' from dual union allselect '32', '20121224','2' from dual union allselect '33', '20121225','2' from dual union allselect '34', '20121226','2' from dual union allselect '35', '20121227','2' from dual union allselect '36', '20121228','2' from dual union allselect '37', '20121228','2' from dual union allselect '38', '20121228','2' from dual union allselect '39', '20121228','2' from dual)select t1.日期,avg(sum(t1.数值)/count(t1.数值)) over(partition by t1.日期 order by t1.日期) a_count from t1,( select 日期,rownum from ( select distinct 日期 from t1 order by 日期 ) where rownum<=30) t2where t1.日期 in t2.日期group by t1.日期order by t1.日期 日期 a_count-------------------------------1 20121127 22 20121128 13 20121129 34 20121130 2.55 20121201 2.56 20121202 27 20121203 28 20121204 29 20121205 210 20121206 411 20121207 312 20121208 613 20121209 714 20121210 315 20121211 516 20121212 217 20121213 218 20121214 219 20121215 220 20121216 221 20121217 222 20121218 223 20121219 224 20121220 225 20121221 226 20121222 227 20121223 228 20121224 229 20121225 230 20121226 2