ToB企服应用市场:ToB评测及商务社交产业平台

标题: SQL面试题3：累计汇总类、直播间同时在线问题 [打印本页]

---

作者: 羊蹓狼    时间: 2025-1-25 18:35
标题: SQL面试题3：累计汇总类、直播间同时在线问题
引言

       随着互联网技术的飞速发展，直播行业已经成为人们日常生活中不可或缺的一部门。从游戏直播到电商带货，从教育讲座到明星互动，直播间为用户提供了一个即时交换和娱乐的新平台。主播们通过屏幕分享自己的知识、技能或是生活点滴，而观众则可以通过弹幕、礼物等方式到场其中，形成了一种全新的社交模式。本文将借助 SQL 语言，深入探究如何计算差别场景下直播间在线人数最大值，助力从业者更好地进行直播数据分析与运营决议。
场景先容：

（一）直播行业发达发展近况

       当下，直播行业出现出发作式增长。各大直播平台不断涌现，主播们各展神通，从专业电竞选手直播出色赛事，到平凡博主分享日常生活点滴，再到明星大咖进行公益直播或产品推广，直播内容丰富多样。据统计，天天都有数以百万计的用户活泼在直播间，观看时长累计达到数亿小时。这不仅改变了人们的娱乐和消费方式，也为企业带来了新的营销渠道，为创作者提供了广阔的发展空间。每一个直播间背后都隐藏着巨大的数据海洋。这些数据不仅记录了观众的举动轨迹，也反映了直播内容的受欢迎程度。对于运营者来说，如何解读并利用这些数据进行决议变得尤为关键，对于平台和主播而言，直播间的在线人数是衡量直播结果的关键指标之一。
（二）直播间最大在线人数的紧张性

• 评估直播结果：最大在线人数直观地展示了直播间在特定时间内吸引和留住观众的能力。一场直播若能达到较高的最大在线人数，阐明其内容、主播表现、互动环节等方面具有强大的吸引力，能有用聚集人气。
  
• 优化直播策略：通太过析差别直播主题、时段、主播风格下的最大在线人数，运营者可以精准调整直播策略。比方，发现某个时间段或某种范例的直播更容易吸引大量观众，便可安排更多类似直播，提升团体平台的活泼度。
  
• 资源分配依据：对于平台来说，最大在线人数是衡量直播间价值的紧张标准。平台可以根据这一数据，合理分配推荐位、流量扶持等资源，将更多曝光机会给予人气高的直播间，实现资源的高效利用。
  

题目描述

现有一份用户直播间观看记录的数据表。该表记录了用户在直播间的各种举动信息，详细有以下几个字段：

• room_id：这是每个直播间独一无二的编号，通过它可以区分差别的直播间。
  
• user_id：每个用户在平台上也都有自己专属的编号，方便我们识别差别的用户。
  
• login_in：记录了用户进入直播间的详细时间，精确到时分秒，好比 “2025-05-10 19:30:00” 。
  
• login_out：记录的是用户离开直播间的时间，同样精确到时分秒。
  根据上述信息，现在需要完成以下三个使命：
  

• 统计有史以来，直播间同时在线人数最大值。
  
• 统计某个时间段内的同时在线人数最大值。
  
• 统计每个小时内直播间同时在线人数最大值。
  
  
  
  • 该问题与问题一的区别在于，问题一考虑的是某一时刻直播间的最大值，而问题三考虑的是小时内，如果用户12:01登出，则算其在12时登陆过，用于进行直播时段分析。
    
  
  

数据准备与代码实现

数据准备

1. CREATE TABLE user_log_tb (
2.     room_id INT,
3.     user_id INT,
4.     login_in TIMESTAMP,
5.     login_out TIMESTAMP
6. );
8. INSERT INTO user_log_tb VALUES
9. -- 直播间 1 的用户数据
10. (1, 1, '2025-05-10 00:00:00', '2025-05-10 01:00:00'),
11. (1, 2, '2025-05-10 00:10:00', '2025-05-10 02:10:00'),
12. (1, 3, '2025-05-10 01:30:00', '2025-05-10 02:30:00'),
13. (1, 4, '2025-05-10 02:00:00', '2025-05-10 04:00:00'),
14. (1, 5, '2025-05-10 02:30:00', '2025-05-10 03:30:00'),
15. (1, 6, '2025-05-10 03:01:00', '2025-05-10 04:05:00'),
16. (1, 7, '2025-05-10 03:30:00', '2025-05-10 04:30:00'),
17. (1, 8, '2025-05-10 04:00:00', '2025-05-10 05:00:00');
18. INSERT INTO user_log_tb VALUES
19. (1, 9, '2025-05-09 23:30:00', '2025-05-10 01:30:00'),
20. (1, 10, '2025-05-09 23:45:00', '2025-05-10 05:30:00');
21. -- 直播间 2 的用户数据
22. INSERT INTO user_log_tb VALUES
23. (2, 11, '2025-05-10 00:30:00', '2025-05-10 01:30:00'),
24. (2, 12, '2025-05-10 01:00:00', '2025-05-10 02:00:00'),
25. (2, 13, '2025-05-10 00:30:00', '2025-05-10 03:00:00'),
26. (2, 14, '2025-05-10 02:10:00', '2025-05-10 05:30:00'),
27. (2, 15, '2025-05-10 02:30:00', '2025-05-10 04:00:00');

复制代码

1. 单个直播间最大在线人数

步调与思路：该实现只利用前八条数据。

• 数据拆分并打标，将一条数据拆分成登录和登出两条数据，登录为1，登出为-1，利用union all将登录时间和登出时间的数据归并到一个结果集中
  
• 利用窗口函数sum() over()对room_id进行分区，按时间排序并利用累加器进行累加。
  
• 利用窗口函数 max(cnt) over () 计算 cnt 的最大值。
  

1. WITH data1 AS (
2.     SELECT room_id, user_id, date_format(login_in,'yyyy-MM-dd HH:mm:ss') login_time, 1 flag FROM user_log_tb
3.     UNION ALL
4.     SELECT room_id, user_id, date_format(login_out,'yyyy-MM-dd HH:mm:ss') login_time, -1 flag FROM user_log_tb
5. ),
6. data2 AS (
7.     SELECT room_id, login_time, flag, SUM(flag) OVER (PARTITION BY room_id ORDER BY login_time) cnt FROM data1
8. ),
9. data3 AS (
10.     SELECT room_id, login_time,  flag, cnt,  MAX(cnt) OVER (PARTITION BY room_id) max_cnt FROM data2
11. )
12. SELECT distinct room_id, max_cnt FROM data3;

复制代码

结果：发现最大的为3，与上述图最大的为4不划一。原因是未充分考虑用户登录和登出的交叉情况，可能导致用户的登出利用先于后续用户的登录利用，从而影响了最大在线人数的准确计算。本代码是根据登录登出时间按用户排序，用户4先进行登出，所以没加入到这个总和里面。
2.统计某个时间段内的同时在线人数最大值

步调与思路：该实现只利用前八条数据。

• 数据拆分并打标，将一条数据拆分成登录和登出两条数据，登录为1，登出为-1，利用union all将登录时间和登出时间的数据归并到一个结果集中
  
• 利用窗口函数sum() over()对room_id进行分区，按时间排序并利用累加器进行累加。
  
• 利用窗口函数lead()获取当前行之后的第一行的login_time作为结束时间。如果没有更多的记录（即这是最后一条记录），则默认返回当前的login_time。
  
  
  
  • lead ( login_time, 1, login_time )：第三个参数，是默认值。如果当前行后面第 1 行不存在（比方在分区的最后一行时），则利用这个默认值。
    
  
  
• 根据条件where cnt = max_cnt用来筛选出那些在线人数达到最大值的时间点。
  

1. with data1 as (
2.     select room_id,user_id,date_format(login_in,'yyyy-MM-dd HH:mm:ss') login_time,1 flag from user_log_tb
3.     union all
4.     select room_id,user_id,date_format(login_out,'yyyy-MM-dd HH:mm:ss') login_time,-1 flag from user_log_tb
5. ),
6.     data2 as (
7.         select room_id,login_time,flag,sum(flag) over (partition by room_id order by login_time) cnt from data1
8.     ),
9.     data3 as (
10.         select room_id,login_time,flag,cnt,max(cnt) over (partition by room_id) max_cnt,lead ( login_time, 1, login_time ) OVER ( ORDER BY login_time ) lead_dt  from data2
11.     )
12. select room_id,max_cnt,login_time as start_time,lead_dt as end_time from data3 where cnt = max_cnt;

复制代码

3. 统计每个小时内直播间同时在线人数最大值

步调与思路：

1. -- 以一条数据为例
2. (1, 2, '2025-05-10 00:10:00', '2025-05-10 02:10:00'),
3. -- 取登录登出的hour作为min_time和max_time
4. 1,2,0,2
5. -- 利用space函数生成长度为max_time-min_time=2的空格字符串，再利用split进行拆分
6. 1,2,0,2,['','','']
7. -- 利用posexplode炸裂函数将一个数组拆分成多行，并拆分成多个时间
8. 1,2,0+0=0
9. 1,2,0+1=1
10. 1,2,0+2=2
11. -- 最后统计分时段的在线人数

复制代码

本代码对date(login_in) = '2025-05-10')进行分析

1. with data1 as (select room_id,
2.                       user_id,
3.                       date(login_in) as login_date,
4.                       hour(login_in) as min_time,
5.                       hour(login_out)  as max_time,
6.                       split(space(hour(login_out) - hour(login_in)), '') as dis
7.                from user_log_tb
8.                where date(login_in) = '2025-05-10'),
9.      data2 as (select room_id, user_id,login_date, min_time + dis_index as on_time
10.                from data1 lateral view posexplode(dis) n as dis_index, dis_data),
11.      data3 as (select room_id,login_date, on_time, count(distinct user_id) as on_time_cnt
12.                from data2
13.                group by room_id,login_date, on_time)
14. select * from data3;

复制代码

利用sequenc函数对问题3进行优化

• Hive 中的 sequence 函数（Hive 版本从 2.2.0 版本开始支持），其作用是生成一个从 HOUR(login_in) 开始，到 HOUR(login_out) - 1 结束，步长为 1 的整数序列，并将这个序列命名为 hours。
  
• 示例数据：('2025-05-10 08:00:00', '2025-05-10 12:00:00')
  
• 结果：[8, 9, 10, 11]
  

1. WITH data1 AS (
2.     SELECT
3.         room_id,
4.         user_id,
5.         DATE(login_in) AS login_date,
6.         HOUR(login_in) AS min_time,
7.         HOUR(login_out) AS max_time,
8.         -- 创建一个从登录到登出前一个小时的序列
9.         sequence(HOUR(login_in), HOUR(login_out) - 1, 1) AS hours
10.     FROM user_log_tb
11.     WHERE DATE(login_in) = '2025-05-10'
12. ),
13. data2 AS (
14.     SELECT room_id, user_id, login_date,
15.         -- 因为sequence函数不包括结束值，所以我们在加1以包含登出的那个小时
16.         hour + 1 AS on_time
17.     FROM data1 LATERAL VIEW EXPLODE(hours) exploded_table AS hour
18. ),
19. data3 AS (
20.     SELECT room_id, login_date, on_time, COUNT(DISTINCT user_id) AS on_time_cnt
21.     FROM data2
22.     GROUP BY room_id, login_date, on_time
23. )
24. SELECT * FROM data3;

复制代码

优化思考：如果用户登录时间与登出时间跨天该怎么处理

1. -- 定义 data1 公共表表达式，处理原始数据并生成跨天的小时序列
2. WITH data1 AS (
3.     SELECT
4.         room_id,
5.         user_id,
6.         login_in,
7.         login_out,
8.         -- 计算登录和登出时间的总秒数差
9.         UNIX_TIMESTAMP(login_out) - UNIX_TIMESTAMP(login_in) AS total_seconds
10.     FROM
11.         user_log_tb
12. ),
13. -- 定义 data2 公共表表达式，生成每小时的时间戳序列
14. data2 AS (
15.     SELECT
16.         room_id,
17.         user_id,
18.         -- 使用 sequence 函数生成从登录时间到登出时间每小时的时间戳序列
19.         EXPLODE(sequence(
20.             CAST(login_in AS TIMESTAMP),
21.             CAST(login_out AS TIMESTAMP),
22.             INTERVAL 1 HOUR
23.         )) AS on_time
24.     FROM
25.         data1
26. ),
27. -- 定义 data3 公共表表达式，统计每个房间、日期和小时的在线用户数
28. data3 AS (
29.     SELECT
30.         room_id,
31.         DATE(on_time) AS login_date,
32.         HOUR(on_time) AS on_time,
33.         COUNT(DISTINCT user_id) AS on_time_cnt
34.     FROM
35.         data2
36.     GROUP BY
37.         room_id,
38.         DATE(on_time),
39.         HOUR(on_time)
40. )
41. -- 从 data3 中选择所需的列并展示结果
42. SELECT
43.     *
44. FROM
45.     data3;

复制代码

示例数据：

1. -- 创建 user_log_tb 表
2. CREATE TABLE user_log_tb (
3.     room_id INT,
4.     user_id INT,
5.     login_in TIMESTAMP,
6.     login_out TIMESTAMP
7. );
9. -- 插入示例数据，包含跨天的登录登出记录
10. INSERT INTO user_log_tb VALUES
11. (1, 1, '2025-05-10 23:00:00', '2025-05-11 02:00:00'),
12. (1, 2, '2025-05-10 22:00:00', '2025-05-11 01:00:00');

复制代码

预期结果：

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

---

欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/)

Powered by Discuz! X3.4