|
/************************
*标题：分析函数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,tjrq_q ORDER BY tjrq_q rows BETWEEN unbounded PRECEDING AND current row) AS c_mavg
FROM etl_exdw_log
ORDER BY unit_id,out_row
–rows BETWEEN unbounded PRECEDING AND unbounded following
SELECT 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,out_row
/*—————————————
** rows unbounded preceding|current row|value_expr preceding
—————————————–*/
–rows unbounded PRECEDING
–等同于rows BETWEEN unbounded PRECEDING AND current row
SELECT unit_id,tjrq_q ORDER BY tjrq_q rows unbounded PRECEDING) AS c_mavg
FROM etl_exdw_log
ORDER BY unit_id,out_row
–rows 1 preceding –当前行，和前一行做分析 –rows current row –不做分析，和原值相同 SELECT unit_id,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,out_row （编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
