如何使用SQL自连接查询统计网站并发访客数量

基础说明 #

• 目标数据表：visitor，包含字段如下
  • user_id：访客唯一标识
  • login_time：访客登录时间
  • logout_time：访客登出时间
• 统计目标：查询返回所有原有字段，新增一列展示每条记录对应login_time时刻的在线并发访客总数
• 实现方式：采用要求的self join（自连接）方案

判定逻辑 #

针对任意一条访客记录A，其登录时刻的并发访客包含所有满足以下条件的访客B：B的登录时间不晚于A的登录时间，且B的登出时间晚于A的登录时间（即B的在线时段完全覆盖A的登录时间点）。

实现SQL #

SELECT
    a.user_id,
    a.login_time,
    a.logout_time,
    COUNT(b.user_id) AS concurrent_visitor_num
FROM visitor a
LEFT JOIN visitor b
  ON b.login_time <= a.login_time
  AND b.logout_time > a.login_time
GROUP BY a.user_id, a.login_time, a.logout_time;

适配提示 #

若业务规则定义登出瞬间用户仍算作在线，只需将连接条件中b.logout_time > a.login_time修改为b.logout_time >= a.login_time即可。

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