如何在MySQL中查询每个客户首个缺失月份前的最大日期

这是个很常见的时间序列连续性分析需求，我给你分享两种方案，优先推荐基于窗口函数的版本（MySQL 8.0+可用），代码清晰且性能更优，完全不需要临时表更新：

方案一：窗口函数+CTE（推荐，MySQL 8.0+） #

这个方案利用LAG()窗口函数获取每个客户的上一个月份，再通过PERIOD_DIFF()判断月份间隔，精准定位首个断点：

WITH client_month_seq AS (
    SELECT 
        ClientID,
        -- 统一日期到每月起始（适配非1号的日期场景）
        DATE_FORMAT(DATE, '%Y-%m-01') AS month_start,
        -- 获取当前客户的上一个月份
        LAG(DATE_FORMAT(DATE, '%Y-%m-01')) OVER (PARTITION BY ClientID ORDER BY DATE) AS prev_month
    FROM your_table_name
),
first_gap_points AS (
    SELECT 
        ClientID,
        prev_month AS last_continuous_month
    FROM client_month_seq
    -- 筛选出第一个出现月份间隔大于1的行
    WHERE PERIOD_DIFF(DATE_FORMAT(month_start, '%Y%m'), DATE_FORMAT(prev_month, '%Y%m')) > 1
    -- 只保留每个客户的首个断点
    GROUP BY ClientID
)
SELECT 
    t.ClientID,
    -- 如果有断点则取断点前的最后日期，无断点则取最大日期
    COALESCE(f.last_continuous_month, MAX(t.DATE)) AS max_date_before_first_gap
FROM your_table_name t
LEFT JOIN first_gap_points f ON t.ClientID = f.ClientID
GROUP BY t.ClientID;

逻辑说明： #

1. client_month_seq：把所有日期统一到当月1号（避免同一天不同日期的干扰），同时用LAG()获取每个客户的上一个月份。
2. first_gap_points：筛选出所有月份间隔超过1的行，这里的prev_month就是连续月份的最后一天；通过GROUP BY ClientID确保只保留每个客户的首个断点。
3. 主查询：用COALESCE()处理两种情况——有断点则取断点前的日期，无断点则取客户的最大日期。

方案二：变量实现（兼容MySQL 5.x） #

如果你的MySQL版本低于8.0，无法使用窗口函数，可通过用户变量来模拟序列追踪：

SELECT 
    ClientID,
    COALESCE(MAX(CASE WHEN gap > 1 THEN prev_date END), MAX(DATE)) AS max_date_before_first_gap
FROM (
    SELECT 
        ClientID,
        DATE,
        -- 记录当前客户的上一个日期
        @prev_date := IF(@current_client = ClientID, @prev_date, NULL) AS prev_date,
        -- 计算当前日期与上一个日期的月份间隔
        @gap := IF(@current_client = ClientID, PERIOD_DIFF(DATE_FORMAT(DATE, '%Y%m'), DATE_FORMAT(@prev_date, '%Y%m')), 0) AS gap,
        -- 更新当前客户标记
        @current_client := ClientID
    FROM your_table_name,
    -- 初始化变量
    (SELECT @current_client := NULL, @prev_date := NULL) vars
    -- 必须按客户和日期排序，保证序列正确
    ORDER BY ClientID, DATE
) t
GROUP BY ClientID;

逻辑说明： #

通过变量@current_client追踪当前处理的客户，@prev_date记录上一行的日期，@gap计算月份间隔。最后通过聚合筛选出首个间隔大于1的情况，取对应的上一个日期；若无断点则取最大日期。

用你的示例数据测试，两个方案都会输出期望的结果：

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