mysql 8 新特性窗口函数

什么是窗口函数？

窗口函数是一种在SQL查询中对结果集进行分组和排序的功能。它可以在查询结果中计算聚合函数（如总和、平均值、计数等），并将结果与原始数据进行关联。窗口函数的语法如下：

简单来说窗口函数在保持所有原始行不变的情况下，为每一行数据增加一个“计算列”，这个计算列的值是根据它周围相关行（即“窗口”）计算出来的。

使用窗口函数时，窗口函数会新增一列，这列的名称是窗口函数的名称，值是根据窗口函数的计算规则计算出来的。

窗口函数的语法

  <窗口函数> OVER (
    [PARTITION BY <列名>]
    [ORDER BY <列名>]
  )

其中，<窗口函数> 是要计算的聚合函数（如SUM、AVG、COUNT等），<列名> 是用于分组和排序的列。PARTITION BY 子句用于指定分组的列，ORDER BY 子句用于指定排序的列。

窗口函数的类型

窗口函数可以分为以下几类：

1. 聚合函数：如SUM、AVG、COUNT等，用于计算窗口内的聚合值。
2. 排名函数：如RANK、DENSE_RANK、ROW_NUMBER等，用于为窗口内的行分配排名。
3. 分布函数：如PERCENT_RANK、CUME_DIST等，用于计算窗口内的分布值。
4. 值函数：如LAG、LEAD、FIRST_VALUE、LAST_VALUE等，用于获取窗口内的前一行、后一行、第一行、最后一行等值。

排名函数

假设我们有这样一张简单的成绩单 scores：

| 孙七 | 80 |

为这些学生排名。

ROW_NUMBER() 行号函数

    SELECT
    student,
    score,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS ranking
FROM scores;

执行结果：

| 孙七 | 80 | 5 |

特点：

1.无并列且排名连续：为每个数据行分配一个唯一的行号，行号从1开始递增。

2.不确定性：对于成绩相同的李四与王五，依然进行了顺序排名，而不是并列排名。这个取决于内部数据库

3.应用场景：需要唯一行号的场景，比如“取前N条记录”。

RANK() 标准排名

SELECT
    student,
    score,
    RANK() OVER (ORDER BY score DESC) AS ranking
FROM scores;

结果：

| 孙七 | 80 | 5 |

特点：

1.有并列并且排名不连续，这里李四和王五并列第二，但赵六是从4开始的，而不是3。

2.适用场景：需要真实反映并列情况的排名，如竞赛排名。

DENSE_RANK() 密集排名

SELECT
    student,
    score,
    DENSE_RANK() OVER (ORDER BY score DESC) AS ranking
FROM scores;

结果：

| 孙七 | 80 | 4 |

特点：

1.有并列并且排名连续，这里李四和王五并列第二，但赵六是从3开始的，而不是4。

2.适用场景：需要为不同“等级”分组的场景，比如“找出成绩在前三等的所有学生”（这里会包含95, 90, 85分的学生）。

分布函数

继续以 scores 表为例

窗口函数中的分布函数 (Distribution Functions)，主要用来计算一个值在它所在的数据分区（或整个结果集）中的相对位置。

它们的结果通常是一个介于0和1之间的小数，可以看作是“百分比排名”。

最常用的分布函数有两个：PERCENT_RANK 和 CUME_DIST。

PERCENT_RANK 百分比排名

计算公式：(rank - 1) / (total_rows - 1)

rank 是使用RANK()函数计算出的排名。

total_rows 是当前分区（或整个窗口）的总行数。

你可以把它理解为：“在我前面的人数”占“参与排名总人数减一”的比例。

SELECT
    student,
    score,
    RANK() OVER (ORDER BY score DESC) AS rk, -- 为了方便理解，我们先把RANK()的结果也选出来
    PERCENT_RANK() OVER (ORDER BY score DESC) AS pr
FROM scores;

结果

| 孙七 | 80 | 5 | 1.0 | (5 - 1) / (5 - 1) = 4 / 4 |

结果解读：

张三 (95分): 他的百分比排名是0，意味着他击败了100%的人，没有人排在他前面。

李四/王五 (90分): 他们的百分比排名是0.25，意味着他们超过了25%的人（即超过了排在他们后面的3个人中的1/4位置的人数比例，这里公式更准确）。

赵六 (85分): 他的百分比排名是0.75，意味着他超过了75%的人。

孙七 (80分): 他的百分比排名是1.0，意味着他是最后一名。

CUME_DIST 累计分布

CUME_DIST (Cumulative Distribution) 计算的是“累积分布”，即小于或等于当前值的行数，占总行数的比例。

计算公式：（number_of_rows_with_value < = current_value / total_rows ）

number_of_rows_with_value < = current_value 是指，分数小于等于当前行分数的总行数（对于降序排名，则是大于等于）。

你可以把它理解为：“包括我自己以及排在我前面的人数”占“总人数”的比例。

SELECT
    student,
    score,
    CUME_DIST() OVER (ORDER BY score DESC) AS cd
FROM scores;

结果

| 孙七 | 80 | 1.0 | 分数 >= 80 的有5人，总共5人 -> 5 / 5 |

结果解读：

张三 (95分): 他的累积分布是0.2，意味着全校前20%的学生分数在95分及以上。

李四/王五 (90分): 他们的累积分布是0.6，意味着全校前60%的学生分数在90分及以上。

孙七 (80分): 他的累积分布是1.0，意味着全校100%的学生分数都在80分及以上。

总结对比

函数 核心含义 计算公式（概念） 结果范围

PERCENT_RANK “我超过了百分之多少的人？” (排名-1) / (总人数-1) [0, 1]

CUME_DIST “排在我前面以及和我并列的人，占总人数的百分之多少？” (排名< =我的人数) / 总人数 (0, 1]

值函数

为了更好地学习，虚构一张简单的月度销售表 monthly_sales：

| 2025-05 | 180 |

LAG() - 向前看

LAG() 函数用于获取当前行往前数N行的某个字段的值。它最经典的应用场景就是计算环比（与上一个周期比较）。

语法: LAG(column, offset, default_value) OVER (ORDER BY ...)

column: 你想获取哪个字段的值。

offset: 你想往前看几行，默认是1（即上一行）。

default_value: 如果往前看没有对应的行（比如第一行就没有“上一行”），此时该用什么默认值，默认是NULL。

SELECT
    month,
    sales,
    LAG(sales, 1, 0) OVER (ORDER BY month) AS last_month_sales
FROM monthly_sales;

结果与分析

| 2025-05 | 180 | 130 |

结果解读：

2025-01: 它是第一行，没有“上一行”，所以last_month_sales取了我们设定的默认值0。

2025-02: 它的last_month_sales就是LAG()函数向前看了1行，取到了1月份的sales值100。

其他行以此类推。有了这个“上月销售额”列，计算环比增长就变得非常简单了。

LEAD() - 向后看

LEAD() 与 LAG() 正好相反，它用于获取当前行往后数N行的某个字段的值。适合用来预估或与下一个周期对比。

语法: LEAD(column, offset, default_value) OVER (ORDER BY ...)

SELECT
    month,
    sales,
    LEAD(sales, 1, 0) OVER (ORDER BY month) AS next_month_sales
FROM monthly_sales;

| 2025-05 | 180 | 0 |

结果解读：

2025-01: 它的next_month_sales就是LEAD()函数向后看了1行，取到了2月份的sales值120。

2025-05: 它是最后一行，没有“下一行”，所以next_month_sales取了默认值0。

FIRST_VALUE() - 看窗口的第一个

FIRST_VALUE() 用于获取当前窗口内，排序后的第一行的某个字段的值。

语法: FIRST_VALUE(column) OVER (PARTITION BY ... ORDER BY ...)

就是窗口函数创建的这一列都取第一行的某个值

SELECT
    month,
    sales,
    FIRST_VALUE(sales) OVER (ORDER BY month) AS first_month_sales_of_year
FROM monthly_sales;

| 2025-05 | 180 | 100 |

结果解读：

ORDER BY month 定义了排序，1月是第一行。

FIRST_VALUE(sales) 在每一行都会去窗口中寻找按此排序的第一行的sales值，也就是1月份的100。

如果有PARTITION BY year，那么计算2026年的数据时，它就会取2026年第一个月的值。

LAST_VALUE() - 看窗口的最后一个

这个函数与FIRST_VALUE()正好相反，它用于获取当前窗口内，排序后的最后一行的某个字段的值。

但是这个函数有陷阱的，必须去规定窗口的范围，否则会取到错误的结果。

SQL示例（注意陷阱）

如果我们直接使用，结果可能出乎意料：

SELECT
    month,
    sales,
    LAST_VALUE(sales) OVER (ORDER BY month) AS last_val -- 这是有问题的用法
FROM monthly_sales;

结果（不符合预期的）：

| ... | ... | ... |

陷阱解释：

默认情况下，窗口的范围是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（从第一行到当前行）。所以，当计算2月份的LAST_VALUE时，它的窗口只包含1月和2月的数据，最后一行的值自然就是2月自己的值120。

正确用法：

必须完整地定义窗口范围，告诉它要看到整个分区的最后。

SELECT
    month,
    sales,
    LAST_VALUE(sales) OVER (
        ORDER BY month
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING -- 定义窗口为从头到尾
    ) AS last_month_sales_of_year
FROM monthly_sales;

| 2025-05 | 180 | 180 |

结果解读：

通过ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING，我们把窗口扩展到了整个数据集，LAST_VALUE就能正确地取到5月份的销售额180了。

窗口函数的使用场景

窗口函数的使用场景：如计算移动平均线、计算累计总和、计算排名等。

窗口函数的注意事项

1. 窗口函数只能在SELECT子句中使用，不能在WHERE子句中使用。
2. 窗口函数只能在ORDER BY子句中使用，不能在GROUP BY子句中使用。
3. 窗口函数只能在SELECT子句中使用，不能在INSERT、UPDATE、DELETE子句中使用。
4. 窗口函数只能在SELECT子句中使用，不能在子查询中使用。
5. 窗口函数只能在SELECT子句中使用，不能在CASE表达式中使用。
6. 窗口函数只能在SELECT子句中使用，不能在HAVING子句中使用。
7. 窗口函数只能在SELECT子句中使用，不能在ORDER BY子句中使用。
8. 窗口函数只能在SELECT子句中使用，不能在PARTITION BY子句中使用。
9. 窗口函数只能在SELECT子句中使用，不能在窗口函数中使用。

总结

窗口函数是MySQL 8.0引入的强大功能，它可以在SQL查询中对数据进行更复杂的分析和处理。

作为初学者的个人理解，我感觉在平时开发或者基本的sql语句的话是用不到的，窗口函数还是很强大的。

如果基本知识不扎实还是以夯实基础为主。