MySQL中如何用非空条件LAG()函数实现特定日期偏移需求

我有一个MySQL大型数据集data，结构和样例数据如下：

id          date          val
aaaaa       2021-01-01    TRUE
aaaaa       2021-01-02    FALSE
aaaaa       2021-01-03    FALSE
aaaaa       2021-01-04    TRUE
aaaaa       2021-01-05    FALSE
aaaaa       2021-01-06    TRUE
aaaaa       2021-01-07    FALSE
...
aaaaa       2021-12-31    FALSE
aaaab       2021-01-01    TRUE
aaaab       2021-01-02    FALSE
...
zzzzz       2021-12-31    FALSE

• id为字符串类型
• date范围是2021-01-01至2021-12-31，无缺失日期
• val为布尔值TRUE或FALSE
• 数据集已按id, date排序

需要新增两列：

• lagged_date：存储当前id对应的上一个val = TRUE的日期
• date_diff：当前行date与lagged_date的天数差

理想结果如下：

id          date          val        lagged_date     date_diff
aaaaa       2021-01-01    TRUE       NULL            NULL
aaaaa       2021-01-02    FALSE      2021-01-01      1
aaaaa       2021-01-03    FALSE      2021-01-01      2
aaaaa       2021-01-04    TRUE       2021-01-01      3
aaaaa       2021-01-05    FALSE      2021-01-04      1
aaaaa       2021-01-06    TRUE       2021-01-04      2
aaaaa       2021-01-07    FALSE      2021-01-06      1
...
aaaaa       2021-12-31    FALSE      2021-12-25      6
aaaab       2021-01-01    TRUE       NULL            NULL
aaaab       2021-01-02    FALSE      2021-01-01      1
...

我尝试过以下查询，但lagged_date未得到预期结果，只返回了偏移后的val值，用MAX(date)也无效：

SELECT *,
       MAX(val) OVER (
          PARTITION BY id, val
          ORDER BY date
          ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
       ) AS lagged_date,
       DATE_DIFF(date, lagged_date, DAY) AS date_diff
  FROM data

解决方案 #

要实现需求，核心是按ID分组，为每一行找到之前最近的val=TRUE的日期，以下是两种高效的实现方式（适用于MySQL 8.0+）：

方法1：直接用条件窗口函数

通过CASE筛选出val=TRUE的日期，再用MAX()窗口函数保留最近的有效值（自动忽略NULL，实现向前填充）：

SELECT 
    id,
    date,
    val,
    MAX(CASE WHEN val = TRUE THEN date END) OVER (
        PARTITION BY id 
        ORDER BY date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
    ) AS lagged_date,
    DATEDIFF(date, MAX(CASE WHEN val = TRUE THEN date END) OVER (
        PARTITION BY id 
        ORDER BY date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
    )) AS date_diff
FROM data
ORDER BY id, date;

方法2：分组标记+LAG函数

先为每个id内的val=TRUE行生成分组标识，再获取上一组的日期：

WITH marked_data AS (
    SELECT 
        *,
        SUM(CASE WHEN val = TRUE THEN 1 ELSE 0 END) OVER (
            PARTITION BY id 
            ORDER BY date
        ) AS group_id
    FROM data
)
SELECT 
    id,
    date,
    val,
    LAG(MAX(date) OVER (PARTITION BY id, group_id)) OVER (
        PARTITION BY id 
        ORDER BY date
    ) AS lagged_date,
    DATEDIFF(date, LAG(MAX(date) OVER (PARTITION BY id, group_id)) OVER (
        PARTITION BY id 
        ORDER BY date
    )) AS date_diff
FROM marked_data
ORDER BY id, date;

说明 #

• 方法1更简洁，适合直接使用，利用MAX()忽略NULL的特性自动填充最近的有效日期。
• 方法2逻辑更直观，通过分组标记区分每个val=TRUE的周期，适合需要扩展其他分组逻辑的场景。
• 两种方法都依赖窗口函数，效率远高于自连接，适合处理大型数据集。

内容的提问来源于stack exchange，提问作者raven