使用 SQL 分析房间用户状态变化
本文将详细介绍如何使用 SQL 的窗口函数和聚合函数，分析用户在房间中的状态变化，目标是计算每个房间指定时间段内的最大用户状态。
示例数据
假设我们的数据表包含以下字段：
| room_id | user_id | login_time | logout_time |
|---|---|---|---|
| 101 | 1 | 2023-03-10 12:05:00 | 2023-03-10 12:45:00 |
| 101 | 2 | 2023-03-10 12:10:00 | 2023-03-10 12:50:00 |
| 102 | 3 | 2023-03-10 12:00:00 | 2023-03-10 13:00:00 |
| 102 | 4 | 2023-03-10 12:30:00 | 2023-03-10 13:30:00 |
第一步：提取事件并标记类型
在这一步，我们将从原始数据中提取每个用户的登录和退出事件，并为每个事件分配一个类型标记（1 表示登录，-1 表示退出）。
提取登录事件
sql">SELECT room_id,user_id,UNIX_TIMESTAMP(login_time) AS event_time,1 AS user_type
FROM table
WHERE FROM_UNIXTIME(UNIX_TIMESTAMP(login_time), '%Y%m%d') = '20230310'AND (HOUR(login_time) BETWEEN 12 AND 13)
结果示例
| room_id | user_id | event_time | user_type |
|---|---|---|---|
| 101 | 1 | 1615375500 | 1 |
| 101 | 2 | 1615375800 | 1 |
| 102 | 3 | 1615375200 | 1 |
| 102 | 4 | 1615377000 | 1 |
提取退出事件
sql">SELECT room_id,user_id,UNIX_TIMESTAMP(logout_time) AS event_time,-1 AS user_type
FROM table
WHERE FROM_UNIXTIME(UNIX_TIMESTAMP(logout_time), '%Y%m%d') = '20230310'AND (HOUR(logout_time) BETWEEN 12 AND 13)
结果示例
| room_id | user_id | event_time | user_type |
|---|---|---|---|
| 101 | 1 | 1615377900 | -1 |
| 101 | 2 | 1615378200 | -1 |
| 102 | 3 | 1615378800 | -1 |
| 102 | 4 | 1615379400 | -1 |
合并结果
我们将登录和退出事件通过 UNION ALL 合并，以获得完整的事件列表。
sql">SELECT room_id,user_id,event_time,user_type
FROM (-- 登录事件SELECT room_id,user_id,UNIX_TIMESTAMP(login_time) AS event_time,1 AS user_typeFROM tableWHERE FROM_UNIXTIME(UNIX_TIMESTAMP(login_time), '%Y%m%d') = '20230310'AND (HOUR(login_time) BETWEEN 12 AND 13)UNION ALL-- 退出事件SELECT room_id,user_id,UNIX_TIMESTAMP(logout_time) AS event_time,-1 AS user_typeFROM tableWHERE FROM_UNIXTIME(UNIX_TIMESTAMP(logout_time), '%Y%m%d') = '20230310'AND (HOUR(logout_time) BETWEEN 12 AND 13)
) AS events
合并结果示例
| room_id | user_id | event_time | user_type |
|---|---|---|---|
| 101 | 1 | 1615375500 | 1 |
| 101 | 2 | 1615375800 | 1 |
| 102 | 3 | 1615375200 | 1 |
| 102 | 4 | 1615377000 | 1 |
| 101 | 1 | 1615377900 | -1 |
| 101 | 2 | 1615378200 | -1 |
| 102 | 3 | 1615378800 | -1 |
| 102 | 4 | 1615379400 | -1 |
第二步：计算累积用户状态
使用窗口函数累积计算用户状态。
sql">SELECTroom_id,user_id,user_type,SUM(user_type) OVER (PARTITION BY room_id ORDER BY event_time) AS status
FROM (-- 上一步的查询
) AS events
结果示例
| room_id | user_id | user_type | status |
|---|---|---|---|
| 101 | 1 | 1 | 1 |
| 101 | 2 | 1 | 2 |
| 101 | 1 | -1 | 1 |
| 101 | 2 | -1 | 0 |
| 102 | 3 | 1 | 1 |
| 102 | 4 | 1 | 2 |
| 102 | 3 | -1 | 1 |
| 102 | 4 | -1 | 0 |
第三步：求最大状态
通过聚合函数求每个房间的最大状态。
sql">SELECTroom_id,MAX(status) AS max_status
FROM (-- 上一步的状态查询
) AS status_calculation
GROUP BYroom_id;
结果示例
| room_id | max_status |
|---|---|
| 101 | 2 |
| 102 | 2 |
解析
- 数据准备：合并登录和退出事件，标记事件类型。
- 计算状态：使用
SUM() OVER计算累积状态。 - 求最大状态：通过
MAX(status)获取最大用户状态。
