（课堂笔记）Oracle 窗口函数/开窗函数/分析函数

窗口函数（OVER）可在SELECT中实现分组计算而不改变行数，核心包括：

1)排名函数(ROW_NUMBER/RANK/DENSE_RANK)用于TOPN查询；

2)LAG/LEAD获取相邻行数据；

3)FIRST_VALUE取组内首条记录；

4)NTILE数据分桶；

5)聚合开窗(SUM/AVG)实现累计计算。

---

与聚合函数不同，窗口函数保留原行数，通过PARTITION BY分组、ORDER BY排序。

---

典型应用包括部门薪资排名、同环比分析、累计求和等，需注意结果需子查询过滤，且必须明确排序规则。

（课堂笔记）窗口函数/开窗函数/分析函数

以下是根据你提供的课堂内容，整理出的《窗口函数（开窗函数/分析函数）完整复习笔记》，结构清晰、便于学生复习和记忆：

📌 一、窗口函数概述

基本语法：

sql

函数() OVER (PARTITION BY 分组字段 ORDER BY 排序字段)

核心特点：

PARTITION 可以省略

---

PARTITION by 1 order by 1 等于默认值

📌窗口函数 vs 聚合函数：

• 聚合函数：多行 → 1 行

• 窗口函数：多行 → 多行（每行带聚合结果）

📌 二、排名函数（取 TOP N）

1. 三种排名函数对比

RANK 排名 DENSE 稠密，密集

---

RANK() 并列跳号 1,2,2,4

DENSE_RANK() 并列不跳 1,2,2,3

2. 基础语法

sql

ROW_NUMBER() OVER (PARTITION BY 分组字段 ORDER BY 排序字段)

3. 实战示例

✅ 每个部门按薪资排名

sql

SELECT ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) AS RN, E.* FROM EMP E;

✅ 每个部门薪资前三名

sql

SELECT * FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) AS RN, E.* FROM EMP E ) WHERE RN <= 3;

✅ 公司薪资第 6 名

sql

SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY SAL DESC) AS RN, E.* FROM EMP E ) WHERE RN = 6;

✅ 每年入职员工月薪前三名

sql

SELECT * FROM ( SELECT ROW_NUMBER() OVER ( PARTITION BY TO_CHAR(HIREDATE,'YYYY') ORDER BY SAL + NVL(COMM,0) DESC ) AS RN, E.* FROM EMP E ) WHERE RN <= 3;

📌 三、LAG / LEAD（取上下行，计算同环比）

1. 语法

sql

LAG(字段, 偏移量, 缺省值) OVER (PARTITION BY 分组 ORDER BY 排序) LEAD(字段, 偏移量, 缺省值) OVER (PARTITION BY 分组 ORDER BY 排序)

✅ 偏移量默认 = 1，缺省值默认 = NULL

---

空值不会触发缺省值，会保持为空。

函数	单词	中文含义	词性
LAG	lag	滞后、延迟、落后	动词/名词
LEAD	lead	领先、超前、引导	动词/名词

2. 实战示例

✅ 与上一个入职员工薪资差

sql

SELECT E.*, ABS(SAL - LAG(SAL, 1, 8888) OVER (ORDER BY HIREDATE)) AS 薪资差 FROM EMP E;

✅ 每个部门中，与下一个入职员工薪资差

sql

SELECT E.*, LEAD(SAL) OVER (PARTITION BY DEPTNO ORDER BY HIREDATE) AS NEXT_SAL FROM EMP E;

📌 四、FIRST_VALUE（取每组第一个值）

1. 语法

sql

FIRST_VALUE(目标字段) OVER (PARTITION BY 分组 ORDER BY 排序)

✅ 取每组排序后的第一条数据的指定字段值

2. 实战示例

✅ 每个部门薪资最高的员工姓名

sql

SELECT DISTINCT FIRST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) AS 最高薪资员工, DEPTNO FROM EMP;

💡 配合ROW_NUMBER方式（更灵活）：

sql

SELECT DEPTNO, ENAME FROM ( SELECT DEPTNO, ENAME, ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) AS RN FROM EMP ) WHERE RN = 1;

📌 五、NTILE（数据切片 / 分桶）

1. 语法

sql

NTILE(桶数) OVER (PARTITION BY 分组 ORDER BY 排序)

✅ 将数据分成 N 份，每行标记所属桶号（1 ~ N）

---

NTILE 整体读作：/ˈen.taɪl/（恩-太欧）
（重音在第一个音节 “en” 上）

---

在专业交流中（如讨论数据分桶），通常直接读 “N-tile”（强调 N 的数字，如 “4-tile” 表示四分位）即可，对方更容易理解。

2. 实战示例

✅ 每个部门薪资分成 3 档（高/中/低），输出高薪员工

sql

SELECT * FROM ( SELECT NTILE(3) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) AS NT, E.* FROM EMP E ) WHERE NT = 1;

📌 六、聚合开窗函数（累计 / 占比）

1. 语法

sql

SUM() OVER (PARTITION BY 分组 ORDER BY 排序) AVG() OVER (PARTITION BY 分组 ORDER BY 排序)

✅ 带ORDER BY：累计值（逐行累加）
✅ 不带ORDER BY：全组聚合值（每行相同）

---

关联阅读推荐

Oracle：为什么 ORDER BY 能让 SUM() 变成累计？

2. 实战示例

✅ 公司累计人力成本（随时间推移）

sql

SELECT E.*, SUM(SAL) OVER (ORDER BY HIREDATE) AS 累计薪资 FROM EMP E;

✅ 每个部门累计人力成本

sql

SELECT E.*, SUM(SAL) OVER (PARTITION BY DEPTNO ORDER BY HIREDATE) AS 部门累计薪资 FROM EMP E;

✅ 员工薪资占部门总薪资比例

sql

SELECT E.*, SAL / SUM(SAL) OVER (PARTITION BY DEPTNO) AS 部门薪资占比 FROM EMP E;

✅ 员工薪资占公司总成本比例

sql

SELECT E.*, SAL / SUM(SAL) OVER () AS 公司薪资占比 FROM EMP E;

✅ 每个部门累计平均薪资

sql

SELECT E.*, AVG(SAL) OVER (PARTITION BY DEPTNO ORDER BY HIREDATE) AS 累计平均薪资 FROM EMP E;

📌 七、窗口范围控制（了解）

sql

SUM(SAL) OVER ( PARTITION BY DEPTNO ORDER BY HIREDATE ROWS BETWEEN 2 PRECEDING AND CURRENT ROW )

💡 一般笔试/面试较少深入，了解即可。

📌 八、常见面试题

Q1：用过哪些窗口函数？

Q2：窗口函数和聚合函数的区别？

Oracle 聚合函数 vs 窗口函数 对比总结（书写顺序与执行顺序示例）

📌 九、课堂练习（自测用）

练习1：每个办公地点薪资前三名

sql

SELECT LOC, ENAME, SAL FROM ( SELECT LOC, ENAME, SAL, ROW_NUMBER() OVER (PARTITION BY LOC ORDER BY SAL DESC) AS RN FROM EMP E JOIN DEPT D ON E.DEPTNO = D.DEPTNO ) WHERE RN <= 3;

练习2：每个岗位上一个入职员工的薪资

sql

SELECT JOB, HIREDATE, SAL, LAG(SAL) OVER (PARTITION BY JOB ORDER BY HIREDATE) AS 上一个薪资 FROM EMP;

练习3：每年薪资最高的员工姓名

sql

SELECT DISTINCT TO_CHAR(HIREDATE,'YYYY') AS YEAR, FIRST_VALUE(ENAME) OVER ( PARTITION BY TO_CHAR(HIREDATE,'YYYY') ORDER BY SAL DESC ) AS ENAME FROM EMP;

📌 十、易错提醒

📊 SQL 窗口函数速查表（一页全）

🔧 基础语法

sql

函数() OVER (PARTITION BY 分组字段 ORDER BY 排序字段)

📌 排名函数（TOP N）

sql

-- 每个部门薪资前三名 SELECT * FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) AS RN, E.* FROM EMP E ) WHERE RN <= 3;

📌 LAG / LEAD（上下行）

sql

-- 与上一个入职员工薪资差 SELECT SAL - LAG(SAL) OVER (ORDER BY HIREDATE) FROM EMP;

📌FIRST_VALUE（每组首个）

sql

-- 每个部门薪资最高的员工 SELECT DISTINCT FIRST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) FROM EMP;

📌 NTILE（数据切片）

sql

-- 每个部门薪资分3档，取高薪档 SELECT * FROM ( SELECT NTILE(3) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) AS NT, E.* FROM EMP ) WHERE NT = 1;

📌 聚合开窗（累计/占比）

sql

-- 部门薪资占比 SELECT SAL / SUM(SAL) OVER (PARTITION BY DEPTNO) FROM EMP; -- 累计人力成本 SELECT SUM(SAL) OVER (ORDER BY HIREDATE) FROM EMP;

⚡ 执行顺序

text

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY ↑ 窗口函数在这里计算

⚠️ 核心要点

🎯 场景速查

💡记忆口诀：排名用ROW，上下用LAG，切片NTILE，累计SUM OVER