PostgreSQL中查询用户登录峰值时间的SQL实现求助

核心思路 #

你的方向是对的：将登录、登出时间拆分为事件流，通过累计计数（登录+1、登出-1）追踪活跃用户数，最终找出最大值对应的时间点。以下是完整可执行的PostgreSQL方案：

WITH event_stream AS (
    -- 拆分登录事件：每登录一个用户，活跃数+1
    SELECT logintime AS event_time, 1 AS delta
    FROM log
    UNION ALL
    -- 拆分登出事件：每登出一个用户，活跃数-1
    SELECT logouttime AS event_time, -1 AS delta
    FROM log
),
running_active_users AS (
    SELECT 
        event_time,
        -- 按时间顺序累计事件，得到当前时间点的活跃用户数
        SUM(delta) OVER (ORDER BY event_time) AS active_count
    FROM event_stream
),
peak_ranking AS (
    SELECT 
        event_time AS timestamp,
        active_count AS max,
        -- 按活跃数倒序排名，找出峰值
        RANK() OVER (ORDER BY active_count DESC) AS rank_num
    FROM running_active_users
)
SELECT timestamp, max
FROM peak_ranking
WHERE rank_num = 1;

代码解释 #

1. event_stream：将原表的登录、登出时间拆分为独立事件，用delta标记计数变化（登录+1、登出-1），用UNION ALL保留所有重复时间的事件（避免丢失同一时间的多用户操作）。
2. running_active_users：通过窗口函数SUM(delta)按时间顺序累计，得到每个时间点的实时活跃用户数。
3. peak_ranking：对所有时间点的活跃数进行倒序排名，排名为1的就是峰值记录。
4. 最后筛选出排名第一的记录，得到峰值时间和对应的活跃用户数。

针对你原有SQL的修正 #

你写的SQL存在几个问题：

• unoin拼写错误，应为UNION（推荐用UNION ALL避免去重）
• 多余的join log l2，登出事件直接从原表读取即可
• 缺少计数标记（+1/-1），无法完成累计计算

特殊情况说明 #

如果有多个时间点达到相同峰值，上述SQL会返回所有峰值时间；若只需返回任意一个，可将RANK()替换为ROW_NUMBER()。

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