[20180917]關于分析函數的range與rows的差別.txt
--//這幾天看文檔,主要想了解學習分析函數這方面内容.
--//遇到一個問題,P99.
[email protected]> @ ver1
PORT_STRING VERSION BANNER CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
with t as
(select rownum id, column_value value from table(sys.odcinumberlist (1,2,3,4.5,4.6,7,10)))
select t.*,
last_value(value) over (order by value range between unbounded preceding and 1 preceding) l1,
last_value(value) over (order by value rows between unbounded preceding and 1 preceding) l2
from t;
ID VALUE L1 L2
---------- ---------- ---------- ----------
1 1
2 2 1 1
3 3 2 2
4 4.5 3 3
5 4.6 3 4.5
6 7 4.6 4.6
7 10 7 7
7 rows selected.
--//不明白為什麼id=5,L1輸出是3,L2輸出是4.5.
--//我仔細看了問題的關鍵了解視窗函數裡面range與rows的含義.
--//看了一些文檔,才明白,實際上還真不好了解.做一個筆記:
--//range是邏輯視窗，是指定目前行對應值的範圍取值(注意包括目前行)，列數不固定，隻要行值在範圍内，對應列都包含在内，注意
--//了解指的是取值範圍.對應字段是value.
--//按照例子:range between unbounded preceding and 1 preceding, 對應id=5那行,values的取值範圍是1到4.6-1,實際上就是1到3.6
--//這樣id=5那行L1的輸出=3.
--//rows是實體視窗，即根據order by 子句排序後，取的前N行及後N行的資料計算(與目前行的值無關，隻與排序後的行号相關)，
--//對于rows between unbounded preceding and 1 preceding,對應id=5那行,行号就是1到4(注意這裡值行号),這樣L2的輸出就是5.
--//有點不好了解如果寫成如下:
with t as
(select rownum id, column_value value from table(sys.odcinumberlist (1,2,3,4.5,4.6,7,10)))
select t.*,
last_value(value) over (order by value range between unbounded preceding and current row) l1,
last_value(value) over (order by value rows between unbounded preceding and 1 preceding) l2
from t;
ID VALUE L1 L2
---------- ---------- ---------- ----------
1 1 1
2 2 2 1
3 3 3 2
4 4.5 4.5 3
5 4.6 4.6 4.5
6 7 7 4.6
7 10 10 7
7 rows selected.
--//如果1 preceding換成current row,value的取值範圍是1到4.6(id=5).
--//另外書中還介紹:
"partition by part" means that we apply an analytic function for each part independently. If it's omitted, then the
whole recordset is treated as one partition. Without an "order by" clause, window for each row covers all the rows for
the current partition so the result is the same for all rows. With an "order by" clause, window for each row covers all
rows from the beginning of the partition to the current row. This can be adjusted by specifying a windowing clause after
"order by" while the default behavior is "range between unbounded preceding and current row" (or simply "range unbounded
preceding") when "order by" is specified; otherwise it's "range between unbounded preceding and unbounded following."
--//裡面partition by ,order by 作用範圍預設從開始到目前行.
--//預設就是,這裡并不指rows:
--//預設是 range between unbounded preceding and current row
--//也可以寫 range unbounded preceding
--//range between unbounded preceding and unbounded following
--//随手看了該書後面的一些例子好難,還是放棄....