SQL知识你知道吗：窗口函数

一、MySQL 中的窗口函数

MySQL窗口函数是一种特殊的函数，它能够在查询结果中进行计算和聚合操作，并且可以在查询结果中返回每个行的值。它们通常与OVER子句一起使用，该子句定义了如何对窗口函数进行分组和排序。

以下是一些常用的MySQL窗口函数：

1. ROW_NUMBER()：为每个行分配一个唯一的数字标识符。

2. RANK()：为每个行分配一个排名，如果有相同的值，则排名相同。

3. DENSE_RANK()：为每个行分配一个排名，如果有相同的值，则排名不同。

4. NTILE(n)：将结果集分成n个等份，并为每个行分配一个组号。

5. LAG(column, offset, default)：返回当前行之前的offset行的值，如果没有，则返回default。

6. LEAD(column, offset, default)：返回当前行之后的offset行的值，如果没有，则返回default。

7. FIRST_VALUE(column)：返回每个分组的第一个行的值。

8. LAST_VALUE(column)：返回每个分组的最后一个行的值。

使用窗口函数可以方便地进行各种计算和聚合操作，例如计算总和、平均值、最大值、最小值、中位数等。它们还可以帮助我们在结果集中查找特定的值、比较相邻的值等。

需要注意的是，MySQL窗口函数只能在SELECT语句中使用，而不能在WHERE或HAVING子句中使用。此外，使用窗口函数可能会导致查询性能下降，因此需要谨慎使用。

二、例子

假设我们有一个包含以下数据的表格：

id	name	department	salary
1	John	Marketing	5000
2	Jane	Sales	6000
3	Peter	Marketing	4500
4	Mary	Sales	5500
5	Jack	Marketing	4000
6	Jill	Sales	7000

1. ROW_NUMBER()

SELECT ROW_NUMBER() OVER() AS row_num, name, salary
FROM employees;

效果：在employees表中为每一行添加一个序号，序号从1开始递增。

输出结果如下：

row_num	name	salary
1	John	5000
2	Jane	6000
3	Peter	4500
4	Mary	5500
5	Jack	4000
6	Jill	7000

2. RANK()

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

效果：在employees表中根据员工的薪资进行排名，并为每个员工分配一个排名。

输出结果如下：

name	salary	salary_rank
Jill	7000	1
Jane	6000	2
Mary	5500	3
John	5000	4
Peter	4500	5
Jack	4000	6

3. DENSE_RANK()

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

效果：在employees表中根据员工的薪资进行排名，并为每个员工分配一个排名，相同薪资的员工排名相同。

输出结果如下：

name	salary	salary_rank
Jill	7000	1
Jane	6000	2
Mary	5500	3
John	5000	4
Peter	4500	5
Jack	4000	6

4. NTILE()

SELECT name, salary, NTILE(3) OVER(ORDER BY salary DESC) AS salary_group
FROM employees;

效果：在employees表中根据员工的薪资将员工分为3组，并为每个员工分配一个组号。

输出结果如下：

name	salary	salary_group
Jill	7000	1
Jane	6000	1
Mary	5500	2
John	5000	2
Peter	4500	3
Jack	4000	3

5. LAG()

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

效果：在employees表中根据员工的薪资，为每个员工添加一个前一位员工的薪资。

输出结果如下：

name	salary	prev_salary
Jill	7000	0
Jane	6000	7000
Mary	5500	6000
John	5000	5500
Peter	4500	5000
Jack	4000	4500

6. LEAD()

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

效果：在employees表中根据员工的薪资，为每个员工添加一个后一位员工的薪资。

输出结果如下：

name	salary	next_salary
Jill	7000	6000
Jane	6000	5500
Mary	5500	5000
John	5000	4500
Peter	4500	4000
Jack	4000	0

7. FIRST_VALUE()

SELECT department, salary, FIRST_VALUE(name) OVER(PARTITION BY department ORDER BY salary DESC) AS top_performer
FROM employees;

效果：在employees表中根据每个员工的部门和薪资，为每个部门添加一个最高薪资的员工姓名。

输出结果如下：

department	salary	top_performer
Marketing	5000	John
Marketing	4500	John
Marketing	4000	John
Sales	7000	Jill
Sales	6000	Jill
Sales	5500	Jill

8. LAST_VALUE()

SELECT department, salary, LAST_VALUE(name) OVER(PARTITION BY department ORDER BY salary DESC) AS last_employee
FROM employees;

效果：在employees表中根据每个员工的部门和薪资，为每个部门添加一个最低薪资的员工姓名。

输出结果如下：

department	salary	last_employee
Marketing	5000	Jack
Marketing	4500	Jack
Marketing	4000	Jack
Sales	7000	Jill
Sales	6000	Jill
Sales	5500	Jill

三、数据库版本

以下是一些支持窗口函数的数据库及其支持版本：

1. Oracle Database：支持窗口函数，从Oracle 8i版本开始支持。

2. Microsoft SQL Server：支持窗口函数，从SQL Server 2005版本开始支持。

3. PostgreSQL：支持窗口函数，从PostgreSQL 8.4版本开始支持。

4. MySQL：支持窗口函数，从MySQL 8.0版本开始支持。

5. SQLite：支持窗口函数，从SQLite 3.25.0版本开始支持。

6. IBM Db2：支持窗口函数，从Db2 9.5版本开始支持。

7. SAP HANA：支持窗口函数，从SAP HANA SPS05版本开始支持。

8. Amazon Redshift：支持窗口函数，从Redshift 1.5版本开始支持。

需要注意的是，不同版本的数据库可能支持的窗口函数有所不同，具体支持情况可以查阅相应数据库的官方文档。