Loading [MathJax]/jax/output/CommonHTML/config.js
首页
学习
活动
专区
圈层
工具
发布
首页
学习
活动
专区
圈层
工具
MCP广场
社区首页 >专栏 >美团字节大数据面试SQL-每分钟最大直播人数

美团字节大数据面试SQL-每分钟最大直播人数

作者头像
数据仓库晨曦
发布于 2024-05-18 01:18:53
发布于 2024-05-18 01:18:53
42300
代码可运行
举报
文章被收录于专栏:数据仓库技术数据仓库技术
运行总次数:0
代码可运行

一、题目

有如下数据记录直播平台主播上播及下播时间,根据该数据计算出平台每分钟最大直播人数。

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
+----------+----------------------+----------------------+
| user_id  |      start_time      |       end_time       |
+----------+----------------------+----------------------+
| 1        | 2024-04-29 01:00:00  | 2024-04-29 02:01:05  |
| 2        | 2024-04-29 01:05:00  | 2024-04-29 02:03:18  |
| 3        | 2024-04-29 02:00:00  | 2024-04-29 04:03:22  |
| 4        | 2024-04-29 03:15:07  | 2024-04-29 04:33:21  |
| 5        | 2024-04-29 03:34:16  | 2024-04-29 06:10:45  |
| 6        | 2024-04-29 05:22:00  | 2024-04-29 07:01:08  |
| 7        | 2024-04-29 06:11:03  | 2024-04-29 09:26:05  |
| 3        | 2024-04-29 08:00:00  | 2024-04-29 12:34:27  |
| 1        | 2024-04-29 11:00:00  | 2024-04-29 16:03:18  |
| 8        | 2024-04-29 15:00:00  | 2024-04-29 17:01:05  |
+----------+----------------------+----------------------+

二、分析

查询每分钟最大在线人数,这里处理逻辑基本和最高峰在线人数是一致的,但有一个问题,如果某一分钟内无任何操作记录,则不会出现该分钟的数据,我们就统计不到。所以这里额外考察了生成数据;

维度

评分

题目难度

⭐️⭐️⭐️⭐️

题目清晰度

⭐️⭐️⭐️⭐️⭐️

业务常见度

⭐️⭐️⭐️⭐️⭐️

三、SQL

每分钟最大直播人数

1.首先对原始数据进行处理,生成主播上下播的日志数据,同时增加人数变化字段,主播上播为1,主播下播-1。新数据包含 user_id,action_time,change_cnt

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
--开播记录
select
user_id,
start_time as action_time,
1 as change_cnt
from t_livestream_log
union all 
--下播记录
select
user_id,
end_time as action_time,
-1 as change_cnt
from t_livestream_log

查询结果

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
+--------------+----------------------+-----------------+
| _u1.user_id  |   _u1.action_time    | _u1.change_cnt  |
+--------------+----------------------+-----------------+
| 1            | 2024-04-29 01:00:00  | 1               |
| 1            | 2024-04-29 02:01:05  | -1              |
| 2            | 2024-04-29 01:05:00  | 1               |
| 2            | 2024-04-29 02:03:18  | -1              |
| 3            | 2024-04-29 02:00:00  | 1               |
| 3            | 2024-04-29 04:03:22  | -1              |
| 4            | 2024-04-29 03:15:07  | 1               |
| 4            | 2024-04-29 04:33:21  | -1              |
| 5            | 2024-04-29 03:34:16  | 1               |
| 5            | 2024-04-29 06:10:45  | -1              |
| 6            | 2024-04-29 05:22:00  | 1               |
| 6            | 2024-04-29 07:01:08  | -1              |
| 7            | 2024-04-29 06:11:03  | 1               |
| 7            | 2024-04-29 09:26:05  | -1              |
| 3            | 2024-04-29 08:00:00  | 1               |
| 3            | 2024-04-29 12:34:27  | -1              |
| 1            | 2024-04-29 11:00:00  | 1               |
| 1            | 2024-04-29 16:03:18  | -1              |
| 8            | 2024-04-29 15:00:00  | 1               |
| 8            | 2024-04-29 17:01:05  | -1              |
+--------------+----------------------+-----------------+

2.生成0~24*60-1条记录数据

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
select idx from (select posexplode(split(space(24*60),' ')) as (idx,value)) t

查询结果,这里仅显示前10行数据

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
+------+
| idx  |
+------+
| 0    |
| 1    |
| 2    |
| 3    |
| 4    |
| 5    |
| 6    |
| 7    |
| 8    |
| 9    |
+------+

3.根据2生成每分钟一条记录的心跳记录,心跳记录change_cnt= 0 ,代表没有主播上播,也没有主播下播。

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
SELECT
    0 as user_id,
    from_unixtime(unix_timestamp('2024-04-29','yyyy-MM-dd')+item*60,'yyyy-MM-dd HH:mm:ss') as action_time,
    0 as change_cnt
from (select posexplode(split(space(24*60),' ')) as (item,value)) t
limit 10;

查询结果,这里仅显示前10行数据

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
+----------+----------------------+-------------+
| user_id  |     action_time      | change_cnt  |
+----------+----------------------+-------------+
| 0        | 2024-04-29 00:00:00  | 0           |
| 0        | 2024-04-29 00:01:00  | 0           |
| 0        | 2024-04-29 00:02:00  | 0           |
| 0        | 2024-04-29 00:03:00  | 0           |
| 0        | 2024-04-29 00:04:00  | 0           |
| 0        | 2024-04-29 00:05:00  | 0           |
| 0        | 2024-04-29 00:06:00  | 0           |
| 0        | 2024-04-29 00:07:00  | 0           |
| 0        | 2024-04-29 00:08:00  | 0           |
| 0        | 2024-04-29 00:09:00  | 0           |
+----------+----------------------+-------------+

4.汇总所有数据之后,对change_cnt累积求和,然后求出每分钟的最大值即可

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
with t_all as(
--开播记录
select
    user_id,
    start_time as action_time,
    1 as change_cnt
from t_livestream_log
union all
--下播记录
select
    user_id,
    end_time as action_time,
    -1 as change_cnt
from t_livestream_log
--心跳数据
union all
SELECT
    0 as user_id,
    from_unixtime(unix_timestamp('2024-04-29','yyyy-MM-dd')+item*60,'yyyy-MM-dd HH:mm:ss') as action_time,
    0 as change_cnt
from (select posexplode(split(space(24*60),' ')) as (item,value)) t
)
select
    date_format(action_time,'yyyy-MM-dd HH:mm') as act_minute,
    max(online_cnt) as minute_max_cnt
from (select user_id,
             action_time,
             change_cnt,
             sum(change_cnt) over (order by action_time asc) online_cnt
      from t_all
      )t1
group by date_format(action_time,'yyyy-MM-dd HH:mm')
;

查询结果,截取了部分有直播数据进行展示

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
|    act_minute     | minute_max_cnt  |
+-------------------+-----------------+
| 2024-04-29 16:40  | 1               |
| 2024-04-29 16:41  | 1               |
| 2024-04-29 16:42  | 1               |
| 2024-04-29 16:43  | 1               |
| 2024-04-29 16:44  | 1               |
| 2024-04-29 16:45  | 1               |
| 2024-04-29 16:46  | 1               |
| 2024-04-29 16:47  | 1               |
| 2024-04-29 16:48  | 1               |
| 2024-04-29 16:49  | 1               |
| 2024-04-29 16:50  | 1               |
| 2024-04-29 16:51  | 1               |
| 2024-04-29 16:52  | 1               |
| 2024-04-29 16:53  | 1               |
| 2024-04-29 16:54  | 1               |
| 2024-04-29 16:55  | 1               |
| 2024-04-29 16:56  | 1               |
| 2024-04-29 16:57  | 1               |
| 2024-04-29 16:58  | 1               |

四、建表语句和数据插入

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
CREATE TABLE IF NOT EXISTS t_livestream_log (
    user_id INT, -- 主播ID
    start_time STRING, -- 开始时间
    end_time STRING -- 结束时间
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','  -- 字段使用逗号分隔
STORED AS ORC;

insert into t_livestream_log(user_id, start_time, end_time) values 
(1,'2024-04-29 01:00:00','2024-04-29 02:01:05'),
(2,'2024-04-29 01:05:00','2024-04-29 02:03:18'),
(3,'2024-04-29 02:00:00','2024-04-29 04:03:22'),
(4,'2024-04-29 03:15:07','2024-04-29 04:33:21'),
(5,'2024-04-29 03:34:16','2024-04-29 06:10:45'),
(6,'2024-04-29 05:22:00','2024-04-29 07:01:08'),
(7,'2024-04-29 06:11:03','2024-04-29 09:26:05'),
(3,'2024-04-29 08:00:00','2024-04-29 12:34:27'),
(1,'2024-04-29 11:00:00','2024-04-29 16:03:18'),
(8,'2024-04-29 15:00:00','2024-04-29 17:01:05');
本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2024-05-10,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 数据仓库技术 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
暂无评论
推荐阅读
编辑精选文章
换一批
字节快手大数据面试SQL-最高峰同时直播人数
有如下数据记录直播平台主播上播及下播时间,根据该数据计算出平台最高峰同时直播人数。
数据仓库晨曦
2024/04/30
2570
字节快手大数据面试SQL-最高峰同时直播人数
最强最全面的大数据SQL面试题和答案(由31位大佬共同协作完成)
本套SQL题的答案是由许多大佬共同贡献,1+1的力量是远远大于2的,有不少题目都采用了非常巧妙的解法,也有不少题目有多种解法。本套大数据SQL题不仅题目丰富多样,答案更是精彩绝伦!
五分钟学大数据
2021/12/27
5.4K0
常见大数据面试SQL-物流线路分析SQL
该题目难度不高,但是计算起来比较麻烦,但是实际业务中这样的加工相对常见。面试遇到这样的题目,说明团队相对比较务实,但是日常工作可能也是类似繁琐内容较多。在面试过程中,从内容理解和解题上,都属于内容量比较多的。
数据仓库晨曦
2024/09/10
2040
常见大数据面试SQL-物流线路分析SQL
大数据面试SQL046-泳池问题(上)
这个题目是根据日志内容,计算时点状态数据,类似的还有根据日志计算直播间用户人数、游戏同时在线人数等。考察内容为累积求和及其使用技巧,属于知识面和经验的考察,如果知道则十分简单。
数据仓库晨曦
2024/04/11
1580
大数据面试SQL046-泳池问题(上)
HiveSQL-面试题028 用户中两人一定认识的组合数
有某城市网吧上网记录表,包含字段:网吧id,访客id(身份证号),上线时间,下线时间。
数据仓库晨曦
2024/01/08
1980
HiveSQL-面试题028 用户中两人一定认识的组合数
留存专题-基础数据生成
为完成留存数据,我们需要先准备相关数据,包含三份数据:日期维表、新增用户表、用户登录表。生成数据涉及不少知识点,之前基本都写过了,这里不再赘述。涉及函数如下。 space() split() posexplode() random() datediff() pow()
数据仓库晨曦
2024/12/19
1160
留存专题-基础数据生成
常见大数据面试SQL-各用户最长的连续登录天数-可间断
现有各用户的登录记录表t_login_events如下,表中每行数据表达的信息是一个用户何时登录了平台。现要求统计各用户最长的连续登录天数,间断一天也算作连续,例如:一个用户在1,3,5,6登录,则视为连续6天登录。
数据仓库晨曦
2024/07/12
7140
常见大数据面试SQL-各用户最长的连续登录天数-可间断
快手大数据面试SQL-用户中两人一定认识的组合数
有某城市网吧上网记录表,包含字段:网吧id,访客id(身份证号),上线时间,下线时间。
数据仓库晨曦
2024/06/17
1420
快手大数据面试SQL-用户中两人一定认识的组合数
百度大数据面试SQL-连续签到领金币
有用户签到记录表,t_coin_signin,记录用户当天是否完成签到,请计算出每个用户的每个月获得的金币数量;
数据仓库晨曦
2024/07/12
1710
百度大数据面试SQL-连续签到领金币
滴滴大数据面试SQL-截止目前登陆用户数及登陆用户列表
已知有用户登录记录表包含登录日期和登录用户ID,请查询出截止到当前日期累积登录用户数及登陆用户列表
数据仓库晨曦
2024/06/27
2220
滴滴大数据面试SQL-截止目前登陆用户数及登陆用户列表
美团大数据面试SQL-计算用户首单是即时单的比例
在外卖订单中,有时用户会指定订单的配送时间。现定义:如果用户下单日期与期望配送日期相同则认为是即时单,如果用户下单日期与期望配送时间不同则是预约单。每个用户下单时间最早的一单为用户首单,请计算用户首单中即时单的占比。
数据仓库晨曦
2024/07/25
2130
美团大数据面试SQL-计算用户首单是即时单的比例
大数据面试SQL048-泳池问题(下)
我们接着上两题继续讨论泳池问题,还是相同的数据。现有一份数据记录了用户进入和离开游泳池的时间,请计算出泳池内的平均人数
数据仓库晨曦
2024/04/11
1420
大数据面试SQL048-泳池问题(下)
腾讯大数据面试SQL-合并连续支付订单
现有一张用户支付表:t_user_pay包含字段订单ID,用户ID,商户ID,支付时间,支付金额。如果同一用户在同一商户存在多笔订单,且中间该用户没有其他商户的支付记录,则认为是连续订单,请把连续订单进行合并,时间取最早支付时间,金额求和。
数据仓库晨曦
2024/06/17
1792
腾讯大数据面试SQL-合并连续支付订单
腾讯大数据面试SQL-连续登陆超过N天的用户
现有用户登录日志表 t_login_log,包含用户ID(user_id),登录日期(login_date)。数据已经按照用户日期去重,请查出连续登录超过4天的用户ID
数据仓库晨曦
2024/06/27
2130
腾讯大数据面试SQL-连续登陆超过N天的用户
大数据面试SQL043-计算出完成订单数的众数
众数是描述数据集中趋势的一种方式,它特别适用于分类数据和顺序数据。在实际应用中,众数可以帮助我们了解数据的集中趋势,尤其是在数据分布不均匀时。
数据仓库晨曦
2024/02/28
2420
大数据面试SQL043-计算出完成订单数的众数
大数据 面试SQL 037查询最近一笔有效订单
现有订单表t_order_037,包含订单ID,订单时间,下单用户,当前订单是否有效
数据仓库晨曦
2024/01/08
3440
大数据 面试SQL 037查询最近一笔有效订单
大数据面试SQL045-每个用户每月访问次数占比及累积占比
接上一道题目大数据面试SQL044-统计每个用户累计访问次数我们再进一步探查sum()聚合函数使用over()开窗后有order by和没有order by的区别。
数据仓库晨曦
2024/03/25
2930
大数据面试SQL045-每个用户每月访问次数占比及累积占比
大数据面试SQL044-统计每个用户累计访问次数
有如下用户访问数据t_visit_stat_044,包含用户ID(user_id),访问日期(visit_date),当天访问次数(visit_cnt)
数据仓库晨曦
2024/03/25
4102
大数据面试SQL044-统计每个用户累计访问次数
常见大数据面试SQL-近30天连续登陆3天以上次数-非开窗
有用户登录记录,已经按照日期去重。求近三十天,用户连续登录超过3天的次数,一直连续登录算一次,有间隔然后重新计算次数, 要求不能用开窗
数据仓库晨曦
2024/08/27
2610
常见大数据面试SQL-近30天连续登陆3天以上次数-非开窗
大数据SQL-查询最新有效订单记录
现有订单操作记录表t_order_log,包含用户ID,订单ID,操作状态,操作时间。该表中操作状态包含下单、取消、改单,三种状态,用户取消订单后,则该订单不能再做修改。
数据仓库晨曦
2025/06/26
940
大数据SQL-查询最新有效订单记录
推荐阅读
相关推荐
字节快手大数据面试SQL-最高峰同时直播人数
更多 >
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档