前言百度文库也记载了 Oracle 中 OVER() 分析函数的用法。在泡坛子的时候无意中发现了这个函数才知道 Oracle 分析函数是如此的强大其中 OVER() 函数的用法又尤为的特别所以将自己的研究结果记录一下。一、OVER() 函数理解个人理解OVER() 函数是对分析函数的一种条件解释直接点就是给分析函数加条件。在网上看见比较常用的就是与 SUM()、RANK() 函数使用。接下来就用分析下两种函数结合 OVER 的用法。以下测试使用的 Oracle 默认的 scott 用户下的 emp 表数据。二、SUM() 结合 OVER()1. 基础用法按部门分区SQL 代码select a.empno as 员工编号 ,a.ename as 员工姓名 ,a.deptno as 部门编号 ,a.sal as 薪酬 ,sum(sal) over (partition by deptno) 按部门求薪酬总和 from scott.emp a;此段 SQL 执行的结果为员工编码员工姓名部门编号薪酬按部门求薪酬总和7934MILLER10130087507782CLARK10245087507839KING10500087507369SMITH20800108757876ADAMS201100108757566JONES202975108757788SCOTT203000108757902FORD203000108757900JAMES3095094007654MARTIN30125094007521WARD30125094007844TURNER30150094007499ALLEN30160094007698BLAKE3028509400可以从结果上看到 SUM() 函数对部门区分进行了求和统计。其中 partition by 官方点的说法叫做分区其实就是统计的范围条件。2. 添加 ORDER BY 子句累计求和下面再把上面的 SQL 语句改造下给 OVER() 函数加上 order by sal 会看到一个更过瘾的效果select a.empno as 员工编号 ,a.ename as 员工姓名 ,a.deptno as 部门编号 ,a.sal as 薪酬 ,sum(sal) over (partition by deptno) 按部门求薪酬总和 ,sum(sal) over (partition by deptno order by sal) 按部门累计薪酬 from scott.emp a;结果为员工编码员工姓名部门编号薪酬按部门求薪酬总和按部门累计薪酬7934MILLER101300875013007782CLARK102450875037507839KING105000875087507369SMITH20800108758007876ADAMS2011001087519007566JONES2029751087548757788SCOTT20300010875108757902FORD20300010875108757900JAMES3095094009507654MARTIN301250940034507521WARD301250940034507844TURNER301500940049507499ALLEN301600940065507698BLAKE30285094009400从结果中可以看的加了 order by 后对统计进行一个累加这里个人理解为对统计范围规定了个统计顺序一步一步的统计。注此 SQL 语句结尾处不要加 order by因为使用的分析函数的 (partition by deptno order by sal) 里已经有排序的语句了如果再在句尾添加排序子句一致倒罢了不一致结果就令人费解了。三、RANK() 结合 OVER()RANK 函数是分级函数这个函数必须与 OVER 函数使用否则会报一个缺少窗口函数的错。我测试 SQL 如下select a.empno as 员工编号, a.sal as 薪资, a.job as 岗位, rank() OVER(partition by a.job ORDER BY a.sal desc) as 岗位薪资等级 from scott.emp a;查询结果为员工编号薪资岗位等级79023000ANALYST177883000ANALYST179341300CLERK178761100CLERK27900950CLERK37369800CLERK475662975MANAGER176982850MANAGER277822450MANAGER378395000PRESIDENT174991600SALESMAN178441500SALESMAN276541250SALESMAN375211250SALESMAN3四、综合实战应用ROW_NUMBER() 与 LAG() 结合 OVER()在实际业务中我们经常需要更复杂的分析比如计算部门内员工的薪酬排名或者计算与上一名员工的薪酬差额。下面通过两个实战示例来展示 OVER() 函数的强大功能。1. 使用 ROW_NUMBER() 计算部门内薪酬排名ROW_NUMBER() 函数可以为每一行分配一个唯一的序号结合 OVER() 的 PARTITION BY 和 ORDER BY 子句可以轻松实现部门内的薪酬排名SELECT empno AS 员工编号, ename AS 员工姓名, deptno AS 部门编号, sal AS 薪酬, ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC) AS 部门内薪酬排名 FROM scott.emp ORDER BY deptno, 部门内薪酬排名;执行结果解释PARTITION BY deptno按部门分区每个部门独立计算排名ORDER BY sal DESC按薪酬降序排列薪酬最高的排第1名ROW_NUMBER()为每个分区内的行分配连续的唯一序号这样就能得到每个部门内员工的薪酬排名从高到低依次为1、2、3...即使薪酬相同也会分配不同的序号。2. 使用 LAG() 计算与上一名员工的薪酬差额LAG() 函数可以访问当前行之前指定偏移量的行的值非常适合计算与前一名员工的差额SELECT empno AS 员工编号, ename AS 员工姓名, deptno AS 部门编号, sal AS 当前薪酬, LAG(sal) OVER (PARTITION BY deptno ORDER BY sal DESC) AS 上一名员工薪酬, sal - LAG(sal) OVER (PARTITION BY deptno ORDER BY sal DESC) AS 薪酬差额 FROM scott.emp ORDER BY deptno, sal DESC;执行结果解释LAG(sal)获取当前行之前一行的 sal 值按部门分区并按薪酬降序排列sal - LAG(sal)计算当前员工薪酬与上一名员工薪酬的差额对于每个部门的第1名员工LAG(sal) 返回 NULL因此薪酬差额也为 NULL3. 综合示例同时计算排名和差额将上述两个功能结合起来可以一次性获得部门内排名和与上一名的薪酬差额SELECT empno AS 员工编号, ename AS 员工姓名, deptno AS 部门编号, sal AS 薪酬, ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC) AS 部门内排名, LAG(sal) OVER (PARTITION BY deptno ORDER BY sal DESC) AS 上一名薪酬, sal - LAG(sal) OVER (PARTITION BY deptno ORDER BY sal DESC) AS 与上一名差额 FROM scott.emp ORDER BY deptno, 部门内排名;这个查询会返回类似以下结构的结果员工编号员工姓名部门编号薪酬部门内排名上一名薪酬与上一名差额7839KING1050001NULLNULL7782CLARK10245025000-25507934MILLER10130032450-1150... 其他部门数据类似应用场景分析薪酬分析HR部门可以快速了解各部门内员工的薪酬分布情况绩效评估管理者可以看到员工与上一名同事的薪酬差距预算规划财务部门可以根据排名和差额制定调薪策略数据监控实时监控薪酬结构变化发现异常波动注意事项ROW_NUMBER() 总是生成连续的唯一序号即使值相同也会分配不同序号如果需要处理并列情况可以使用 RANK() 或 DENSE_RANK() 函数LAG() 函数的第二个参数可以指定偏移量如 LAG(sal, 2) 获取前两行的值LAG() 的第三个参数可以指定默认值如 LAG(sal, 1, 0) 当没有前一行时返回0通过这两个示例我们可以看到 OVER() 函数结合其他分析函数能够解决复杂的业务分析需求大大提高了 SQL 的数据分析能力。五、常见面试题与解答在 Oracle 数据库相关的面试中OVER() 分析函数是经常被问到的知识点。以下是几个典型的面试题及其详细解答帮助读者深入理解 OVER() 函数的应用场景。1. 面试题解释 OVER() 函数中 PARTITION BY 和 ORDER BY 的区别问题描述请解释 OVER() 函数中 PARTITION BY 子句和 ORDER BY 子句的作用和区别并举例说明。解答PARTITION BY 和 ORDER BY 是 OVER() 函数的两个核心子句它们的作用完全不同PARTITION BY用于将数据分成不同的分区组在每个分区内独立进行计算。如果不指定 PARTITION BY则整个结果集作为一个分区。ORDER BY用于指定分区内的排序顺序对于某些分析函数如 SUM() 配合 ORDER BY会产生累计效果。示例对比-- 示例1仅使用 PARTITION BY SELECT empno, ename, deptno, sal, SUM(sal) OVER (PARTITION BY deptno) AS 部门薪酬总和 FROM scott.emp; -- 示例2同时使用 PARTITION BY 和 ORDER BY SELECT empno, ename, deptno, sal, SUM(sal) OVER (PARTITION BY deptno ORDER BY sal) AS 部门累计薪酬 FROM scott.emp;结果差异示例1中每个部门的所有员工都会显示该部门的总薪酬相同值示例2中每个部门的员工会按薪酬升序排列并显示到当前行为止的累计薪酬递增值2. 面试题ROW_NUMBER()、RANK() 和 DENSE_RANK() 的区别问题描述ROW_NUMBER()、RANK() 和 DENSE_RANK() 都是排名函数请说明它们之间的区别并给出具体的 SQL 示例。解答这三个函数都用于生成排名但在处理相同值并列时有不同的行为函数特点相同值处理排名序列ROW_NUMBER()为每一行分配唯一的连续序号相同值也会分配不同序号1, 2, 3, 4, 5...RANK()为相同值分配相同排名但会跳过后续排名相同值排名相同下一个不同值排名会跳过1, 1, 3, 4, 4, 6...DENSE_RANK()为相同值分配相同排名但不会跳过后续排名相同值排名相同下一个不同值排名连续1, 1, 2, 3, 3, 4...SQL 示例SELECT empno, ename, sal, ROW_NUMBER() OVER (ORDER BY sal DESC) AS row_num, RANK() OVER (ORDER BY sal DESC) AS rank_num, DENSE_RANK() OVER (ORDER BY sal DESC) AS dense_rank_num FROM scott.emp WHERE deptno 20;结果分析假设部门20的薪酬数据为3000, 3000, 2975, 1100, 800ROW_NUMBER() 会生成1, 2, 3, 4, 5RANK() 会生成1, 1, 3, 4, 5两个3000并列第12975排第3DENSE_RANK() 会生成1, 1, 2, 3, 4两个3000并列第12975排第23. 面试题使用 LAG() 和 LEAD() 函数解决实际问题问题描述有一个销售记录表 sales包含销售日期和销售额。请使用 LAG() 和 LEAD() 函数计算每日销售额与前一日、后一日的对比情况。解答LAG() 和 LEAD() 是窗口函数中常用的偏移函数LAG(column, n)获取当前行之前第n行的值LEAD(column, n)获取当前行之后第n行的值SQL 代码-- 假设 sales 表结构sale_date DATE, amount NUMBER SELECT sale_date, amount AS 当日销售额, LAG(amount, 1) OVER (ORDER BY sale_date) AS 前一日销售额, LEAD(amount, 1) OVER (ORDER BY sale_date) AS 后一日销售额, amount - LAG(amount, 1) OVER (ORDER BY sale_date) AS 较前日变化, LEAD(amount, 1) OVER (ORDER BY sale_date) - amount AS 较后日变化 FROM sales ORDER BY sale_date;结果解释销售日期当日销售额前一日销售额后一日销售额较前日变化较后日变化2023-01-011000NULL1200NULL2002023-01-0212001000900200-3002023-01-0390012001500-300600应用场景销售趋势分析观察销售额的日环比变化异常检测识别销售额突增或突降的日子预测分析基于前后日数据建立简单的预测模型面试技巧理解 OVER() 函数的基本语法和各个子句的作用掌握常见分析函数SUM、AVG、COUNT、ROW_NUMBER、RANK、LAG等的用法能够结合实际业务场景设计窗口函数查询注意 NULL 值的处理特别是 LAG() 和 LEAD() 函数了解性能优化合理使用 PARTITION BY 和 ORDER BY避免全表扫描