mysql窗口函数，使 SQL 语句更简洁、高效

首页 2025-09-12 17:40:23

MySQL 8.0 及以上版本引入了窗口函数（Window Functions），这是一种强大的 SQL 特性，用于在一组与当前行相关的行上执行计算。与聚合函数（如 SUM()、AVG()）不同，窗口函数不会将多行合并为一行，而是为每行返回一个结果。

窗口函数基本语法

							sql

				函数名(列名) OVER (
    [PARTITION BY 列1, 列2, ...]  -- 分组（类似 GROUP BY，但不合并行）
    [ORDER BY 列3, 列4, ... [ASC|DESC]]  -- 组内排序
    [ROWS|RANGE BETWEEN ...]  -- 窗口范围（可选）
)

常用窗口函数分类

1. 排名函数

RANK()：相同值排名相同，下一名跳过空缺（如 1,1,3）
DENSE_RANK()：相同值排名相同，下一名不跳过空缺（如 1,1,2）
ROW_NUMBER()：为每行分配唯一序号，即使值相同（如 1,2,3）

示例：

							sql

				SELECT 
    name, score,
    RANK() OVER (ORDER BY score DESC) AS rnk,
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rnk,
    ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num
FROM students;

2. 聚合类窗口函数

将普通聚合函数用作窗口函数，如 SUM()、AVG()、MAX()、MIN() 等。

示例：

							sql

				SELECT 
    sale_date, product, amount,
    SUM(amount) OVER (PARTITION BY product) AS total_by_product,  -- 按产品分组求和
    AVG(amount) OVER (ORDER BY sale_date) AS moving_avg  -- 按日期排序的移动平均值
FROM sales;

3. 取值函数

LAG(列名, n)：获取当前行的前 n 行数据
LEAD(列名, n)：获取当前行的后 n 行数据
FIRST_VALUE(列名)：获取窗口内第一行数据
LAST_VALUE(列名)：获取窗口内最后一行数据

示例：

							sql

				SELECT 
    month, revenue,
    LAG(revenue, 1) OVER (ORDER BY month) AS prev_month_revenue,  -- 上月收入
    LEAD(revenue, 1) OVER (ORDER BY month) AS next_month_revenue  -- 下月收入
FROM monthly_revenue;

4. 分布函数

PERCENT_RANK()：计算百分比排名（范围 0~1）
CUME_DIST()：计算累积分布值（范围 0~1）

窗口范围（ROWS/RANGE）

可通过 ROWS 或 RANGE 限制窗口内的行范围：

							sql

				SELECT 
    sale_date, amount,
    -- 包含当前行及前2行、后1行的窗口
    AVG(amount) OVER (ORDER BY sale_date 
                      ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING) AS moving_avg
FROM sales;

常用范围关键字：

CURRENT ROW：当前行
n PRECEDING：前 n 行
n FOLLOWING：后 n 行
UNBOUNDED PRECEDING：窗口起始行
UNBOUNDED FOLLOWING：窗口结束行

实际应用场景

排行榜（如成绩排名、销量排名）
同比 / 环比分析（与上期数据对比）
累计求和（如累计销售额）
移动平均值（如近 7 天平均销量）
分组内 Top N 查询

窗口函数极大简化了复杂的数据分析场景，避免了繁琐的子查询或自连接，使 SQL 语句更简洁、高效。

阅读全文

上一篇：mysql8安装，不同操作系统上安装 MySQL 8.0 的详细步骤
下一篇：redis和mysql的区别，不同的设计理念和适用场景