徐长亮的专栏

自始至终,一以贯之; 技术源于实践,结论来自验证;

oracle分析函数:一、窗口子句的使用

/************************
*标题:分析函数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

阅读更多
版权声明:本文为博主原创文章(原文:blog.csdn.net/clark_xu 徐长亮的专栏),未经博主允许不得转载。 https://blog.csdn.net/u011538954/article/details/51852277
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

不良信息举报

oracle分析函数:一、窗口子句的使用

最多只允许输入30个字

加入CSDN,享受更精准的内容推荐,与500万程序员共同成长!
关闭
关闭