如何用PostgreSQL计算用户每次登录的会话时长？

核心思路 #

要解决这个问题，核心是用窗口函数划分每个用户的会话边界，再针对每个会话计算有效时长。不需要复杂的表分区，靠窗口函数就能高效完成逻辑。

分步实现SQL #

以下是可读性和性能兼顾的SQL代码，用CTE拆分逻辑，便于理解和维护：

WITH user_events AS (
    -- 标记Sign-In事件，同时获取当前用户下一次Sign-In的时间
    SELECT
        user_id,
        category,
        date,
        CASE WHEN category = 'Sign-In' THEN date END AS session_start,
        -- 按用户分区、时间排序，拿到下一次Sign-In的时间，无后续则用极晚时间兜底
        LEAD(CASE WHEN category = 'Sign-In' THEN date END) OVER (
            PARTITION BY user_id 
            ORDER BY date
        ) AS next_sign_in_time
    FROM events
),
session_boundaries AS (
    -- 提取所有Sign-In会话，确定会话的时间截止边界
    SELECT
        user_id,
        session_start,
        COALESCE(next_sign_in_time, '9999-12-31'::timestamp) AS session_end_cutoff
    FROM user_events
    WHERE session_start IS NOT NULL
),
session_last_events AS (
    -- 统计每个会话内的最晚事件时间，同时检查是否存在Page View
    SELECT
        sb.user_id,
        sb.session_start,
        MAX(e.date) AS last_event_time,
        BOOL_OR(e.category = 'Page View') AS has_page_view
    FROM session_boundaries sb
    JOIN events e 
        ON e.user_id = sb.user_id
        AND e.date >= sb.session_start
        AND e.date < sb.session_end_cutoff
    GROUP BY sb.user_id, sb.session_start
)
-- 最终计算会话时长
SELECT
    user_id,
    session_start,
    -- 有Page View则计算时间差，否则时长为0
    CASE 
        WHEN has_page_view THEN EXTRACT(EPOCH FROM (last_event_time - session_start))::INT
        ELSE 0
    END AS duration
FROM session_last_events
ORDER BY user_id, session_start;

关键逻辑解释 #

1. LEAD()窗口函数：按用户分组、事件时间排序，获取当前Sign-In之后的下一次Sign-In时间，以此作为当前会话的截止边界。如果是用户最后一次Sign-In，用COALESCE替换为极晚时间，确保能包含后续所有事件。
2. 会话事件匹配：通过JOIN关联会话边界和原始事件表，精准筛选出当前会话时间范围内的所有事件。
3. 时长计算：用MAX(e.date)拿到会话内的最后事件时间，结合BOOL_OR判断是否存在Page View，无Page View时直接返回0。

性能优化建议 #

• 给events表创建复合索引：CREATE INDEX idx_events_user_date ON events(user_id, date);，这能大幅提升窗口函数和JOIN的执行效率，尤其在数据量较大时效果明显。
• 若不需要保留未结束的会话（即当前时间之后无Sign-In的会话），可将'9999-12-31'::timestamp替换为CURRENT_TIMESTAMP。

示例结果 #

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