oracle分析函数以及窗口,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