在PostgreSQL中获取每个用户的最长连续天数段

我来帮你搞定这个需求！先理清楚咱们要做的事：从给定的用户会话数据里，找出每个用户连续登录的最长时间段——这里的连续指的是daydiff=1的连续记录（第一天的daydiff=0是起始点）。下面一步步来实现：

第一步：先整理你的原始数据集 #

第二步：核心思路——用窗口函数划分连续段 #

连续会话的判断逻辑很简单：当daydiff != 1时，说明这是一个新的连续段的起点（比如第一天的0，或者间隔多天的2）。我们可以用累加计数的方式，给每个连续段分配唯一的组ID，之后就能对每个组统计时长了。

第三步：完整SQL实现 #

WITH continuous_segments AS (
    SELECT
        respondent_id,
        day_session,
        -- 生成连续段ID：遇到非1的daydiff就累加1，同一个连续段ID相同
        SUM(CASE WHEN daydiff != 1 THEN 1 ELSE 0 END) OVER (
            PARTITION BY respondent_id
            ORDER BY day_session
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS segment_id
    FROM your_table_name -- 记得替换成你的实际表名
),
segment_stats AS (
    SELECT
        respondent_id,
        segment_id,
        COUNT(*) AS consecutive_days, -- 连续天数就是段内的记录数
        MIN(day_session) AS start_date,
        MAX(day_session) AS end_date
    FROM continuous_segments
    GROUP BY respondent_id, segment_id
),
ranked_segments AS (
    SELECT
        *,
        -- 按连续天数倒序排名，天数最多的排第1
        RANK() OVER (
            PARTITION BY respondent_id
            ORDER BY consecutive_days DESC, start_date ASC
        ) AS rank
    FROM segment_stats
)
SELECT
    respondent_id,
    consecutive_days,
    start_date,
    end_date
FROM ranked_segments
WHERE rank = 1;

代码逐段解释 #

1. continuous_segments CTE：用窗口函数SUM()给每个连续段打标签。每碰到daydiff≠1的记录，就给当前段ID加1，这样同一个连续会话的所有记录会共享一个ID。
2. segment_stats CTE：对每个用户的每个连续段，统计该段的总天数、起始日期和结束日期。
3. ranked_segments CTE：用RANK()给每个用户的连续段排序，最长的段排第1；如果有多个段天数相同，会保留所有并列的最长段。
4. 最后筛选出rank=1的记录，就是每个用户的最长连续天数段。

针对你数据集的结果示例 #

对于用户nmo876，从11/24/2017到12/11/2017（假设后续记录的daydiff都是1）的连续段天数最多，这个查询会返回该段的起始日期、结束日期和总天数。

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