如何使用PostgreSQL计算距离当前日期的连续天数？

问题根源 #

原有SQL的计算偏差来自同一用户同一天存在多条watch_history记录时，行号会重复计数，导致连续日期分组逻辑失效，你需要的created_at去重逻辑可以通过提前按用户+日期维度去重实现。

修正后完整SQL（适配到当前日期的连续天数统计） #

WITH unique_dates AS (
    -- 先按用户+日期去重，确保每个用户每天仅保留一条记录，实现你要的created_at去重需求
    SELECT DISTINCT user_id, created_at::timestamp::date AS visit_date
    FROM watch_history
    -- 若要查询特定用户比如示例中的702，可在此处加过滤条件：WHERE user_id = 702
),
grouped_dates AS (
    SELECT 
        user_id,
        visit_date,
        -- 经典连续日期分组逻辑：连续日期减去对应行号会得到相同的分组值
        visit_date - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY visit_date) || ' days')::interval::date AS grouping_date
    FROM unique_dates
)
-- 统计每个用户到当前日期的最新连续天数
SELECT 
    user_id,
    COUNT(*) AS current_consecutive_days
FROM grouped_dates
WHERE grouping_date = CURRENT_DATE - COUNT(*) OVER (PARTITION BY user_id, grouping_date)::integer
GROUP BY user_id, grouping_date
ORDER BY user_id;

全量连续周期统计简化版本 #

如果你只需要统计每个用户所有历史连续周期的天数，不需要限定到当前日期，用下方版本即可：

WITH unique_dates AS (
    SELECT DISTINCT user_id, created_at::timestamp::date AS visit_date
    FROM watch_history
),
grouped_dates AS (
    SELECT 
        user_id,
        visit_date,
        visit_date - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY visit_date) || ' days')::interval::date AS grouping_date
    FROM unique_dates
)
SELECT 
    user_id,
    COUNT(*) AS number_of_consecutive_days,
    MIN(visit_date) AS streak_start_date,
    MAX(visit_date) AS streak_end_date
FROM grouped_dates
GROUP BY user_id, grouping_date
ORDER BY user_id, streak_end_date DESC;

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