日期:2014-05-17 浏览次数:21084 次
with t1 as ( select '1' 编号,'20121127' 日期,'2' 数值 from dual union all select '2', '20121128','1' from dual union all select '3', '20121129','3' from dual union all select '4', '20121130','4' from dual union all select '5', '20121130','3' from dual union all select '6', '20121130','1' from dual union all select '7', '20121130','2' from dual union all select '8', '20121201','3' from dual union all select '9', '20121201','2' from dual union all select '10', '20121202','2' from dual union all select '11', '20121203','2' from dual union all select '12', '20121204','2' from dual union all select '13', '20121205','2' from dual union all select '14', '20121206','4' from dual union all select '15', '20121207','3' from dual union all select '16', '20121208','6' from dual union all select '17', '20121209','7' from dual union all select '18', '20121210','3' from dual union all select '19', '20121211','5' from dual union all select '20', '20121212','2' from dual union all select '21', '20121213','2' from dual union all select '22', '20121214','2' from dual union all select '23', '20121215','2' from dual union all select '24', '20121216','2' from dual union all select '25', '20121217','2' from dual union all select '26', '20121218','2' from dual union all select '27', '20121219','2' from dual union all select '28', '20121220','2' from dual union all select '29', '20121221','2' from dual union all select '30', '20121222','2' from dual union all select '31', '20121223','2' from dual union all select '32', '20121224','2' from dual union all select '33', '20121225','2' from dual union all select '34', '20121226','2' from dual union all select '35', '20121227','2' from dual union all select '36', '20121228','2' from dual union all select '37', '20121228','2' from dual union all select '38', '20121228','2' from dual union all select '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 ) t2 where t1.日期 in t2.日期 group by t1.日期 order by t1.日期 日期 a_count ------------------------------- 1 20121127 2 2 20121128 1 3 20121129 3 4 20121130 2.5 5 20121201 2.5 6 20121202 2 7 20121203 2 8 20121204 2 9 20121205 2 10 20121206 4 11 20121207 3 12 20121208 6 13 20121209 7 14 20121210 3 15 20121211 5 16 20121212 2 17 20121213 2 18 20121214 2 19 20121215 2 20 20121216 2 21 20121217 2 22 20121218 2 23 20121219 2 24 20121220 2 25 2012122