mysql 窗口函数面试题_MySQL 8 中窗口函数（Window Function）的理解与使用

通过使用窗口函数(Window Function)，对于查询中的所有行数据都可通过计算与该行相关的一些行来得到一些值，这可以通过OVER和WINDOW子句实现。什么意思呢？

既然是有计算，那就有一些计算函数，这里先利用一两个说明一下它是如何使用的。

先通过一个例子来说明一下：

RANK()函数作用是在分区数据中获得当前行的排名，SQL如下：mysql> SELECT hire_date_year, salary, RANK() OVER w

AS 'Rank' FROM employees join salaries ON

salaries.emp_no=employees.emp_no WINDOW w AS

(PARTITION BY hire_date_year ORDER BY salary DESC)

ORDER BY salary DESC LIMIT 10;

+----------------+--------+------+

| hire_date_year | salary | Rank |

+----------------+--------+------+

| 1985 | 158220 | 1 |

| 1985 | 157821 | 2 |

| 1986 | 156286 | 1 |

| 1985 | 155709 | 3 |

| 1987 | 155513 | 1 |

| 1985 | 155377 | 4 |

| 1985 | 155190 | 5 |

| 1985 | 154888 | 6 |

| 1985 | 154885 | 7 |

| 1985 | 154459 | 8 |

+----------------+--------+------+

10 rows in set (8.52 sec)

注意到上面的SQL中有一个查询列Rank是RANK() OVER w AS 'Rank'，它的意思就是在窗口查询w的结果集中进行计算得到当前行的排名。这个窗口查询是在ORDRE BY 子句之前定义的，WINDOW w AS (PARTITION BY hire_date_year ORDER BY salary DESC)，这里运用了PARTITION分区，你可以理解为分组。上面的SQL的意思是，查询员工的薪资，这里的每条记录多了一个计算列，这个计算列是根据WINDOW中的定义来计算的，即按照hire_date_year进行分组，并根据salary进行排序，那么之前查询到行时就在此WINDOW中找到分区中对应记录的RANK值，将这RANK值与之前行数据关联，就可以得到当前行的排名值。

下面看一个简单的ROW_NUMBER()函数的示例：

可以通过ROW_NUMBER()获取所有行的排名，注意RANK()函数是获取分区排名。mysql> SELECT CONCAT(first_name, " ", last_name) AS

full_name, salary, ROW_NUMBER() OVER(ORDER BY salary

DESC) AS 'Rank' FROM employees JOIN salaries ON

salaries.emp_no=employees.emp_no LIMIT 10;

+-------------------+--------+------+

| full_name | salary | Rank |

+-------------------+--------+------+

| Tokuyasu Pesch | 158220 | 1 |

| Tokuyasu Pesch | 157821 | 2 |

| Honesty Mukaidono | 156286 | 3 |

| Xiahua Whitcomb | 155709 | 4 |

| Sanjai Luders | 155513 | 5 |

| Tsutomu Alameldin | 155377 | 6 |

| Tsutomu Alameldin | 155190 | 7 |

| Tsutomu Alameldin | 154888 | 8 |

| Tsutomu Alameldin | 154885 | 9 |

| Willard Baca | 154459 | 10 |

+-------------------+--------+------+

10 rows in set (6.24 sec)

这里的计算列是ROW_NUMBER() OVER (ORDER BY salary DESC) AS 'Rank'，注意，这里没有使用命令窗口，使用的匿名窗口为(ORDER BY salary DESC)，这里没有使用PARTITON分区，所以是获取的行是在所有记录上的排名。

其它的计算函数如下：CUME_DIST()：累积分布值

DENSE_RANK()：当前行在分区中的排名，无缝隙

FIRST_VALUE()：获取窗口集中第1行的参数值

LAG()：分区中滞后于当前行的行参数值

LAST_VALUE()：来自窗口数据集中最后一行的参数值

LEAD()：获取分区中当前行前一行的参数值

NTH_VALUE()：获取窗口数据集中第n行的参数值

NTILE()：分区中当前行的桶号

PERCENT_RANK()：百分比排名值

RANK()：获取分区中当前行的排名，有缝隙

ROW_NUMBER()：分区中当前行号