本文参考以下文章总结而成,转载请注明本文转载来源:
分析函数入门
分析函数是什么
分析函数是Oracle用来解决复杂报表统计需求的函数,通过分析函数可以对数据进行分组分析,并对每一个分组的每一个行都返回一个值。
一般的聚合函数只能将所有的数据进行分组,并且每个分组只能有一个值。
分析函数的逻辑可以通过一般的子查询语句继续改写,但是性能上要差很多。
分析函数语法格式
FUNCTION_NAME(<参数>,…) OVER |KEEP(< PARTITION BY 表达式, …> < ORDER BY 表达式 < ASC|DESC>< NULLS FIRST | NULLS LAST>>< WINDOWING子句>)
- FUNCTION_NAME
见下方介绍 - OVER|KEEP关键字
分析函数关键字,标识这是一个分析函数。KEEP关键字仅配合几个聚合函数使用,见下方详细介绍。 - PARTITION子句
分子函数的分组条件,相当于聚合函数中的group by。缺省即将所有的数据作为一个组。(在某些文章中又将分析函数成为开窗函数,PARTITION子句就是所谓的“窗”) - ORDER BY 子句
分析函数内的排序逻辑,ASC升序,DESC降序,NULLS FIRST空值优先,NULLS LAST空值置后 - WINDOWING子句
必须与ORDER BY子句一同使用,该子句的作用是限制分析函数的偏移域,即作用范围。缺省时默认是当前分区(分组)的第一行到当前行。
语法格式 RANGE/ROW BETWEEN 参数 1 FOLLOWING/ PRECEDING AND 参数2 FOLLOWING/ PRECEDING
1.RANGE值偏移,ROW物理偏移
2.FOLLOWING表示-,PRECEDING表示+
- 值域窗(RANGE WINDOW),逻辑偏移
例:OVER(PARTITION BY t.deptno ORDER BY t.sal RANGE BETWEEN 1 PRECEDING AND 0 following)
表示在(sal+1,sal)范围内进行查询分析。逻辑值(即参数)只能是数字(不能为负数)或者日期 - 行窗,物理偏移
例:OVER(PARTITION BY t.deptno ORDER BY t.sal rows BETWEEN 1 PRECEDING AND 1 following)
表示在按照sal进行升序排序,然后在当前行的前一行,当前行,后一行的范围内进行查询分析。
- 值域窗(RANGE WINDOW),逻辑偏移
一个直观的分析函数实例
下例默认一个部门只有一个Manager,即一个Manager_id可以认为是一个部门
SELECT emp.full_name
,emp.salary
,emp.manager_id
,row_number() over(PARTITION BY emp.manager_id ORDER BY emp.salary DESC) row_number_dept --部门排行
,rownum row_number --行号
,round((rownum + 1) / 4) page_number --每4行一页
,ntile(2) over(ORDER BY emp.salary DESC) page_number_nt --平均分成两类
,AVG(emp.salary) over(PARTITION BY emp.manager_id) avg_salary_department --该部门薪水均值
,SUM(emp.salary) over(PARTITION BY emp.manager_id) sum_salary_department --该部门薪水总额
,COUNT(emp.salary) over(PARTITION BY emp.manager_id) count_emp_department --部门所有的员工
,dense_rank() over(PARTITION BY emp.manager_id ORDER BY emp.salary DESC) rank_salary_dept --该人员的部门薪水排行
,dense_rank() over(ORDER BY emp.salary DESC) rank_salary_company --该人员的全公司排行
,MIN(emp.salary) over(PARTITION BY emp.manager_id) min_salary_dept --部门的最低薪水
,MIN(emp.salary) keep(dense_rank FIRST ORDER BY emp.salary) over(PARTITION BY emp.manager_id) min_salary_dept_first --部门的最低薪水
,first_value(emp.salary) over(PARTITION BY emp.manager_id ORDER BY emp.salary) min_salary_dept_firstv --部门的最低薪水
,MAX(emp.salary) over(PARTITION BY emp.manager_id) max_salary_dept --部门的最高薪水
,MAX(emp.salary) keep(dense_rank LAST ORDER BY emp.salary) over(PARTITION BY emp.manager_id) max_salary_dept_last --部门的最高薪水
,last_value(emp.salary) over(PARTITION BY emp.manager_id ORDER BY emp.salary) max_salary_dept_lastv --部门的最高薪水
,lag(emp.full_name
,1
,'00') over(ORDER BY emp.salary DESC) last_persion --薪水在自己前一位的人
,lead(emp.full_name
,1
,'00') over(ORDER BY emp.salary DESC) next_persion --薪水在自己后一位的人
FROM fwk_tbx_employees emp
ORDER BY emp.salary DESC
详解分析函数中的函数
从上例可以看出,分析函数的分析计算逻辑是基于现有的函数而来。这些函数中,有些是我们熟知的聚合函数(SUM,AVG,COUNT等),还有一些是特殊函数。
函数名 | 作用 |
---|---|
AVG(< distinct / all > expr) | 一组或选定窗中表达式的平均值,添加distinct去重取平均值。 |
CORR(expr, expr) | 即COVAR_POP(exp1,exp2) / (STDDEV_POP(expr1) * STDDEV_POP(expr2)),两个表达式的互相关,-1(反相关) ~ 1(正相关),表示不相关 |
COUNT(< distinct> <*> < expr>) | 计数,添加distinct去重计数。 |
COVAR_POP(expr, expr) | 总体协方差 |
COVAR_SAMP(expr, expr) | 样本协方差 |
CUME_DIST | 累积分布,即行在组中的相对位置,返回0 ~ 1 |
DENSE_RANK | 行的相对排序(与ORDER BY搭配),相同的值具有一样的序数(NULL计为相同),并不留空序数 |
FIRST_VALUE | 一个组的第一个值 |
LAG(expr, < offset>,< default>) | 访问之前的行,OFFSET是缺省为1 的正数,表示相对行数,DEFAULT是当超出选定窗范围时的返回值(如第一行不存在之前行) |
LAST_VALUE | 一个组的最后一个值 |
LEAD(expr,< offset>,< default>) | 访问之后的行,OFFSET是缺省为1 的正数,表示相对行数,DEFAULT是当超出选定窗范围时的返回值(如最后行不存在之前行) |
MAX(expr) | 最大值 |
MIN(expr) | 最小值 |
NTILE(expr) | 按表达式的值和行在组中的位置编号,如表达式为4,则组分4份,分别为1 ~ 4的值,而不能等分则多出的部分在值最小的那组 |
PERCENT_RANK | 类似CUME_DIST,1/(行的序数 - 1) |
RANK | 相对序数,允许并列,并空出随后序号 |
RATIO_TO_REPORT(expr) | 表达式值 / SUM(表达式值),当前值占当前分组的比例。 |
REGR_ xxxx(expr, expr) | 线性回归函数,ORACLE提供了9种不同的适用于最小二乘回归线的线性回归函数 |
ROW_NUMBER | 排序的组中行的偏移 |
STDDEV(expr) | 标准差 |
STDDEV_POP(expr) | 总体标准差 |
STDDEV_SAMP(expr) | 样本标准差 |
SUM(expr) | 合计 |
VAR_POP(expr) | 总体方差 |
VAR_SAMP(expr) | 样本方差 |
VARIANCE(expr) | 方差 |
分析函数查询结果分析
排序影响
很多的分析函数都需要用到order by语句,同时sql语句又有自己的Order by语句。那么这两个order by语句是如何影响查询结果呢?
sql语句的order by影响最终的排序结果,分析函数的order by语句影响分析函数所得到的结果,对最终查询的产生结果的排序基本没有影响。在这里需要注意的是,对于一些比较特殊的分析函数,两个order by语句的逻辑不相同很可能产生不通的结果(例如使用rows关键字的分析函数)。
性能分析
分析函数是可以通过子查询或者嵌套sql进行改写的(特别是一些聚合函数SUM,AVG等,很容易通过子查询进行改写),那么这两种写法性能上有何区别呢?
oracle的说法是,分析函数是基于一个已经查询出的结果集进行分析。而结果集是通过主查询语句查询出来,并存储到缓存中待后续处理的。分析函数是在主查询结束后,在缓存中对已经有的结果集进行分析处理,因此使用了分析函数的sql语句其实只有一次查询表的操作(这里不考虑主查询语句比较复杂的情况)。而子查询或者嵌套sql实际上是执行两个不同的查询语句,并将查询结果进行笛卡尔积。从这个角度看,分析函数的性能要比子查询要高。当然,这并不是绝对的。比如将本身就很简单的子查询改写成分析函数,性能反而有可能降低。这需要根据情况具体分析。
一些常用的分析函数介绍
first_value()与last_value()
求每个组的最低值/最高值,具体是最低还是最高根据分析函数的order by语句而定。
rank(),dense_rank()与row_number()
求排序。这三个函数会为每条记录产生一个从1开始到N的自然数,N可能小于等于记录总数。三个函数的区别在于碰到相同数据的处理方式。
- rank()
返回一个唯一的值,当碰到相同的数据时,所有相同数据的排序序号是一样的,同时会在最后一条相同记录和下一条不同记录的排名或之间空出排名,即1,2,2,4,5.。。。 - dense_rank()
返回一个唯一的值,碰到相同的数据时,相同数据的排序序号是一样的,即,1,2,2,3,4。。。 - row_number()
返回一个唯一的值,碰到相同数据,按照记录集中的顺序依次递增排序,即1,2,3,4,5…..
lag()与lead(),求之前或之后的第N行
可以在一次查询中取出同一字段的前N行或后N行的值。可以用表连接实现相同的结果,但是性能不如分析函数。
函数包含三个参数:(arg1,arg2,arg3)
- arg1 列名
- arg2 偏移量
- arg3 超出记录窗口时的默认值