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行。