如何在MSSQL与SQLite中计算首次后续状态连续记录的长度？

原始数据 #

date          unit     status
2023-04-30    unit1    1
2023-05-31    unit1    1
2023-08-31    unit1    1
2023-09-30    unit1    1
2023-11-30    unit1    1
2023-12-31    unit1    1
2024-01-31    unit1    1
2024-02-28    unit1    1

需求 #

根据指定的参考日期，计算每个unit在参考日期之后首次出现的连续status=1记录的长度，要求同时适配生产环境的MSSQL和单元测试的SQLite。

连续记录判定规则：

• 仅统计status=1的记录
• 连续指相邻两条记录的月份连续（如2023-08-31与2023-09-30视为连续）
• 若两条status=1记录间隔超过1个月，则连续序列中断

示例说明 #

示例1：参考日期为2023-05-15 #

期望输出：

unit     streak
unit1    3

原因：2023年5月之后首个status=1的月份是2023年8月，随后统计连续的月份数量，最终得长度为3。

示例2：参考日期为2023-11-01 #

期望输出：

unit     streak
unit1    3

原因：2023年11月之后首个status=1的月份是2023年12月，连续记录截止到2024年2月——因未记录status=0的月份，且下一个status=1的月份间隔超过1个月，统计得长度为3。

适配方案代码 #

MSSQL版本 #

DECLARE @ReferenceDate DATE = '2023-05-15'; -- 替换为目标参考日期

WITH ProcessedData AS (
    SELECT 
        unit,
        DATEFROMPARTS(YEAR(date), MONTH(date), 1) AS month_start
    FROM your_table_name
    WHERE status = 1
        AND date > @ReferenceDate
),
OrderedData AS (
    SELECT 
        unit,
        month_start,
        LAG(month_start) OVER (PARTITION BY unit ORDER BY month_start) AS prev_month_start,
        CASE 
            WHEN LAG(month_start) OVER (PARTITION BY unit ORDER BY month_start) IS NULL THEN 1
            WHEN DATEDIFF(MONTH, prev_month_start, month_start) > 1 THEN 1
            ELSE 0
        END AS is_new_streak
    FROM ProcessedData
),
StreakGroups AS (
    SELECT 
        unit,
        month_start,
        SUM(is_new_streak) OVER (PARTITION BY unit ORDER BY month_start) AS streak_group_id
    FROM OrderedData
),
FirstStreak AS (
    SELECT 
        unit,
        COUNT(*) AS streak_length
    FROM StreakGroups
    WHERE streak_group_id = (SELECT MIN(streak_group_id) FROM StreakGroups sg WHERE sg.unit = StreakGroups.unit)
    GROUP BY unit
)
SELECT unit, streak_length AS streak
FROM FirstStreak;

SQLite版本 #

WITH ProcessedData AS (
    SELECT 
        unit,
        DATE(date, 'start of month') AS month_start
    FROM your_table_name
    WHERE status = 1
        AND date > '2023-05-15' -- 替换为目标参考日期
),
OrderedData AS (
    SELECT 
        unit,
        month_start,
        LAG(month_start) OVER (PARTITION BY unit ORDER BY month_start) AS prev_month_start,
        CASE 
            WHEN LAG(month_start) OVER (PARTITION BY unit ORDER BY month_start) IS NULL THEN 1
            WHEN (STRFTIME('%Y%m', month_start) - STRFTIME('%Y%m', prev_month_start)) > 1 THEN 1
            ELSE 0
        END AS is_new_streak
    FROM ProcessedData
),
StreakGroups AS (
    SELECT 
        unit,
        month_start,
        SUM(is_new_streak) OVER (PARTITION BY unit ORDER BY month_start) AS streak_group_id
    FROM OrderedData
),
FirstStreak AS (
    SELECT 
        unit,
        COUNT(*) AS streak_length
    FROM StreakGroups
    WHERE streak_group_id = (SELECT MIN(streak_group_id) FROM StreakGroups sg WHERE sg.unit = StreakGroups.unit)
    GROUP BY unit
)
SELECT unit, streak_length AS streak
FROM FirstStreak;

代码说明 #

• 日期处理：统一将日期转换为当月第一天，消除不同月份最后一天的差异，便于计算月份间隔。MSSQL用DATEFROMPARTS实现，SQLite用DATE(date, 'start of month')实现。
• 连续序列标记：通过LAG()窗口函数获取上一条记录的月份，判断间隔是否超过1个月，标记新序列的起点；再通过累计求和生成每个连续序列的分组ID。
• 首次序列统计：筛选每个unit的最小分组ID（即参考日期后的首个连续序列），统计该分组内的记录数即为streak长度。

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