如何通过单条SQL统计各小时的活跃用户会话数量

需求拆解 #

你之前的SQL统计的是每小时新发起登录的会话数量，现在需要达成的目标是：以每个小时的整点时刻为时间槽代表值foo，只要满足login_time <= foo AND logout_time >= foo的会话，就算这个时间槽的活跃会话，最终输出按时间排序的每小时活跃数供可视化。 #

容易出错的实现误区 #

如果直接基于user_session表的现有时间字段做分组统计，会出现两个典型漏数问题：

• 某小时没有新的登录请求，但之前发起的跨小时会话仍处于活跃状态，这类时间槽会完全缺失统计数据
• 跨多个小时的长会话，只会在其登录的那个小时被计数，没法在覆盖的每个小时槽都被统计为活跃

可直接运行的单条SQL（适配你之前使用的PostgreSQL语法） #

WITH all_hour_slots AS (
  -- 生成整个统计周期内的所有小时整点时间槽，避免漏数
  SELECT date_trunc('hour', generate_series(
    (SELECT min(login_time) FROM user_session),
    (SELECT max(logout_time) FROM user_session),
    interval '1 hour'
  )) AS foo
)
SELECT
  ahs.foo AS "time",
  count(us.session_id) AS active_session_count
FROM all_hour_slots ahs
LEFT JOIN user_session us
  -- 严格按题目给定的活跃规则做关联
  ON us.login_time <= ahs.foo
  AND us.logout_time >= ahs.foo
GROUP BY ahs.foo
ORDER BY ahs.foo;

实用补充说明 #

• 关联逻辑严格遵循题目给定的活跃判定规则：会话的登录时间不晚于当前小时槽的代表值、且登出时间不早于当前小时槽的代表值，即判定为该时段的活跃会话
• 最终返回的time字段可直接作为图表X轴，active_session_count作为Y轴绘制趋势图
• 统计时使用会话表主键（如session_id）做计数而非count(*)，可以保证没有匹配会话的时间槽计数为0，不会出现计数虚高的问题
• 如果业务里存在用户未正常登出、logout_time为空的情况，可以把关联条件调整为COALESCE(us.logout_time, now()) >= ahs.foo，把未登出的会话默认计为到当前时间点为止的活跃状态
• 如果统计范围有指定的起止时间，可以直接替换generate_series里的上下界，不用从表里取最大最小值，查询效率会更高

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