窗⼝函数--配合over（...）的选项
说明：在使⽤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
oracle中trunc函数用法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 1
7 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,
2sum(tot_sales) month_sales,
3sum(sum(tot_sales)) over (order by month
4 rows between unbounded preceding and unbounded following) total_sales
5from orders
6group by month;
MONTH MONTH_SALES TOTAL_SALES
---------- ----------- -----------
16106976307766
24286766307766
36370316307766
45411466307766
55929356307766
65014856307766
76069146307766
84605206307766
93928986307766
105101176307766
115328896307766
124924586307766
已选择12⾏。
绿⾊⾼亮处的代码在这⾥发挥了关键作⽤，它告诉oracle统计从第⼀条记录开始⾄最后⼀条记录的每⽉销售额。这个统计在记录集形成的过程中执⾏了12次，这时相当费时的！但⾄少我们解决了问题。
unbounded preceding and unbouned following的意思针对当前所有记录的前⼀条、后⼀条记录，也就是表中的所有记录。那么假如我们直接指定从第⼀条记录开始直⾄末尾呢？看看下⾯的结果：
SQL>select month,
2sum(tot_sales) month_sales,
3sum(sum(tot_sales)) over (order by month
4 rows between1 preceding and unbounded following) all_sales
5from orders
6group by month;
MONTH MONTH_SALES ALL_SALES
---------- ----------- ----------
16106976307766
24286766307766
36370315697069
45411465268393
55929354631362
65014854090216
76069143497281
84605202995796
93928982388882
105101171928362
115328891535464
124924*********
已选择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,
2sum(tot_sales) month_sales,
3sum(sum(tot_sales)) over(order by month
4 rows between unbounded preceding and current row) current_total_sales
5from orders
6group by month;
MONTH MONTH_SALES CURRENT_TOTAL_SALES
---------- ----------- -------------------
1610697610697
24286761039373
36370311676404
45411462217550
55929352810485
65014853311970
76069143918884
84605204379404
93928984772302
105101175282419
115328895815308
124924586307766
已选择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: //wwwblogs/zf2011/archive/2012/07/04/2576470.html 分析函数全说明
***************************************************************
/************************
*标题：分析函数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::=
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论