MySQL 窗口函数按时间范围取最近记录的写法

如果你的目标是“在一个时间范围内，为每台设备取最近的一条完整记录”，最稳妥的写法是分两层：先用 WHERE 把候选记录限制在半开区间内，再用 ROW_NUMBER() 按设备分组、按时间倒序排名，最后在外层保留 rn = 1。这样取到的是整行，而不只是一个最大时间值。

官方地址：https://dev.mysql.com/doc/refman/8.4/en/window-functions-usage.html

固定时间段取最近整行，用“时间过滤 + ROW_NUMBER”；逐行计算最近一段时间里的某个字段，才使用 RANGE BETWEEN INTERVAL ... PRECEDING AND CURRENT ROW。RANGE 和 ROWS 不是一回事。

升级范围：先把“最近”定义成候选集

这类查询通常有三个业务字段：device_id 表示分组键，recorded_at 表示记录时间，id 是能稳定打破同一时间并列的主键。时间范围建议写成“左闭右开”，例如 [2026-11-01 00:00:00, 2026-11-08 00:00:00)，这样连续查询不会重复计算边界时刻。

先把旧的“只找最大时间”思路改成“先筛选、再排名”。窗口函数不会把每个分组压成一行，而是给分组中的每一行附加计算结果；因此它适合把最近记录的状态、来源等其他列一起带出来。

变更表：MAX 只给时间，ROW_NUMBER 才能选整行

例如，每台设备在一周范围内取最后一条上报记录：

WITH ranked AS (
    SELECT
        id,
        device_id,
        recorded_at,
        status,
        payload,
        ROW_NUMBER() OVER (
            PARTITION BY device_id
            ORDER BY recorded_at DESC, id DESC
        ) AS rn -- 时间相同时用主键固定唯一顺序
    FROM device_events
    WHERE recorded_at >= '2026-11-01 00:00:00' -- 左边界包含
      AND recorded_at 

这里的关键不是把日期写进窗口函数，而是先定义候选集。外层过滤 rn = 1 也不能放回同一层的窗口函数表达式中，因为窗口函数只负责产生排名，筛选排名需要在外层完成。

旧代码风险：不要用 MAX 再猜哪一行是最近的

下面的写法只返回时间值。如果再把它和原表连接，遇到两条记录拥有相同 recorded_at 时，可能得到两行；如果直接用非聚合列补齐结果，还会把“最近时间”和“哪一行”混成两个没有确定关联的概念。

SELECT device_id, MAX(recorded_at) AS latest_at
FROM device_events
WHERE recorded_at >= '2026-11-01 00:00:00' -- 同一候选时间范围
  AND recorded_at 

如果业务只需要时间戳，这样写没有问题；如果还要 status、payload 或主键，就优先使用上面的排名方案。排序至少要包含时间和一个唯一键：recorded_at DESC, id DESC。只按时间排序时，同一时刻的行属于并列项，结果可能无法稳定复现。

新写法：需要滚动时间窗口时再用 RANGE

RANGE 表示按排序值的范围选择窗口，ROWS 表示按当前行前后的位置选择窗口。MySQL 8.4 手册允许在时间表达式上使用时间间隔，因此可以计算“当前记录往前七天内的最后一个状态”：

SELECT
    device_id,
    recorded_at,
    status,
    LAST_VALUE(status) OVER (
        PARTITION BY device_id
        ORDER BY recorded_at
        RANGE BETWEEN INTERVAL 7 DAY PRECEDING AND CURRENT ROW
    ) AS latest_status_7d -- 时间值范围内取状态字段
FROM device_events;

这个写法返回的是每一行对应的一个字段值，不是“最近那条记录”的全部列。并且 RANGE 会按时间值处理同值行，不能靠它替代完整行的唯一排序。如果需要同时带出 id、payload 等列，仍应回到“先限定范围，再用 ROW_NUMBER() 选整行”的方案。

回归检查：迁移后重点看四个边界

• 时间边界：统一使用左闭右开区间，确认等于结束时刻的记录不会被下一段重复取到。
• 并列时间：为 ORDER BY 增加唯一键；如果业务没有自增主键，就使用能唯一标识记录的组合键。
• 空值与空结果：明确 recorded_at IS NULL 是否应参与最近记录，时间范围为空时应返回空集还是业务默认值。
• 访问路径：候选过滤和分组排序应有匹配的索引设计，至少把设备键与时间列纳入评估；不要把未实测的执行计划当成结论。

最终可以按这个迁移清单落地：需要整行就选 ROW_NUMBER()；只要最大时间就保留 MAX()；需要每行的滚动字段值才选 RANGE；需要按物理行数而不是时间差计算时才选 ROWS。

相关问题

Q：为什么不直接用 ORDER BY recorded_at DESC LIMIT 1？
它只适合整个结果集取一行；按设备、用户或订单分别取最近记录时，需要分区排名。

Q：同一时间有两条记录怎么办？
在窗口排序中追加唯一键，并明确哪条记录优先，例如 ORDER BY recorded_at DESC, id DESC。

Q：RANGE 可以和 ROW_NUMBER 混用吗？
可以在同一查询中分别定义不同窗口，但它们解决的问题不同；不要把时间范围窗口误当成完整记录选择器。