概述
说明：在使用over()函数进行统计（尤其是求和，求平均的等）的时候，有时候需要设定一个范围（时间，数值等），因此，oracle提供了窗口函数选项
rows/rang between ... preceding and ... following
分析函数的语法结构一般是：分析函数名(参数) OVER (PARTITION BY子句 ORDER BY子句 ROWS/RANGE子句)
即由以下三部分组成：
分析函数名：如sum、max、min、count、avg等聚集函数以及lead、lag行比较函数等；
over： 关键字，表示前面的函数是分析函数，不是普通的集合函数；
分析子句：over关键字后面挂号内的内容；
分析子句又由下面三部分组成：
partition by ：分组子句，表示分析函数的计算范围，不同的组互不相干；
ORDER BY： 排序子句，表示分组后，组内的排序方式；
ROWS/RANGE：窗口子句，是在分组(PARTITION BY)后，组内的子分组（也称窗口），此时分析函数的计算范围窗口，而不是PARTITON。窗口有两种，ROWS和RANGE；
下面分析rows与range窗口子句的用法，先看下面例子：
使用场景：
1 WITH t AS
2 (SELECT (CASE
3 WHEN LEVEL IN (1, 2) THEN
4 1
5 WHEN LEVEL IN (4, 5) THEN
6 6
7 ELSE
8 LEVEL
9 END) ID
10 FROM dual
11 CONNECT BY LEVEL < 10)
12 SELECT id,
13 SUM(ID) over(ORDER BY ID) default_sum,
14 SUM(ID) over(ORDER BY ID RANGE BETWEEN unbounded preceding AND CURRENT ROW) range_unbound_sum,
15 SUM(ID) over(ORDER BY ID ROWS BETWEEN unbounded preceding AND CURRENT ROW) rows_unbound_sum,
16 SUM(ID) over(ORDER BY ID RANGE BETWEEN 1 preceding AND 2 following) range_sum,
17 SUM(ID) over(ORDER BY ID ROWS BETWEEN 1 preceding AND 2 following) rows_sum
18* FROM t
SQL> /
ID DEFAULT_SUM RANGE_UNBOUND_SUM ROWS_UNBOUND_SUM RANGE_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行。从上面的例子可知：
1、窗口子句必须和order by 子句同时使用，且如果指定了order by 子句未指定窗口子句，则默认为RANGE BETWEEN unbounded preceding AND CURRENT ROW，如上例结果集中的defult_sum等于range_unbound_sum；
2、如果分析函数没有指定ORDER BY子句，也就不存在ROWS/RANGE窗口的计算；
3、range是逻辑窗口，是指定当前行对应值的范围取值，列数不固定，只要行值在范围内，对应列都包含在内，如上例中range_sum（即range 1 preceing and 2 following）例的分析结果：
当id=1时，是sum为1-1<=id<=1+2 的和，即sum=1+1+3=5(取id为1,1,3);
当id=3时，是sum为3-1<=id<=3+2 的和，即sum=3（取id为3）；
当id=6时，是sum为6-1<=id<=6+2 的和，即sum=6+6+6+7+8=33（取id为6，6，6，7，8）;
以此类推下去，结果如上例中所示。
4、rows是物理窗口，即根据order by 子句排序后，取的前N行及后N行的数据计算（与当前行的值无关，只与排序后的行号相关），如上例中rows_sum例结果，是取前1行和后2行数据的求和，分析上例rows_sum的结果：
当id=1（第一个1时）时，前一行没数，后二行分别是1和3，sum=1+1+3=5；
当id=3时，前一行id=1,后二行id都为6，则sum=1+3+6+6=16；
以此类推下去，结果如上例所示。
注：行比较分析函数lead和lag无window(窗口)子句。
实例：
SQL> select month,
2 sum(tot_sales) month_sales,
3 sum(sum(tot_sales)) over (order by month
4 rows between unbounded preceding and unbounded following) total_sales
5 from orders
6 group by month;
MONTH MONTH_SALES TOTAL_SALES
---------- ----------- -----------
1 610697 6307766
2 428676 6307766
3 637031 6307766
4 541146 6307766
5 592935 6307766
6 501485 6307766
7 606914 6307766
8 460520 6307766
9 392898 6307766
10 510117 6307766
11 532889 6307766
12 492458 6307766
已选择12行。
绿色高亮处的代码在这里发挥了关键作用，它告诉oracle统计从第一条记录开始至最后一条记录的每月销售额。这个统计在记录集形成的过程中执行了12次，这时相当费时的！但至少我们解决了问题。
unbounded preceding and unbouned following的意思针对当前所有记录的前一条、后一条记录，也就是表中的所有记录。那么假如我们直接指定从第一条记录开始直至末尾呢？看看下面的结果：
SQL> select month,
2 sum(tot_sales) month_sales,
3 sum(sum(tot_sales)) over (order by month
4 rows between 1 preceding and unbounded following) all_sales
5 from orders
6 group by month;
MONTH MONTH_SALES ALL_SALES
---------- ----------- ----------
1 610697 6307766
2 428676 6307766
3 637031 5697069
4 541146 5268393
5 592935 4631362
6 501485 4090216
7 606914 3497281
8 460520 2995796
9 392898 2388882
10 510117 1928362
11 532889 1535464
12 492458 1025347
已选择12行。
很明显这个语句错了。实际1在这里不是从第1条记录开始的意思，而是指当前记录的前一条记录。preceding前面的修饰符是告诉窗口函数执行时参考的记录数，如同unbounded就是告诉oracle不管当前记录是第几条，只要前面有多少条记录，都列入统计的范围。
窗口函数进阶－滚动统计(累积/均值)：
考虑前面提到的第2个需求：列出每月的订单总额以及截至到当前月的订单总额。也就是说2月份的记录要显示当月的订单总额和1,2月份订单总额的和。3月份要显示当月的订单总额和1,2,3月份订单总额的和，依此类推。
很明显这个需求需要在统计第N月的订单总额时，还要再统计这N个月来的订单总额之和。想想上面的语句，假如我们能够把and unbounded following换成代表当前月份的逻辑多好啊！很幸运的是Oracle考虑到了我们这个需求，为此我们只需要将语句稍微改成: curreent row就可以了。
SQL> select month,
2 sum(tot_sales) month_sales,
3 sum(sum(tot_sales)) over(order by month
4 rows between unbounded preceding and current row) current_total_sales
5 from orders
6 group by month;
MONTH MONTH_SALES CURRENT_TOTAL_SALES
---------- ----------- -------------------
1 610697 610697
2 428676 1039373
3 637031 1676404
4 541146 2217550
5 592935 2810485
6 501485 3311970
7 606914 3918884
8 460520 4379404
9 392898 4772302
10 510117 5282419
11 532889 5815308
12 492458 6307766
已选择12行。
在一些销售报表中我们会时常看到求平均值的需求，有时可能是针对全年的数据求平均值，有时会是针对截至到当前的所有数据求平均值。很简单，只需要将：
sum(sum(tot_sales))换成avg(sum(tot_sales))即可。
窗口函数进阶－根据时间范围统计：
前面我们说过，窗口函数不单适用于指定记录集进行统计，而且也能适用于指定范围进行统计的情况，例如下面这个SQL语句就统计了当天销售额和五天内的评价销售额：
select trunc(order_dt) day,
sum(sale_price) daily_sales,
avg(sum(sale_price)) over (order by trunc(order_dt)
range between interval '2' day preceding
and interval '2' day following) five_day_avg
from cust_order
where sale_price is not null
and order_dt between to_date('01-jul-2001','dd-mon-yyyy')
and to_date('31-jul-2001','dd-mon-yyyy')
为了对指定范围进行统计，Oracle使用关键字range、interval来指定一个范围。上面的例子告诉Oracle查找当前日期的前2天，后2天范围内的记录，并统计其销售平均值。
References:
http: //www.cnblogs.com/zf2011/archive/2012/07/04/2576470.html 分析函数全说明
http://blog.csdn.net/rfb0204421/article/details/7675877;
http://blog.csdn.net/rfb0204421/article/details/7673755;
http://www.ecdoer.com/post/oracle-rank-rows.html;
***************************************************************
分析函数补充; http://blog.csdn.net/clark_xu/article/details/51852277;
/************************
*标题：分析函数analytic functions
*时间：2016-07-07
*作者：clark
*************************/
分析函数：基于一组记录（行），计算聚合之的函数；
和集合函数的区别：
(1)对于每个分组，返回多个行，而不是一个行；
(2)分析的一组记录，称为窗口
(3)每一行，都有一个滑动的窗口；
(4)计算当前行的时候，窗口决定了完成这个计算的行的范围；
(5)在查询语句中，分析函数是最后的操作；即，它在where ,group by
order by ,having之后执行；
(6)因此，分析函数只能出现在select list或者 order by 语句中；
(7)分析函数一般用于计算累加，移动，集中或者报告总量
–analytic_function::=
analytic_function ( aruments ) over ( analytic_clause)
–analytic_clause::=
query_partition_clause order_by_clause windowing_clause
–注：表明分析函数作用的一个查询的结果集，它放在from,where group by,having字句之后
–query_partition_clause::=
partition by (expr)
–注：partition by 字句；根据一个或者多个expr将结果集(分析函数作用的一个查询的结果集)分割成组；
–order_by_clause::=
order siblings by (expr|position|c_alias) (asc|desc) nulls (first|last)
–注：order_by字句指定了一个数据分区中数据排序；
–(1)使用rank排列值得实惠，order by多个键值特别有用，第二个表达式可以解决第一个表达式相同的值
–注：order by a,b,c
–函数表现为：cume_dist,dense_rank,ntile,percent_rank,rank每一列返回相同的结果
–row_number分配没有给列一个不同的值；
–对于其他解析函数，结果以来窗口；（逻辑窗口，物理窗口）
/*===================================================
windows_clause:重点，难点
=====================================================*/
–windowing_clause::=
(rows|range) between (unbounded preceding|current row|value_expr preceding/following) and (unbounded following|current row|value_expr preceding/following)
(rows|range) (unbounded preceding|current row|value_expr preceding)
rows,指定窗口为物理行
range，指定窗口为逻辑的偏移量
–窗口的移动从上往下
–order by 返回的
–between and
指定窗口的开始点和结束点
–unbounded preceding：指定的窗口从第一个分区开始；是起点规范
–unbounded following：指定窗口结束在分区的最后一行，是终点规范
–value_expr preceding/following
current row
–注：指定了窗口开始在当前行；
value_expr preceding/following
–注：value_expr preceding开始，必须value_expr preceding结束；
–value_expr following 开始，必须value_expr following 结束；
rows value_expr preceding/following
–注：
value_expr 是物理偏移量，它必须是正确的数字或者表达式
value_expr是起点，必须是终点的前一行；
range value_expr preceding/following
–注：
–如果value_expr是数值，order by 必须是数值
–如果value_expr是区间值，order by 必须是日期数据类型
–如果忽略windows_clause；则窗口是unbounded preceding and current row;
/************************
*标题：avg函数
*时间：2016-07-07
*作者：clark
*************************/
–(1)聚合aggregate
select avg(out_row) from j1_dw.etl_exdw_log
–(2)分析例子
/*—————————————
****rows bwteen
(1)unbounded PRECEDING AND current row
(2)nbounded PRECEDING AND unbounded following
(3)无windows_clause 等同于nbounded PRECEDING AND unbounded following
(4)1 preceding and 1 following
—————————————–*/
–ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
SELECT unit_id,
unit_code,
tjrq_q,
out_row,
AVG(out_row) OVER(PARTITION BY unit_id, tjrq_q ORDER BY tjrq_q /ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING/) AS c_mavg
FROM etl_exdw_log
ORDER BY unit_id, unit_code, tjrq_q, out_row
–rows BETWEEN unbounded PRECEDING AND current row
SELECT unit_id,
unit_code,
tjrq_q,
out_row,
AVG(out_row) OVER(PARTITION BY unit_id, tjrq_q ORDER BY tjrq_q rows BETWEEN unbounded PRECEDING AND current row) AS c_mavg
FROM etl_exdw_log
ORDER BY unit_id, unit_code, tjrq_q, out_row
–rows BETWEEN unbounded PRECEDING AND unbounded following
SELECT unit_id,
unit_code,
tjrq_q,
out_row,
AVG(out_row) OVER(PARTITION BY unit_id,tjrq_q ORDER BY tjrq_q rows BETWEEN unbounded PRECEDING AND unbounded following) AS c_mavg
FROM etl_exdw_log
ORDER BY unit_id, unit_code, tjrq_q, out_row
/*—————————————
** rows unbounded preceding|current row|value_expr preceding
—————————————–*/
–rows unbounded PRECEDING
–等同于rows BETWEEN unbounded PRECEDING AND current row
SELECT unit_id,
unit_code,
tjrq_q,
out_row,
AVG(out_row) OVER(PARTITION BY unit_id, tjrq_q ORDER BY tjrq_q rows unbounded PRECEDING) AS c_mavg
FROM etl_exdw_log
ORDER BY unit_id, unit_code, tjrq_q, out_row
–rows 1 preceding
–当前行，和前一行做分析
–rows current row
–不做分析，和原值相同
SELECT unit_id,
unit_code,
tjrq_q,
out_row,
AVG(out_row) OVER(PARTITION BY unit_id, tjrq_q ORDER BY tjrq_q rows 1 preceding) AS c_mavg
FROM etl_exdw_log
ORDER BY unit_id, unit_code, tjrq_q, out_row
最后
以上就是温柔洋葱为你收集整理的窗口函数--配合over(...)的选项 rows/range between ... preceding and ... following--工作备忘2016/9/30的全部内容，希望文章能够帮你解决窗口函数--配合over(...)的选项 rows/range between ... preceding and ... following--工作备忘2016/9/30所遇到的程序开发问题。
如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。
发表评论 取消回复