中国空间站首次太空授课,网上做网站赚钱,旅游网站建设推广,榆林公司网站建设Oracle 分析函数详解
在Oracle数据库中#xff0c;分析函数#xff08;Analytical Functions#xff09;是一类非常强大的工具#xff0c;它们允许在查询结果集上进行复杂的计算和分析#xff0c;而无需使用自连接或子查询等复杂操作。本文将详细介绍Oracle分析函数的使用…Oracle 分析函数详解
在Oracle数据库中分析函数Analytical Functions是一类非常强大的工具它们允许在查询结果集上进行复杂的计算和分析而无需使用自连接或子查询等复杂操作。本文将详细介绍Oracle分析函数的使用方法和应用场景包括排名函数、统计函数、取首尾记录、取上下行记录以及滑动窗口等功能。
一、排名函数
1. RANK()
RANK()函数用于计算排序后的排名相同值排名相同排名之间可能有间隔。
SELECT E.ENAME, E.JOB, E.SAL, E.DEPTNO,RANK() OVER(PARTITION BY E.DEPTNO ORDER BY E.SAL DESC) AS RANK
FROM EMP E;
说明PARTITION BY E.DEPTNO将数据按部门分组ORDER BY E.SAL DESC在每个部门内按工资降序排列RANK()为每行分配排名。
2. DENSE_RANK()
DENSE_RANK()函数用于计算排序后的排名相同值排名相同但排名之间没有间隔。
SELECT E.ENAME, E.JOB, E.SAL, E.DEPTNO,DENSE_RANK() OVER(PARTITION BY E.DEPTNO ORDER BY E.SAL DESC) AS RANK
FROM EMP E;
3. ROW_NUMBER()
ROW_NUMBER()函数为查询结果中的每一行分配一个唯一的序号。
SELECT E.ENAME, E.JOB, E.SAL, E.DEPTNO,ROW_NUMBER() OVER(PARTITION BY E.DEPTNO ORDER BY E.SAL DESC) AS ROW_NUM
FROM EMP E;
4. NTILE(n)
NTILE(n)函数将结果集分成指定数量的组并为每一行分配组编号。
SELECT E.ENAME, E.JOB, E.SAL, E.DEPTNO,NTILE(4) OVER(PARTITION BY E.DEPTNO ORDER BY E.SAL DESC) AS QUARTILE
FROM EMP E;
二、统计函数
OVER关键字可以与聚合函数如SUM()、AVG()、COUNT()等结合使用对分组数据进行统计。
SELECT E.DEPTNO, E.ENAME, E.SAL,SUM(E.SAL) OVER(PARTITION BY E.DEPTNO) AS SUM_SAL,AVG(E.SAL) OVER(PARTITION BY E.DEPTNO) AS AVG_SAL,COUNT(*) OVER(PARTITION BY E.DEPTNO) AS COUNT_EMP
FROM EMP E;
说明PARTITION BY E.DEPTNO将数据按部门分组然后在每个分组内分别计算工资总和、平均工资和员工数量。
三、取首尾记录
OVER关键字可以与FIRST_VALUE()和LAST_VALUE()函数结合使用获取分组中的首尾记录。
SELECT E.DEPTNO, E.ENAME, E.SAL,FIRST_VALUE(E.ENAME) OVER(PARTITION BY E.DEPTNO ORDER BY E.SAL DESC) AS MAX_SAL_EMP,LAST_VALUE(E.ENAME) OVER(PARTITION BY E.DEPTNO ORDER BY E.SAL DESC) AS MIN_SAL_EMP
FROM EMP E;
说明PARTITION BY E.DEPTNO将数据按部门分组ORDER BY E.SAL DESC在每个部门内按工资降序排列FIRST_VALUE()获取每个部门工资最高的员工姓名LAST_VALUE()获取工资最低的员工姓名。
四、取上下行记录
OVER关键字可以与LEAD()和LAG()函数结合使用获取当前行的上下行记录。
SELECT E.ENAME, E.SAL,LAG(E.SAL, 1, 0) OVER(ORDER BY E.SAL) AS PREV_SAL,LEAD(E.SAL, 1, 0) OVER(ORDER BY E.SAL) AS NEXT_SAL,E.SAL - LAG(E.SAL, 1, 0) OVER(ORDER BY E.SAL) AS DIFF_PREV,LEAD(E.SAL, 1, 0) OVER(ORDER BY E.SAL) - E.SAL AS DIFF_NEXT
FROM EMP E;
说明ORDER BY E.SAL按工资升序排列LAG(E.SAL, 1, 0)获取当前行的前一行工资LEAD(E.SAL, 1, 0)获取当前行的后一行工资然后计算当前行工资与前后行工资的差额。
五、滑动窗口
通过ROWS BETWEEN或RANGE BETWEEN子句OVER关键字可以指定窗口范围实现滑动窗口计算。
SELECT E.DEPTNO, E.ENAME, E.SAL,SUM(E.SAL) OVER(PARTITION BY E.DEPTNOORDER BY E.SALROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CUM_SUM_SAL
FROM EMP E;
说明PARTITION BY E.DEPTNO将数据按部门分组ORDER BY E.SAL在每个部门内按工资升序排列ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW定义窗口范围为从分组的第一行到当前行SUM(E.SAL)计算从分组的第一行到当前行的工资累计和。
