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

女爷i 2024-03-16 20:47 41阅读 0赞

## 一、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子句中使用。此外，使用窗口函数可能会导致查询性能下降，因此需要谨慎使用。

## 二、例子 ##

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

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>name</th> 
   <th>department</th> 
   <th>salary</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>John</td> 
   <td>Marketing</td> 
   <td>5000</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>Jane</td> 
   <td>Sales</td> 
   <td>6000</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>Peter</td> 
   <td>Marketing</td> 
   <td>4500</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>Mary</td> 
   <td>Sales</td> 
   <td>5500</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>Jack</td> 
   <td>Marketing</td> 
   <td>4000</td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td>Jill</td> 
   <td>Sales</td> 
   <td>7000</td> 
  </tr> 
 </tbody> 
</table>

1.  ROW\_NUMBER()

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

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

输出结果如下：

<table> 
 <thead> 
  <tr> 
   <th>row_num</th> 
   <th>name</th> 
   <th>salary</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>John</td> 
   <td>5000</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>Jane</td> 
   <td>6000</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>Peter</td> 
   <td>4500</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>Mary</td> 
   <td>5500</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>Jack</td> 
   <td>4000</td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td>Jill</td> 
   <td>7000</td> 
  </tr> 
 </tbody> 
</table>

1.  RANK()

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

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

输出结果如下：

<table> 
 <thead> 
  <tr> 
   <th>name</th> 
   <th>salary</th> 
   <th>salary_rank</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Jill</td> 
   <td>7000</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>Jane</td> 
   <td>6000</td> 
   <td>2</td> 
  </tr> 
  <tr> 
   <td>Mary</td> 
   <td>5500</td> 
   <td>3</td> 
  </tr> 
  <tr> 
   <td>John</td> 
   <td>5000</td> 
   <td>4</td> 
  </tr> 
  <tr> 
   <td>Peter</td> 
   <td>4500</td> 
   <td>5</td> 
  </tr> 
  <tr> 
   <td>Jack</td> 
   <td>4000</td> 
   <td>6</td> 
  </tr> 
 </tbody> 
</table>

1.  DENSE\_RANK()

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

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

输出结果如下：

1.  NTILE()

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

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

输出结果如下：

<table> 
 <thead> 
  <tr> 
   <th>name</th> 
   <th>salary</th> 
   <th>salary_group</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Jill</td> 
   <td>7000</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>Jane</td> 
   <td>6000</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>Mary</td> 
   <td>5500</td> 
   <td>2</td> 
  </tr> 
  <tr> 
   <td>John</td> 
   <td>5000</td> 
   <td>2</td> 
  </tr> 
  <tr> 
   <td>Peter</td> 
   <td>4500</td> 
   <td>3</td> 
  </tr> 
  <tr> 
   <td>Jack</td> 
   <td>4000</td> 
   <td>3</td> 
  </tr> 
 </tbody> 
</table>

1.  LAG()

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

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

输出结果如下：

<table> 
 <thead> 
  <tr> 
   <th>name</th> 
   <th>salary</th> 
   <th>prev_salary</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Jill</td> 
   <td>7000</td> 
   <td>0</td> 
  </tr> 
  <tr> 
   <td>Jane</td> 
   <td>6000</td> 
   <td>7000</td> 
  </tr> 
  <tr> 
   <td>Mary</td> 
   <td>5500</td> 
   <td>6000</td> 
  </tr> 
  <tr> 
   <td>John</td> 
   <td>5000</td> 
   <td>5500</td> 
  </tr> 
  <tr> 
   <td>Peter</td> 
   <td>4500</td> 
   <td>5000</td> 
  </tr> 
  <tr> 
   <td>Jack</td> 
   <td>4000</td> 
   <td>4500</td> 
  </tr> 
 </tbody> 
</table>

1.  LEAD()

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

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

输出结果如下：

<table> 
 <thead> 
  <tr> 
   <th>name</th> 
   <th>salary</th> 
   <th>next_salary</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Jill</td> 
   <td>7000</td> 
   <td>6000</td> 
  </tr> 
  <tr> 
   <td>Jane</td> 
   <td>6000</td> 
   <td>5500</td> 
  </tr> 
  <tr> 
   <td>Mary</td> 
   <td>5500</td> 
   <td>5000</td> 
  </tr> 
  <tr> 
   <td>John</td> 
   <td>5000</td> 
   <td>4500</td> 
  </tr> 
  <tr> 
   <td>Peter</td> 
   <td>4500</td> 
   <td>4000</td> 
  </tr> 
  <tr> 
   <td>Jack</td> 
   <td>4000</td> 
   <td>0</td> 
  </tr> 
 </tbody> 
</table>

1.  FIRST\_VALUE()

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

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

输出结果如下：

<table> 
 <thead> 
  <tr> 
   <th>department</th> 
   <th>salary</th> 
   <th>top_performer</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Marketing</td> 
   <td>5000</td> 
   <td>John</td> 
  </tr> 
  <tr> 
   <td>Marketing</td> 
   <td>4500</td> 
   <td>John</td> 
  </tr> 
  <tr> 
   <td>Marketing</td> 
   <td>4000</td> 
   <td>John</td> 
  </tr> 
  <tr> 
   <td>Sales</td> 
   <td>7000</td> 
   <td>Jill</td> 
  </tr> 
  <tr> 
   <td>Sales</td> 
   <td>6000</td> 
   <td>Jill</td> 
  </tr> 
  <tr> 
   <td>Sales</td> 
   <td>5500</td> 
   <td>Jill</td> 
  </tr> 
 </tbody> 
</table>

1.  LAST\_VALUE()

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

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

输出结果如下：

<table> 
 <thead> 
  <tr> 
   <th>department</th> 
   <th>salary</th> 
   <th>last_employee</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Marketing</td> 
   <td>5000</td> 
   <td>Jack</td> 
  </tr> 
  <tr> 
   <td>Marketing</td> 
   <td>4500</td> 
   <td>Jack</td> 
  </tr> 
  <tr> 
   <td>Marketing</td> 
   <td>4000</td> 
   <td>Jack</td> 
  </tr> 
  <tr> 
   <td>Sales</td> 
   <td>7000</td> 
   <td>Jill</td> 
  </tr> 
  <tr> 
   <td>Sales</td> 
   <td>6000</td> 
   <td>Jill</td> 
  </tr> 
  <tr> 
   <td>Sales</td> 
   <td>5500</td> 
   <td>Jill</td> 
  </tr> 
 </tbody> 
</table>

## 三、数据库版本 ##

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

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版本开始支持。

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