窗口函数--配合over(...)的选项 rows/range between ... preceding and ... following--工作备忘2016/9/30_数据库_Richie's 残酷舞台

CSDN博客 · · 492 次点击 · · 开始浏览    
这是一个创建于 的文章,其中的信息可能已经有所发展或是发生改变。



说明:在使用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


492 次点击  
加入收藏 微博
暂无回复
添加一条新回复 (您需要 登录 后才能回复 没有账号 ?)
  • 请尽量让自己的回复能够对别人有帮助
  • 支持 Markdown 格式, **粗体**、~~删除线~~、`单行代码`
  • 支持 @ 本站用户;支持表情(输入 : 提示),见 Emoji cheat sheet
  • 图片支持拖拽、截图粘贴等方式上传