分析函数中的rangs 和rows怎么理解。
--------------------------------------------------------丁俊的 一个ppt上面的例子
WITH t AS
(SELECT (CASE WHEN LEVEL IN (1,2) THEN 1
WHENLEVEL IN (4,5) THEN 6
ELSE LEVEL END)ID
FROM dual CONNECT BY LEVEL<10)--select * from t
--默认排序,逻辑当前行
SELECT id,SUM(ID) over(ORDER BY ID) default_sum,
--逻辑行上限到当前行，此当前行是逻辑当前行，和上面一样
SUM(ID) over(ORDER BY ID
RANGE BETWEEN unbounded preceding
AND CURRENT ROW) range_unbound_sum,
--物理当前行，按排序后的行位置计算
SUM(ID) over(ORDER BY ID
ROWS BETWEENunbounded preceding
AND CURRENT ROW) rows_unbound_sum,
--下面两条与上面的比较，换成了对窗口有一定的限制，同样分为--逻辑行和物理行
SUM(ID) over(ORDER BY ID
RANGE BETWEEN 1 preceding AND 2 following) range_sum,
SUM(ID) over(ORDER BY ID
ROWS BETWEEN 1 preceding AND 2 following) rows_sum
FROM t;
-----------------------------------------------------------------------------------------
ID DEFAULT_SUM RANGE_UNBOUND_SUM ROWS_UNBOUND_SUMRANGE_SUM ROWS_SUM
---------- ----------- ----------------- ---------------- ---------- ----------
1 2
2
1
5
5
1 2
2
2
5 11
3 5
5
5
3 16
6
23
23
11 33 21
6
23
23
17 33 25
6
23
23
23 33 27
7
30
30
30 42 30
8
38
38
38 24 24
9
47
47
47 17 17
9 rows selected
倒数第二列， 怎么出来的呀 。 看不懂！！！！
|
