对比 Pandas 和 MySQL 统计直播间各时间段上线人数

数据开发

源数据表结构：
room_id(直播间ID)、user_id(上麦用户ID)、start_time(用户上麦开始时间)、end_time(用户上麦结束时间)
该表记录的是每个用户在每个直播间每次上下麦的时间段

目标数据表结构：
room_id(直播间1D)、start_time(用户上麦开始时间)、end_time(用户上麦结束时间)、user_cnt(该时间段内上麦人数）
该表需要记录每个直播间每次人员变动的情况

例子

源数据：

room_id	user_id	start_time	end_time
a	user1	2022-02-01 10:12:13	2022-02-01 10:30:23
a	user2	2022-02-01 10-21:10	2022-02-01 11:02:06

目标数据：

room_id	start_time	end_time	user_cnt
a	2022-02-01 10:12:13	2022-02-01 10:21:10	1
a	2022-02-01 10-21:10	2022-02-01 10:30:23	2
a	2022-02-01 10:30:23	2022-02-01 11:02:06	1

有了上图，我们应该就秒懂了这题的解题思路，先将所有的时间点排序，按顺序分配得到每个时间分区，绿色为进入起始时间点，红色为结束时间点，我们进入起始点时+1，进入结束时间点时-1，这样就可以得到每个区间的在线人数了。当然假如user3也从10:21:10
进去，则这个时间点位置+2，后续的时间片段内在线人数就是累加后的值。

理解了思路，我们就可以开始编码了：

pandas处理代码

最终完整处理代码为：

import pandas as pd

df = pd.DataFrame([
    ["a", "user1", "2022-02-01 10:12:13", "2022-02-01 10:30:23"],
    ["a", "user2", "2022-02-01 10:21:10", "2022-02-01 11:02:06"],
    ["b", "user1", "2022-02-01 10:12:13", "2022-02-01 10:30:23"],
    ["b", "user2", "2022-02-01 10:21:10", "2022-02-01 11:02:06"],
    ["b", "user3", "2022-02-01 10:19:10", "2022-02-01 11:05:06"],
], columns=["room_id", "user_id", "start_time", "end_time"])
df


def func(df):
    times = pd.concat([df.start_time.value_counts(),
                       -df.end_time.value_counts()]).sort_index()
    times.index.name = "start_time"
    r = times.cumsum().to_frame("user_cnt").reset_index()
    r.insert(1, "end_time", r.start_time.shift(-1))
    r.insert(0, "room_id", df.room_id.iat[0])
    return r.query("user_cnt>0")


df.groupby("room_id", as_index=False).apply(func).reset_index(drop=True)

结果：

注意：使用如下代码即可设置一个单元格可以显示全部输出（）

from IPython.core.interactiveshell import InteractiveShell
InteractiveShell.ast_node_interactivity = "all"

默认是输出结尾表达式：

from IPython.core.interactiveshell import InteractiveShell
print(InteractiveShell.ast_node_interactivity.default_value)
print(InteractiveShell.ast_node_interactivity.values)

last_expr
['all', 'last', 'last_expr', 'none', 
 'last_expr_or_assign']

MySQL处理代码

首先我们创建表并插入数据：

CREATE TABLE `t1` (
  `room_id` VARCHAR(20),
  `user_id` VARCHAR(20),
  `start_time` DATETIME,
  `end_time` DATETIME
);

INSERT  INTO `t1`(`room_id`,`user_id`,`start_time`,`end_time`) VALUES ('a','user1','2022-02-01 10:12:13','2022-02-01 10:30:23');
INSERT  INTO `t1`(`room_id`,`user_id`,`start_time`,`end_time`) VALUES ('a','user2','2022-02-01 10:21:10','2022-02-01 11:02:06');
INSERT  INTO `t1`(`room_id`,`user_id`,`start_time`,`end_time`) VALUES ('b','user1','2022-02-01 10:12:13','2022-02-01 10:30:23');
INSERT  INTO `t1`(`room_id`,`user_id`,`start_time`,`end_time`) VALUES ('b','user2','2022-02-01 10:21:10','2022-02-01 11:02:06');
INSERT  INTO `t1`(`room_id`,`user_id`,`start_time`,`end_time`) VALUES ('b','user3','2022-02-01 10:19:10','2022-02-01 11:05:06');

然后编写SQL：

SELECT
 room_id, start_time, end_time, user_cnt
FROM(
 SELECT
  room_id, start_time,
  lead(start_time) over(PARTITION BY room_id ORDER BY start_time) end_time,
  SUM(i) over(PARTITION BY room_id ORDER BY start_time) user_cnt
 FROM(
  SELECT room_id, start_time, COUNT(1) AS i
  FROM t1 GROUP BY room_id, start_time
  UNION ALL 
  SELECT room_id, end_time, -COUNT(1) AS i
  FROM t1 GROUP BY room_id, end_time
 ) a
) b
WHERE user_cnt>0;

🏴‍☠️宝藏级🏴‍☠️ 原创公众号『数据STUDIO』内容超级硬核。公众号以Python为核心语言，垂直于数据科学领域，包括可戳👉 Python｜MySQL｜数据分析｜数据可视化｜机器学习与数据挖掘｜爬虫 等，从入门到进阶！

长按👇关注- 数据STUDIO -设为星标，干货速递