当前位置： 首页 > article >正文

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

article 2025/1/25 5:05:51

引言

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

场景介绍：

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

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

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

题目描述

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

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

数据准备与代码实现

数据准备

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 的最大值。
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_tb
    UNION ALL
    SELECT 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用来筛选出那些在线人数达到最大值的时间点。
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_tb
    union all
    select 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. 统计每个小时内直播间同时在线人数最大值

步骤与思路：

-- 以一条数据为例
(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')进行分析

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 dis
               from user_log_tb
               where date(login_in) = '2025-05-10'),
     data2 as (select room_id, user_id,login_date, min_time + dis_index as on_time
               from 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_cnt
               from data2
               group 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]
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,
        -- 创建一个从登录到登出前一个小时的序列
        sequence(HOUR(login_in), HOUR(login_out) - 1, 1) AS hours 
    FROM user_log_tb
    WHERE 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_cnt
    FROM data2
    GROUP BY room_id, login_date, on_time
)
SELECT * FROM data3;
优化思考：如果用户登录时间与登出时间跨天该怎么处理
-- 定义 data1 公共表表达式，处理原始数据并生成跨天的小时序列
WITH data1 AS (
    SELECT
        room_id,
        user_id,
        login_in,
        login_out,
        -- 计算登录和登出时间的总秒数差
        UNIX_TIMESTAMP(login_out) - UNIX_TIMESTAMP(login_in) AS total_seconds
    FROM
        user_log_tb
),
-- 定义 data2 公共表表达式，生成每小时的时间戳序列
data2 AS (
    SELECT
        room_id,
        user_id,
        -- 使用 sequence 函数生成从登录时间到登出时间每小时的时间戳序列
        EXPLODE(sequence(
            CAST(login_in AS TIMESTAMP),
            CAST(login_out AS TIMESTAMP),
            INTERVAL 1 HOUR
        )) AS on_time
    FROM
        data1
),
-- 定义 data3 公共表表达式，统计每个房间、日期和小时的在线用户数
data3 AS (
    SELECT
        room_id,
        DATE(on_time) AS login_date,
        HOUR(on_time) AS on_time,
        COUNT(DISTINCT user_id) AS on_time_cnt
    FROM
        data2
    GROUP BY
        room_id,
        DATE(on_time),
        HOUR(on_time)
)
-- 从 data3 中选择所需的列并展示结果
SELECT
    *
FROM
    data3;

示例数据：

-- 创建 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.kler.cn/a/517271.html

相关文章：

  • 智慧脚下生根，智能井盖监测终端引领城市安全新革命
  • SQL-leetcode—1193. 每月交易 I
  • linux+docker+nacos+mysql部署
  • 微服务学习-Nacos 注册中心实战
  • springboot 配置redis
  • iOS开发设计模式篇第二篇MVVM设计模式
  • 翻译：How do I reset my FPGA?
  • 在Linux中，如何查询已安装软件包的版本信息？
  • 【电脑无法通过鼠标和键盘唤醒应该怎么办】
  • 9.1 GPTs 应用商店介绍：解锁定制化 AI 的无限潜能
  • 使用Swift Package Manager怎样区分debug和release打包环境
  • 从C语言看数据结构和算法：复杂度决定性能
  • Vue-Day1
  • ＜论文＞用于大语言模型去偏的因果奖励机制
  • mybatis在mysql库中创建表实现案例
  • 【PHP】数组转换成json
  • ComfyUI实现老照片修复——AI修复老照片（ComfyUI-ReActor / ReSwapper）解决天坑问题及加速pip下载
  • LangChain + llamaFactory + Qwen2-7b-VL 构建本地RAG问答系统
  • 利用Python爬虫与1688 API接口：高效获取商品搜索数据
  • Docker 实现MySQL 主从复制
  • 搭建CANN开发+运行环境实验报告
  • SpringBoot篇 单元测试 理论篇
  • DRG/DIP 2.0时代下基于PostgreSQL的成本管理实践与探索（下）
  • Vue3 30天精进之旅：Day01 - 初识Vue.js的奇妙世界
  • 豆瓣API-我在IDE上标记想看的电影(低配版API文档)
  • 第6章 数据结构—列表与列表项讲解--总结