数据分析师（二）--分析函数_分析函数并列跳级怎么写-优快云博客

本文链接：https://blog.youkuaiyun.com/xu1979185008/article/details/126013552

本文深入探讨了SQL中的分析函数，包括MIN(), MAX(), SUM(), AVG(), COUNT(), ROW_NUMBER(), RANK(), DENSE_RANK(), LEAD(), LAG(), WM_CONCAT()和LISTAGG()等。分析函数不同于聚合函数，它能在聚合结果的同时提供组内详细信息。例如，使用ROW_NUMBER()可实现行编号，LEAD()和LAG()用于行间的偏移操作，而WM_CONCAT()和LISTAGG()则用于字符串的连接。文章通过示例展示了如何在不同场景下应用这些函数。" 123359566,7884375,Asaoka法在沉降预测中的应用与实现,"['算法', '数值计算', '数据拟合', 'C#编程', '土木工程']

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、分析函数（开窗函数）

分析函数和聚合函数的区别：聚合函数一给组只能看到一个聚合后的结果，分析函数既可以看到聚合后的结果，还能看到组内的详细信息。

语法：函数名([列])over([partition by 列])

1、MIN()

2、MAX()

3、SUM ()

4、AVG ()

5、COUNT （）

查询每个部门的最低工资：

--聚合函数
SELECT DEPTON,MIN(SAL) FROM EMP GROUP BY DEPTNO;
--分析函数
SELECT E.*,MIN(SAL) OVER(PARTITION BY DEPTNO) AAAA  FROM EMP E;

如果要求累计值，只要在over中加上order by即可

（1）、按值累计(有几个相同的值时是同时加的)

--求平均工资,工资综合
SELECT SAL,AVG(SAL)OVER(ORDER BY SAL) 平均工资.
       SUM(SAL)OVER(ORDER BY SAL)  工资和   FROM EMP;

（2）、按行累计，在以上的基础上加上

ROWS BETWEEN UNBOUNDED PRECEDING AND CRRENT ROW

SELECT SAL,AVG(SAL)OVER(ORDER BY SAL ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),
           SUM(SAL)OVER(ORDER BY SAL ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FORM EMP;

6、ROW_NUMBER () OVER ( [PARTITION BY] ORDER BY) 不重复，没有并列的情况：1、2、3、4、5。必须进行排序。

7、RANK () OVER ( [PARTITION BY] ORDER BY) 并列跳一级：1、2、2、4、5、5、7。必须进行排序。

8、DENSE_RANK () OVER ( [PARTITION BY] ORDER BY) 并列不跳级：1、2、2、3、4。必须进行排序。

--查询员工姓名  工资 工资的排名 
SELECT ENAME,SAL,ROW_NUMBER()OVER(ORDER BY SAL DESC) 排名A,
                 rank()over(order by sal desc) 排名B,
                 DENSE_RANK()OVER(ORDER BY SAL DESC) 排名C
FROM EMP;

9、LEAD (列[, 数[, 值]])OVER ( [PARTITION BY] ORDER BY) 向上偏移(把列中某一行下面几行往上偏移到本行处理)。必须排序

10、LAG (列[, 数[, 值]])OVER ( [PARTITION BY] ORDER BY) 向下偏移(把列中某一行上面几行往下偏移到本行处理)。必须排序

数：向上（下）偏移的行数，默认为1

值：当两个函数取上N/下N个值，当在表中从当前行位置向前数N行已经超出了表的范围时，lag（）函数将括号中的"值"这个参数值作为函数的返回值，若没有指定默认值，则返回NULL，那么在数学运算中，总要给一个默认值才不会出错。

--查询每个人的工资和下一个人的工资
SELECT ENAME,SAL,LEAD(SAL)OVER(ORDER BY SAL) 下个人的工资,
       LEAD(SAL,2,0)OVER(ORDER BY SAL)  下下个人工资
FROM EMP;

11、WM_CONCAT(列)[ OVER() ] 分组连接字符串

12、LISTAGG(列, '分隔符')[ WITHIN GROUP() ] 分组连接字符串

SELECT ENAME,DEPTNO,WM_CONCAT(ENAME)OVER(PARTITION BY DEPTNO)
FROM EMP;
SELECT ENAME,DEPTNO,LISTAGG(ENAME,'/')WITHIN GROUP(ORDER BY SAL)OVER(PARTITION BY DEPTNO)
FROM EMP;