求日均值
ACCOUNTDATE PRICE
2006-12-4 9
2006-12-7 2
2006-12-8 2
2006-12-25 508
2006-12-26 44
2006-12-27 10
2006-12-28 11
2006-12-29 2
数据值如上
求每天的日均值
及要得到每一天的平均值
如
ACCOUNTDATE PRICE
2006-12-1               0
2006-12-2               0
2006-12-3               0
2006-12-4 2.25
2006-12-5               1.8
2006-12-6               1.5
2006-12-7 1.57
2006-12-8 1.625
.....                       .....

------解决方案--------------------
SQL> select * from test where rownum <= 10;

ACCOUNTDATE PRICE
----------- ----------------------
2006-12-4 9.00
2006-12-7 2.00
2006-12-8 2.00
2006-12-25 508.00
2006-12-26 44.00
2006-12-27 10.00
2006-12-28 11.00
2006-12-29 2.00

8 rows selected


SQL> select a.sdate, round(sum(nvl(b.price,0))over(order by a.sdate)/a.num,2) from
2 (select to_date( '2006-12-01 ', 'yyyy-mm-dd ') + level -1 as sdate, level as num from dual connect by level <= 31)a
3 left join test b
4 on a.sdate = b.ACCOUNTDATE
5 order by a.sdate
6 /

SDATE ROUND(SUM(NVL(B.PRICE,0))OVER(
----------- ------------------------------
2006-12-1 0
2006-12-2 0
2006-12-3 0
2006-12-4 2.25
2006-12-5 1.8
2006-12-6 1.5
2006-12-7 1.57
2006-12-8 1.63
2006-12-9 1.44
2006-12-10 1.3
2006-12-11 1.18
2006-12-12 1.08
2006-12-13 1
2006-12-14 0.93
2006-12-15 0.87
2006-12-16 0.81
2006-12-17 0.76
2006-12-18 0.72
2006-12-19 0.68
2006-12-20 0.65

SDATE ROUND(SUM(NVL(B.PRICE,0))OVER(
----------- ------------------------------
2006-12-21 0.62
2006-12-22 0.59
2006-12-23 0.57
2006-12-24 0.54
2006-12-25 20.84
2006-12-26 21.73
2006-12-27 21.3
2006-12-28 20.93
2006-12-29 20.28
2006-12-30 19.6
2006-12-31 18.97

31 rows selected