如何编写SQLite查询从指定日期查找连续记录天数

数据表结构 #

假设表名为records，结构如下：

需求：根据指定查询日期，计算从该日期（含）往前的连续记录天数；若该日期无记录，返回0。

解决方案1：递归CTE实现 #

使用递归公共表表达式（CTE）可以直观地逐天验证连续记录，逻辑清晰易理解：

完整SQL查询 #

WITH RECURSIVE streak_cte AS (
    -- 初始步骤：检查查询日期是否存在记录，存在则初始化连续天数为1
    SELECT 
        1 AS streak,
        strftime('%Y-%m-%d', :query_date, '%d/%m/%Y') AS current_date
    WHERE EXISTS (
        SELECT 1 FROM records 
        WHERE strftime('%Y-%m-%d', dt, '%d/%m/%Y') = strftime('%Y-%m-%d', :query_date, '%d/%m/%Y')
    )
    UNION ALL
    -- 递归步骤：往前推一天，若存在记录则连续天数加1
    SELECT 
        streak + 1,
        date(current_date, '-1 day')
    FROM streak_cte
    WHERE EXISTS (
        SELECT 1 FROM records 
        WHERE strftime('%Y-%m-%d', dt, '%d/%m/%Y') = date(current_date, '-1 day')
    )
)
-- 取最大连续天数，若无数据（查询日期无记录）则返回0
SELECT COALESCE(MAX(streak), 0) AS streak FROM streak_cte;

参数说明 #

将:query_date替换为目标查询日期，格式保持DD/MM/YYYY（如12/12/2023）。

验证结果 #

对应题目示例场景，查询结果完全匹配预期：

• 查询09/12/2023 → 返回0
• 查询12/12/2023 → 返回3
• 查询11/12/2023 → 返回2
• 查询17/12/2023 → 返回2
• 查询24/12/2023 → 返回0
• 查询26/12/2023 → 返回2

解决方案2：窗口函数分组实现 #

如果偏好非递归方式，可通过窗口函数对连续日期分组，快速定位连续记录区间：

完整SQL查询 #

WITH formatted_dates AS (
    -- 转换所有记录日期为SQLite标准格式
    SELECT strftime('%Y-%m-%d', dt, '%d/%m/%Y') AS date
    FROM records
),
date_groups AS (
    -- 为连续日期生成相同的分组ID
    SELECT 
        date,
        date(date, '-' || ROW_NUMBER() OVER (ORDER BY date) || ' day') AS group_id
    FROM formatted_dates
),
target_group AS (
    -- 获取查询日期所在的分组（若存在）
    SELECT group_id
    FROM date_groups
    WHERE date = strftime('%Y-%m-%d', :query_date, '%d/%m/%Y')
)
-- 统计目标组中<=查询日期的记录数，无匹配则返回0
SELECT COALESCE(
    (SELECT COUNT(*) 
     FROM date_groups
     WHERE group_id = (SELECT group_id FROM target_group)
       AND date <= strftime('%Y-%m-%d', :query_date, '%d/%m/%Y')),
    0
) AS streak;

逻辑说明 #

连续日期按顺序排序后，每个日期减去其行号会得到相同的group_id，通过这个分组可快速定位连续记录区间，再统计区间内到查询日期的记录数即可。

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