求日均值
ACCOUNTDATEPRICE
2006-12-49
2006-12-72
2006-12-82
2006-12-25508
2006-12-2644
2006-12-2710
2006-12-2811
2006-12-292
数据值如上
求每天的日均值
及要得到每一天的平均值
如
ACCOUNTDATEPRICE
2006-12-1 0
2006-12-2 0
2006-12-3 0
2006-12-42.25
2006-12-5 1.8
2006-12-6 1.5
2006-12-71.57
2006-12-81.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
