[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

--//随手看了该书后面的一些例子好难,还是放弃....

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-2214337/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-2214337/