PostgreSQL查询指定用户指定日期的反向连续日期记录条数（遇断档停止）

实现思路 #

无需使用generate_series预先指定日期范围，通过窗口函数即可实现断档即停的连续计数需求：

1. 筛选指定用户所有小于等于目标查询日期的记录
2. 将筛选结果按日期降序排列
3. 标记日期断档位置：相邻行日期间隔不等于1则标记为断档
4. 对断档标记累加生成连续分组ID，同组内为连续日期
5. 统计第一个分组的记录条数，即为从查询日起往前的连续记录数

实现代码 #

假设表名为user_date_records，可直接使用如下SQL：

WITH filtered_records AS (
    -- 筛选指定用户、小于等于查询日期的所有记录
    SELECT date
    FROM user_date_records
    WHERE userid = $1  -- 传入的查询userid参数，例如1
      AND date <= $2   -- 传入的查询日期参数，例如'2020-07-29'::date
    ORDER BY date DESC
),
marked_records AS (
    -- 标记断档：相邻行日期差不为1则标记为断档
    SELECT 
        date,
        CASE 
            WHEN LAG(date, 1) OVER (ORDER BY date DESC) - date = 1 THEN 0
            ELSE 1
        END AS is_break
    FROM filtered_records
),
grouped_records AS (
    -- 累加断档标记生成分组ID，连续记录属于同一分组
    SELECT 
        date,
        SUM(is_break) OVER (ORDER BY date DESC) AS group_id
    FROM marked_records
)
-- 统计第一个连续分组的记录数即为结果
SELECT COUNT(*) AS continuous_count
FROM grouped_records
WHERE group_id = 1;

效果验证 #

场景1：无断档 #

表数据如下：

查询userid=1、日期2020-07-29，返回结果为3，符合预期。

场景2：存在断档 #

删除userid=1的2020-07-28记录后，相同查询返回结果为1，符合预期。

注意事项 #

• 如果date字段为带时间的timestamp类型，需要先转成date类型再计算，避免时间部分导致日期间隔计算错误：date::date
• 可根据实际业务替换表名、字段名

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