SQL面试题3：累计汇总类、直播间同时在线问题

embedded/2025/1/30 6:30:46/

引言

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

场景介绍：

（一）直播行业蓬勃发展现状

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

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

题目描述

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

  • room_id：这是每个直播间独一无二的编号，通过它可以区分不同的直播间。
  • user_id：每个用户在平台上也都有自己专属的编号，方便我们识别不同的用户。
  • login_in：记录了用户进入直播间的具体时间，精确到时分秒，比如 “2025-05-10 19:30:00” 。
  • login_out：记录的是用户离开直播间的时间，同样精确到时分秒。
    根据上述信息，现在需要完成以下三个任务：
  1. 统计有史以来，直播间同时在线人数最大值。
  2. 统计某个时间段内的同时在线人数最大值。
  3. 统计每个小时内直播间同时在线人数最大值。
    • 该问题与问题一的区别在于，问题一考虑的是某一时刻直播间的最大值，而问题三考虑的是小时内，如果用户12:01登出，则算其在12时登陆过，用于进行直播时段分析。

数据准备与代码实现

数据准备

sql">CREATE TABLE user_log_tb (room_id INT,user_id INT,login_in TIMESTAMP,login_out TIMESTAMP
);INSERT INTO user_log_tb VALUES
-- 直播间 1 的用户数据
(1, 1, '2025-05-10 00:00:00', '2025-05-10 01:00:00'),
(1, 2, '2025-05-10 00:10:00', '2025-05-10 02:10:00'),
(1, 3, '2025-05-10 01:30:00', '2025-05-10 02:30:00'),
(1, 4, '2025-05-10 02:00:00', '2025-05-10 04:00:00'),
(1, 5, '2025-05-10 02:30:00', '2025-05-10 03:30:00'),
(1, 6, '2025-05-10 03:01:00', '2025-05-10 04:05:00'),
(1, 7, '2025-05-10 03:30:00', '2025-05-10 04:30:00'),
(1, 8, '2025-05-10 04:00:00', '2025-05-10 05:00:00');
INSERT INTO user_log_tb VALUES
(1, 9, '2025-05-09 23:30:00', '2025-05-10 01:30:00'),
(1, 10, '2025-05-09 23:45:00', '2025-05-10 05:30:00');
-- 直播间 2 的用户数据
INSERT INTO user_log_tb VALUES
(2, 11, '2025-05-10 00:30:00', '2025-05-10 01:30:00'),
(2, 12, '2025-05-10 01:00:00', '2025-05-10 02:00:00'),
(2, 13, '2025-05-10 00:30:00', '2025-05-10 03:00:00'),
(2, 14, '2025-05-10 02:10:00', '2025-05-10 05:30:00'),
(2, 15, '2025-05-10 02:30:00', '2025-05-10 04:00:00');

在这里插入图片描述

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

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

  1. 数据拆分并打标，将一条数据拆分成登录和登出两条数据，登录为1，登出为-1，利用union all将登录时间和登出时间的数据合并到一个结果集中
  2. 利用窗口函数sum() over()对room_id进行分区，按时间排序并利用累加器进行累加。
  3. 使用窗口函数 max(cnt) over () 计算 cnt 的最大值。
sql">WITH data1 AS (SELECT room_id, user_id, date_format(login_in,'yyyy-MM-dd HH:mm:ss') login_time, 1 flag FROM user_log_tbUNION ALLSELECT room_id, user_id, date_format(login_out,'yyyy-MM-dd HH:mm:ss') login_time, -1 flag FROM user_log_tb
),
data2 AS (SELECT room_id, login_time, flag, SUM(flag) OVER (PARTITION BY room_id ORDER BY login_time) cnt FROM data1
),
data3 AS (SELECT room_id, login_time,  flag, cnt,  MAX(cnt) OVER (PARTITION BY room_id) max_cnt FROM data2
)
SELECT distinct room_id, max_cnt FROM data3;

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

2.统计某个时间段内的同时在线人数最大值

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

  1. 数据拆分并打标，将一条数据拆分成登录和登出两条数据，登录为1，登出为-1，利用union all将登录时间和登出时间的数据合并到一个结果集中
  2. 利用窗口函数sum() over()对room_id进行分区，按时间排序并利用累加器进行累加。
  3. 使用窗口函数lead()获取当前行之后的第一行的login_time作为结束时间。如果没有更多的记录（即这是最后一条记录），则默认返回当前的login_time。
    • lead ( login_time, 1, login_time )：第三个参数，是默认值。如果当前行后面第 1 行不存在（例如在分区的最后一行时），则使用这个默认值。
  4. 根据条件where cnt = max_cnt用来筛选出那些在线人数达到最大值的时间点。
sql">with data1 as (select room_id,user_id,date_format(login_in,'yyyy-MM-dd HH:mm:ss') login_time,1 flag from user_log_tbunion allselect room_id,user_id,date_format(login_out,'yyyy-MM-dd HH:mm:ss') login_time,-1 flag from user_log_tb
),data2 as (select room_id,login_time,flag,sum(flag) over (partition by room_id order by login_time) cnt from data1),data3 as (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)
select room_id,max_cnt,login_time as start_time,lead_dt as end_time from data3 where cnt = max_cnt;
3. 统计每个小时内直播间同时在线人数最大值

步骤与思路：

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

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

sql">with data1 as (select room_id,user_id,date(login_in) as login_date,hour(login_in) as min_time,hour(login_out)  as max_time,split(space(hour(login_out) - hour(login_in)), '') as disfrom user_log_tbwhere date(login_in) = '2025-05-10'),data2 as (select room_id, user_id,login_date, min_time + dis_index as on_timefrom data1 lateral view posexplode(dis) n as dis_index, dis_data),data3 as (select room_id,login_date, on_time, count(distinct user_id) as on_time_cntfrom data2group by room_id,login_date, on_time)
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]
sql">WITH data1 AS (SELECTroom_id, user_id, DATE(login_in) AS login_date, HOUR(login_in) AS min_time, HOUR(login_out) AS max_time,-- 创建一个从登录到登出前一个小时的序列sequence(HOUR(login_in), HOUR(login_out) - 1, 1) AS hours FROM user_log_tbWHERE DATE(login_in) = '2025-05-10'
),
data2 AS (SELECT room_id, user_id, login_date,-- 因为sequence函数不包括结束值，所以我们在加1以包含登出的那个小时hour + 1 AS on_time FROM data1 LATERAL VIEW EXPLODE(hours) exploded_table AS hour
),
data3 AS (SELECT room_id, login_date, on_time, COUNT(DISTINCT user_id) AS on_time_cntFROM data2GROUP BY room_id, login_date, on_time
)
SELECT * FROM data3;
优化思考：如果用户登录时间与登出时间跨天该怎么处理
sql">-- 定义 data1 公共表表达式，处理原始数据并生成跨天的小时序列
WITH data1 AS (SELECTroom_id,user_id,login_in,login_out,-- 计算登录和登出时间的总秒数差UNIX_TIMESTAMP(login_out) - UNIX_TIMESTAMP(login_in) AS total_secondsFROMuser_log_tb
),
-- 定义 data2 公共表表达式，生成每小时的时间戳序列
data2 AS (SELECTroom_id,user_id,-- 使用 sequence 函数生成从登录时间到登出时间每小时的时间戳序列EXPLODE(sequence(CAST(login_in AS TIMESTAMP),CAST(login_out AS TIMESTAMP),INTERVAL 1 HOUR)) AS on_timeFROMdata1
),
-- 定义 data3 公共表表达式，统计每个房间、日期和小时的在线用户数
data3 AS (SELECTroom_id,DATE(on_time) AS login_date,HOUR(on_time) AS on_time,COUNT(DISTINCT user_id) AS on_time_cntFROMdata2GROUP BYroom_id,DATE(on_time),HOUR(on_time)
)
-- 从 data3 中选择所需的列并展示结果
SELECT*
FROMdata3;

示例数据：

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

预期结果：
在这里插入图片描述


http://www.ppmy.cn/embedded/157720.html

相关文章

MongoDB使用详解

MongoDB使用详解

文章目录 MongoDB使用详解一、引言二、MongoDB的安装与配置1、安装MongoDB2、配置MongoDB 三、MongoDB的基本操作1、插入数据2、查询数据3、更新数据4、删除数据 四、使用示例1、聚合操作2、索引优化3、备份与恢复 五、总结 MongoDB使用详解 一、引言 MongoDB是一款高性能、开…
阅读更多...
全志 视频输入组件的使用

全志 视频输入组件的使用

1.启动MPP和Glog库 示例代码： log_init(argv[0], &stGLogConfig);MPP_SYS_CONF_S stSysConf; memset(&stSysConf, 0, sizeof(MPP_SYS_CONF_S)); stSysConf.nAlignWidth 32; AW_MPI_SYS_SetConf(&stSysConf); ret AW_MPI_SYS_Init();2.获取配置文件信…
阅读更多...
MFC开发，给对话框添加垂直滚动条并解决鼠标滚动响应的问题

MFC开发，给对话框添加垂直滚动条并解决鼠标滚动响应的问题

无论在使用QT或者MFC进行界面开发时，都会出现在一个对话框里面存在好多的选项，导致对话框变得非常长或者非常大，就会显现的不美观，在这种情况下通常是添加一个页面的滚动条来解决这个问题，下面我们就来介绍给MFC的对话…
阅读更多...
蓝桥杯python语言基础（4）——基础数据结构（上）

蓝桥杯python语言基础（4）——基础数据结构（上）

目录 一、列表与元组 （一）列表 （二）操作列表 （三）元组 习题P502 习题P497 二、字符串 （一）字符串的基本操作 （二）字符串的常用方法 （三&…
阅读更多...
从0到1：C++ 开启游戏开发奇幻之旅（一）

从0到1：C++ 开启游戏开发奇幻之旅（一）

目录 为什么选择 C 进行游戏开发 性能卓越 内存管理精细 跨平台兼容性强 搭建 C 游戏开发环境 集成开发环境（IDE） Visual Studio CLion 图形库 SDL（Simple DirectMedia Layer） SFML（Simple and Fast Multim…
阅读更多...
知网爬虫，作者、摘要、题目、发表期刊等主要内容的获取

知网爬虫，作者、摘要、题目、发表期刊等主要内容的获取

爬取知网内容的详细过程 爬取知网内容需要考虑多个因素，包括网站的结构、反爬虫机制等。以下是一个详细的步骤和代码实现，帮助你使用Python爬取知网上的论文信息，包括作者、摘要、题目、发表期刊等主要内容。 1. 数据准备 首先，需要准备一些基础数据，如知网的URL、请求…
阅读更多...
基于fpga技术的脉冲信号源设计（论文+源码）

基于fpga技术的脉冲信号源设计（论文+源码）

1系统总体设计 本次课题为基于FPGA的质谱脉冲信号源.，主要结合FPGA技术进行设计，整个系统的框图如图3.1所示，其包括了EP4CE6E22 FPGA控制器，按键，LCDLCD液晶显示，AD9708数模转换电路等器件构成系统的硬件基…
阅读更多...
TypeScript 学习 -类型 - 9

TypeScript 学习 -类型 - 9

声明合并 成员变量合并：成员变量会合并，但类型必须一致。成员函数合并：如果函数签名不同，合并后的函数会是签名的联合类型。接口声明顺序：在同一个接口内按顺序合并；不同接口时，后声明的会覆盖…
阅读更多...
最新文章

Copyright @ 2022~2023