为了帮助你更详细、清晰地理解 SQL 窗口函数，本文从零开始详细解释，并通过逐步示例来展示如何使用这些函数。窗口函数可以在处理大量数据时大大提高查询效率，帮助你从不同角度理解数据。

---

什么是窗口函数？

窗口函数是 SQL 中的一类特殊函数，它可以基于查询结果集的某些部分（窗口）来计算某些聚合值或执行其他操作。不同于普通的聚合函数（如 SUM()、AVG()、COUNT() 等），窗口函数不会对数据进行“汇总”，而是保留每一行数据，并在该行周围的“窗口”中执行计算。

---

1. 基本语法

窗口函数的基本语法如下：

<窗口函数> OVER (PARTITION BY <分组字段> ORDER BY <排序字段>)

• 窗口函数：这是你想执行的具体操作，例如：ROW_NUMBER(), RANK(), SUM(), AVG() 等。
• OVER()：表示窗口函数将在此语句中被应用。
• PARTITION BY：指定如何对数据进行分组。类似于 GROUP BY，但窗口函数会保持每一行数据，只是在分组内计算。
• ORDER BY：指定如何对每个分组内的数据进行排序。排序影响排名和其他计算结果。

---

2. 常见的窗口函数及示例

ROW_NUMBER() – 给每一行数据排序并返回唯一的行号

ROW_NUMBER() 是一个最常见的窗口函数，它给每一行数据分配一个唯一的编号。常用于对数据按某个字段排序并给每行数据添加排名。

SELECT 
    id, 
    name, 
    salary, 
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num
FROM employees;

解释：

• ROW_NUMBER() 将会为每一行员工数据按薪资（salary DESC）排序，并给每一行添加一个唯一的行号。
• 排名从 1 开始，如果有多个员工的薪资相同，他们的行号也会根据排序规则分配不同的编号。

结果示例：

id	name	salary	row_num
1	张三	9000	1
2	李四	8500	2
3	王五	8500	3
4	赵六	7000	4

RANK() – 给数据排名，排名相同的行跳过

RANK() 函数和 ROW_NUMBER() 相似，但如果有相同值的数据，它们会得到相同的排名，并跳过下一个排名。

SELECT 
    id, 
    name, 
    salary, 
    RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;

解释：

• RANK() 会根据薪资为员工排名，但当多个员工的薪资相同，他们的排名会相同（如 1 和 2），而下一个排名会跳过一个数字。

结果示例：

id	name	salary	rank
1	张三	9000	1
2	李四	8500	2
3	王五	8500	2
4	赵六	7000	4

DENSE_RANK() – 给数据排名，排名相同的行不跳过

DENSE_RANK() 也是为数据排名，但它不会跳过排名。如果有两个相同的薪资，他们会得到相同的排名，下一个排名不会跳过。

SELECT 
    id, 
    name, 
    salary, 
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

解释：

• DENSE_RANK() 对于同样的值会给出相同的排名，但是不会跳过下一个排名。

结果示例：

id	name	salary	dense_rank
1	张三	9000	1
2	李四	8500	2
3	王五	8500	2
4	赵六	7000	3

NTILE() – 将数据分成 N 个组

NTILE() 用来将数据分成若干个组（例如四分位），并返回每行所属的组号。

SELECT 
    id, 
    name, 
    salary, 
    NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees;

解释：

• NTILE(4) 将员工数据根据薪资降序排序，并分成 4 个组，返回每个员工所在的组号。

结果示例：

id	name	salary	quartile
1	张三	9000	1
2	李四	8500	1
3	王五	8500	2
4	赵六	7000	3

LEAD() 和 LAG() – 获取当前行的前后行数据

• LEAD() 获取当前行之后的一行数据。
• LAG() 获取当前行之前的一行数据。

SELECT 
    id, 
    name, 
    salary, 
    LAG(salary, 1) OVER (ORDER BY salary DESC) AS prev_salary, 
    LEAD(salary, 1) OVER (ORDER BY salary DESC) AS next_salary
FROM employees;

解释：

• LAG(salary, 1) 获取当前员工薪资的前一个员工薪资（如果有的话）。
• LEAD(salary, 1) 获取当前员工薪资的下一个员工薪资（如果有的话）。

结果示例：

id	name	salary	prev_salary	next_salary
1	张三	9000	NULL	8500
2	李四	8500	9000	8500
3	王五	8500	8500	7000
4	赵六	7000	8500	NULL

SUM() 和 AVG() – 按组计算总和或平均值

这些常用的聚合函数可以配合 PARTITION BY 用于每个分组内的计算。

SELECT 
    id, 
    name, 
    salary, 
    SUM(salary) OVER (PARTITION BY department) AS dept_salary_sum,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary
FROM employees;

解释：

• SUM(salary) OVER (PARTITION BY department) 会按部门（department）计算每个部门的总薪资。
• AVG(salary) OVER (PARTITION BY department) 会按部门计算每个部门的平均薪资。

结果示例：

id	name	department	salary	dept_salary_sum	dept_avg_salary
1	张三	IT	9000	17000	8500
2	李四	IT	8000	17000	8500
3	王五	HR	7000	7000	7000
4	赵六	HR	7000	7000	7000

---

3. 总结和应用场景

1. 排名计算：ROW_NUMBER(), RANK(), DENSE_RANK() 常用于计算排名，处理例如销售、竞赛等数据。
2. 前后行数据比较：LAG() 和 LEAD() 用于比较当前行和相邻行的值，适用于计算趋势、差异等。
3. 分组汇总：SUM(), AVG(), MIN(), MAX() 可以按分组计算总和、平均值、最小值、最大值等，适用于财务统计、业绩分析等。

通过这些窗口函数，你能够在 SQL 查询中不丢失行数据的前提下进行强大的计算分析。