[20180917]關於分析函式的range與rows的區別.txt

[20180917]關於分析函式的range與rows的區別.txt

--//這幾天看文件<Oracle SQL Revealed.pdf>,主要想了解學習分析函式這方面內容.
--//遇到一個問題,P99.

SCOTT@test01p> @ 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

--//隨手看了該書後面的一些例子好難,還是放棄....