PostgreSQL中统计用户最长连续训练天数（重复日期场景）

原查询的问题在于未对用户训练日期做去重处理——当用户单日在多个场地训练时，day字段会重复出现，干扰连续天数的计算逻辑，最终导致统计结果偏小。

解决思路 #

先对每个用户的训练日期去重，确保同一天的多次训练仅被算作一天，再基于去重后的日期集合计算连续天数。

修正后的SQL查询 #

WITH unique_training_days AS (
    -- 第一步：去重，保留每个用户唯一的训练日期
    SELECT DISTINCT user_id, day
    FROM logs
),
continuous_date_groups AS (
    -- 第二步：为连续日期生成分组标识
    SELECT
        user_id,
        day,
        -- 连续的日期会生成相同的group_key
        DATE_SUB(day, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY day) DAY) AS group_key
    FROM unique_training_days
)
-- 第三步：计算每个用户的最长连续天数
SELECT
    user_id,
    MAX(DATEDIFF(MAX(day), MIN(day)) + 1) AS longest_continuous_days
FROM continuous_date_groups
GROUP BY user_id;

逻辑说明 #

1. unique_training_days：通过DISTINCT过滤同一用户同一天的重复训练记录，保证每个日期仅参与一次统计。
2. continuous_date_groups：对每个用户的日期按顺序排序，用当前日期减去对应行号（ROW_NUMBER()），连续日期会得到相同的group_key，以此区分不同的连续日期段。
3. 最终聚合：按用户和分组标识分组，计算每个连续段的天数（DATEDIFF(MAX(day), MIN(day)) + 1），取最大值即为该用户的最长连续训练天数。

示例验证 #

假设某用户的训练记录day字段为：2024-01-01, 2024-01-01, 2024-01-02, 2024-01-03, 2024-01-04

• 去重后得到4个唯一日期：2024-01-01, 2024-01-02, 2024-01-03, 2024-01-04
• 计算后最长连续天数为4，符合预期。

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