MySQL - MySQL 8.0(三)进阶操作：窗口函数

此学习文是基于MySQL 8.0写的
得益于大神朋友的悉心指导解决不少坑，才写出此文，向大神奉上膝盖

  对于查询中的每一行，可以使用窗口函数，利用与该行相关的行执行计算。 这是通过使用OVER和WINDOW子句来完成的。
  以下是可以执行计算的函数。

• ROW_NUMBER()：分区内当前行的编号。
• RANK()：分区中当前行的等级（有间隔）。
• DENSE_RANK()：分区内当前行的等级（无间隔）。
• PERCENT_RANK()：百分比排名值。
• FIRST_VALUE()：窗口帧中第一行的参数值。
• LAST_VALUE()：窗口帧中最末行的参数值。
• LEAD()：领先于分区内当前行的那一行的参数值。
• LAG()：落后于分区内当前行的那一行的参数值。
• NTH_VALUE()：窗口帧中的第 n 行的参数值。
• NTILE()：分区内当前行的桶的编号。
• COME_DIST()：累积分布值。

准备工作

  为了使示例有效，请先添加hire_date_year、full_name虚拟列
参考：MySQL - MySQL 8.0进阶操作：生成列（generated column）

# 新增虚拟列
mysql> alter table employees.employees add hire_date_year year as (year(hire_date)) virtual;
Query OK, 0 rows affected (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table employees.employees add full_name varchar(30) as (concat(first_name, ' ', last_name)) virtual;
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc employees.employees;
+----------------+---------------+------+-----+---------+-------------------+
| Field          | Type          | Null | Key | Default | Extra             |
+----------------+---------------+------+-----+---------+-------------------+
| emp_no         | int(11)       | NO   | PRI | NULL    |                   |
| birth_date     | date          | NO   |     | NULL    |                   |
| first_name     | varchar(14)   | NO   |     | NULL    |                   |
| last_name      | varchar(16)   | NO   |     | NULL    |                   |
| gender         | enum('M','F') | NO   |     | NULL    |                   |
| hire_date      | date          | NO   |     | NULL    |                   |
| hire_date_year | year(4)       | YES  |     | NULL    | VIRTUAL GENERATED |
| full_name      | varchar(30)   | YES  |     | NULL    | VIRTUAL GENERATED |
+----------------+---------------+------+-----+---------+-------------------+
8 rows in set (0.00 sec)

语法

可以在OVER子句中分割结果

• partition by：分组
• order by：排序

mysql> select full_name, 
    -> gender, 
    -> row_number()over(partition by gender order by hire_date_year asc) rn  
    -> from employees.employees 
    -> limit 3;
+-----------------------+--------+----+
| full_name             | gender | rn |
+-----------------------+--------+----+
| Shen Peternell        | M      |  1 |
| Shigehito Uhrig       | M      |  2 |
| Sivanarayana Dymetman | M      |  3 |
+-----------------------+--------+----+
3 rows in set (0.32 sec)

详情参阅：https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html