MYSQL窗口函数实战：用Rows与Range精准定义你的数据滑动窗口

weixin_30800987

592人浏览 · 2026-05-13 05:38:56

weixin_30800987 · 2026-05-13 05:38:56 发布

1. 窗口函数基础：从零理解滑动计算

刚接触MySQL窗口函数时，我也被那些专业术语搞得一头雾水。直到有一次需要分析电商平台的用户购买趋势，才发现这玩意儿简直是数据分析的"瑞士军刀"。简单来说，窗口函数就是在不改变原始数据行数的情况下，对数据进行分组、排序和计算。它和GROUP BY最大的区别就是：GROUP BY会把多行合并成一行，而窗口函数会保留所有原始行。

举个生活中的例子：想象你正在看篮球比赛的技术统计。GROUP BY就像球队的场均数据，把每个球员的表现合并成一个总数；而窗口函数更像是实时更新的球员数据面板，既能看到每个球员的当前得分，又能看到他在全队中的排名，还能看到最近5分钟的表现趋势。这就是窗口函数的魔力——让你在保留细节的同时获得宏观视角。

窗口函数的基本语法结构是这样的：

SELECT 
    窗口函数() OVER (
        PARTITION BY 分组字段 
        ORDER BY 排序字段
        ROWS/RANGE BETWEEN 起始范围 AND 结束范围
    )
FROM 表名

其中最关键的三部分是：

PARTITION BY：相当于分组，但不像GROUP BY那样合并行
ORDER BY：决定数据的排序方式，直接影响窗口范围
ROWS/RANGE：定义"滑动窗口"的大小，也就是计算时考虑哪些行

2. ROWS与RANGE的核心区别：行号VS值范围

很多初学者（包括当年的我）最容易混淆的就是ROWS和RANGE的区别。虽然它们都能定义滑动窗口，但背后的逻辑完全不同。ROWS看的是物理行号，RANGE看的是字段值的范围。这就好比在教室里选人：ROWS是"从你往前数3排的同学"，RANGE是"所有分数比你高5分到低5分的同学"。

2.1 ROWS：简单粗暴的行号定位

ROWS完全基于行号来定义窗口，和字段值无关。它最常用的场景是需要固定行数的计算，比如7日移动平均、最近5次交易金额等。语法上支持以下几种定义方式：

-- 当前行及前2行（共3行）
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

-- 当前行及之后所有行
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

-- 前后各1行（共3行）
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING

我最近用ROWS解决过一个实际问题：计算电商商品的7日销量移动平均。这样能消除单日波动，更准确判断商品趋势。SQL是这样写的：

SELECT 
    product_id,
    sale_date,
    daily_sales,
    AVG(daily_sales) OVER (
        PARTITION BY product_id
        ORDER BY sale_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS 7_day_avg
FROM product_sales

2.2 RANGE：基于值的智能窗口

RANGE则根据字段值的范围来定义窗口，行数不固定。它特别适合处理时间序列、连续数值等场景。比如要分析"同类价格区间"的商品销售情况，RANGE就是最佳选择。

RANGE的边界定义比ROWS更丰富，支持数值间隔和时间间隔：

-- 数值范围：当前值±1000的范围
RANGE BETWEEN 1000 PRECEDING AND 1000 FOLLOWING

-- 时间范围：最近7天
RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND CURRENT ROW

曾经有个金融项目需要分析股票异常波动，我就用了RANGE来找出那些价格突然偏离均线±5%的交易日：

SELECT 
    stock_code,
    trade_date,
    close_price,
    AVG(close_price) OVER (
        PARTITION BY stock_code
        ORDER BY trade_date
        RANGE BETWEEN INTERVAL 5 DAY PRECEDING AND INTERVAL 1 DAY PRECEDING
    ) AS 5_day_avg,
    close_price - AVG(close_price) OVER (
        PARTITION BY stock_code
        ORDER BY trade_date
        RANGE BETWEEN INTERVAL 5 DAY PRECEDING AND INTERVAL 1 DAY PRECEDING
    ) AS deviation
FROM stock_daily
WHERE ABS(deviation) > 0.05 * 5_day_avg

3. 实战对比：相同需求的不同实现

理解了理论，我们通过几个实际案例来看看ROWS和RANGE在不同场景下的表现差异。这些案例都来自我工作中真实遇到的问题，相信你也会遇到类似的场景。

3.1 案例一：员工薪资分布分析

假设我们有一个员工表，需要分析不同薪资区间的人数分布。如果用ROWS实现：

SELECT 
    emp_id,
    salary,
    COUNT(*) OVER (
        ORDER BY salary
        ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING
    ) AS rows_count
FROM employees

这个查询会返回每个员工薪资前后各2行（共5行）的人数。但问题来了：如果薪资有重复值，比如第3和第4行都是50000，ROWS会严格按行数计算，可能漏掉同薪资的其他行。

改用RANGE后：

SELECT 
    emp_id,
    salary,
    COUNT(*) OVER (
        ORDER BY salary
        RANGE BETWEEN 5000 PRECEDING AND 5000 FOLLOWING
    ) AS range_count
FROM employees

这次计算的是每个员工薪资±5000范围内的人数，不管有多少行。这才是我们真正想要的薪资区间分布！

3.2 案例二：用户活跃度趋势

在分析用户日活时，我们通常需要计算周环比。用ROWS实现7日移动平均很简单：

SELECT 
    date,
    dau,
    AVG(dau) OVER (
        ORDER BY date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS weekly_avg
FROM daily_active_users

但如果数据有缺失（比如节假日没有记录），ROWS就会出错，因为它严格按行数计算。这时RANGE就更可靠：

SELECT 
    date,
    dau,
    AVG(dau) OVER (
        ORDER BY date
        RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW
    ) AS weekly_avg
FROM daily_active_users

这样即使中间缺了几天的数据，计算的时间范围仍然是准确的7天。