[转]分析函数计算移动平均的测试

转自：http://www.itpub.net/thread-1838303-1-1.html

1.中心移动（前1行、本行、后一行共3行）
SQL> with t as(select level mon,level*10 v from dual connect by level<=12)
  2  select mon, sum(v) sv,avg(sum(v))over
  3  (ORDER BY mon ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) ma
  4  from t
  5  group by mon
  6  order by mon;

       MON         SV         MA
---------- ---------- ----------
         1         10         15
         2         20         20
         3         30         30
         4         40         40
         5         50         50
         6         60         60
         7         70         70
         8         80         80
         9         90         90
        10        100        100
        11        110        110
        12        120        115

已选择12行。

 

2.后移（当前行和前3行共4行）
SQL> with t as(select level mon,level*10 v from dual connect by level<=12)
  2  select mon, sum(v) sv,avg(sum(v))over
  3  (ORDER BY mon ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) ma1
  4  from t
  5  group by mon
  6  order by mon;

       MON         SV        MA1
---------- ---------- ----------
         1         10         10
         2         20         15
         3         30         20
         4         40         25
         5         50         35
         6         60         45
         7         70         55
         8         80         65
         9         90         75
        10        100         85
        11        110         95
        12        120        105

已选择12行。

 

3.累计值（从第1月开始）
SQL> with t as(select level mon,level*10 v from dual connect by level<=12)
  2  select mon, sum(v) sv,sum(sum(v))over
  3  (ORDER BY mon ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as1
  4  from t
  5  group by mon
  6  order by mon;

       MON         SV        AS1
---------- ---------- ----------
         1         10         10
         2         20         30
         3         30         60
         4         40        100
         5         50        150
         6         60        210
         7         70        280
         8         80        360
         9         90        450
        10        100        550
        11        110        660
        12        120        780

已选择12行。

 

4.当季累计(分别从1 4 7 10月开始)
SQL> with t as(select level mon,level*10 v from dual connect by level<=12)
  2  select mon, sum(v) sv,sum(sum(v))over
3 (ORDER BY mon ROWS BETWEEN mod(mon-1,3) PRECEDING AND CURRENT ROW) as1
  4  from t
  5  group by mon
  6  order by mon;

       MON         SV        AS1
---------- ---------- ----------
         1         10         10
         2         20         30
         3         30         60
         4         40         40
         5         50         90
         6         60        150
         7         70         70
         8         80        150
         9         90        240
        10        100        100
        11        110        210
        12        120        330

已选择12行。

 

5.--窗口中第n(本例=2)个值
SQL> with t as(select level mon,level*10 v from dual connect by level<=12)
  2  select mon, sum(v) sv,nth_value(sum(v),2)over
  3  (ORDER BY mon ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)nv2
  4  from t
  5  group by mon
  6  order by mon;

       MON         SV        NV2
---------- ---------- ----------
         1         10         20
         2         20         20
         3         30         20
         4         40         20
         5         50         20
         6         60         20
         7         70         20
         8         80         20
         9         90         20
        10        100         20
        11        110         20
        12        120         20

已选择12行。

SQL> --窗口中第n(本例=2)个值
SQL> with t as(select level mon,level*10 v from dual connect by level<=12)
  2  select mon, sum(v) sv,nth_value(sum(v),2)over
  3  (ORDER BY mon ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)nv2
  4  from t
  5  group by mon
  6  order by mon;

       MON         SV        NV2
---------- ---------- ----------
         1         10         20
         2         20         30
         3         30         40
         4         40         50
         5         50         60
         6         60         70
         7         70         80
         8         80         90
         9         90        100
        10        100        110
        11        110        120
        12        120 NULL

已选择12行。

 

 6.四等分，貌似与设想不符
SQL> with t as(select level mon,level*10 v from dual connect by level<=12)
  2  select mon, sum(v) sv,ntile(4)over
  3  (ORDER BY sum(mon) asc)nt4
  4  from t
  5  group by mon
  6  order by mon;

       MON         SV        NT4
---------- ---------- ----------
         1         10          1
         2         20          1
         3         30          1
         4         40          2
         5         50          2
         6         60          2
         7         70          3
         8         80          3
         9         90          3
        10        100          4
        11        110          4
        12        120          4

已选择12行。

 

7.累积分布
SQL> with t as(select level mon,level*10 v from dual connect by level<=12)
  2  select mon, sum(v) sv,
  3  cume_dist()over(ORDER BY sum(mon) asc)cd,
  4  percent_rank()over(ORDER BY sum(mon) asc)cd
  5  from t where mon<=5
  6  group by mon
  7  order by mon;

       MON         SV         CD         CD
---------- ---------- ---------- ----------
         1         10         .2          0
         2         20         .4        .25
         3         30         .6         .5
         4         40         .8        .75
         5         50          1          1

 

8.反向累积
SQL> with t as(select level mon,level*10 v from dual connect by level<=12)
  2  select
  3  PERCENTILE_CONT(0.6) WITHIN GROUP (ORDER BY SUM(v) ASC)c,
  4  PERCENTILE_DISC(0.6) WITHIN GROUP (ORDER BY SUM(v) ASC)d
  5  from t where mon<=5
  6  group by mon
  7  ;

         C          D
---------- ----------
        34         30