一、题目
现有一份数据记录了用户进入和离开游泳池的时间，请找出一天中泳池最多时有几个人
--样例数据1 enter 2024-04-07 08:01:002 enter 2024-04-07 08:20:001 leave 2024-04-07 09:30:003 enter 2024-04-07 10:01:004 enter 2024-04-07 10:25:002 leave 2024-04-07 11:22:004 leave 2024-04-07 12:20:005 enter 2024-04-07 12:30:006 enter 2024-04-07 13:05:003 leave 2024-04-07 14:20:005 leave 2024-04-07 14:30:007 enter 2024-04-07 14:45:008 enter 2024-04-07 15:18:007 leave 2024-04-07 15:32:006 leave 2024-04-07 15:45:009 enter 2024-04-07 16:01:0010 enter 2024-04-07 16:10:009 leave 2024-04-07 16:25:008 leave 2024-04-07 16:35:0010 leave 2024-04-07 16:55:00
二、分析
这个题目是根据日志内容，计算时点状态数据，类似的还有根据日志计算直播间用户人数、游戏同时在线人数等。考察内容为累积求和及其使用技巧，属于知识面和经验的考察，如果知道则十分简单。
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️⭐ |
| 业务常见度 | ⭐️⭐️⭐️⭐️ |
三、SQL
1）根据进入和离开的状态，计算泳池人数的变化，进入记为1，离开记为-1
selectuser_id,log_type,log_time,if(log_type ='enter',1,-1) as enter_cntfrom t_user_pool_log
查询结果
2)对enter_cnt进行累积求和，即对sum()函数进行开窗，然后开窗内根据时间进行排序。
selectuser_id,log_type,log_time,enter_cnt,sum(enter_cnt)over(order by log_time asc) as pool_user_cntfrom(selectuser_id,log_type,log_time,if(log_type ='enter',1,-1) as enter_cntfrom t_user_pool_log) t
查询结果
3)对泳池内的用户数计算最大值，则得出当天泳池内的最大人数。
selectmax(pool_user_cnt)from(selectuser_id,log_type,log_time,enter_cnt,sum(enter_cnt)over(order by log_time asc) as pool_user_cntfrom(selectuser_id,log_type,log_time,if(log_type ='enter',1,-1) as enter_cntfrom t_user_pool_log) t)tt
查询结果
四、建表语句和数据插入
--建表语句CREATE TABLE t_user_pool_log (user_id INT,log_type STRING,log_time STRING)COMMENT '泳池进出日志表'ROW FORMAT DELIMITEDFIELDS TERMINATED BY ','LINES TERMINATED BY '\n'STORED AS TEXTFILE;-- 样例数据插入insert into t_user_pool_log values(1,'enter','2024-04-07 08:01:00'),(2,'enter','2024-04-07 08:20:00'),(1,'leave','2024-04-07 09:30:00'),(3,'enter','2024-04-07 10:01:00'),(4,'enter','2024-04-07 10:25:00'),(2,'leave','2024-04-07 11:22:00'),(4,'leave','2024-04-07 12:20:00'),(5,'enter','2024-04-07 12:30:00'),(6,'enter','2024-04-07 13:05:00'),(3,'leave','2024-04-07 14:20:00'),(5,'leave','2024-04-07 14:30:00'),(7,'enter','2024-04-07 14:45:00'),(8,'enter','2024-04-07 15:18:00'),(7,'leave','2024-04-07 15:32:00'),(6,'leave','2024-04-07 15:45:00'),(9,'enter','2024-04-07 16:01:00'),(10,'enter','2024-04-07 16:10:00'),(9,'leave','2024-04-07 16:25:00'),(8,'leave','2024-04-07 16:35:00'),(10,'leave','2024-04-07 16:55:00');
本文同步在微信公众号”数据仓库技术“和个人博客”数据仓库技术“发表。原文:
同时有“数据仓库技术”社群以及有几十位小伙伴一起讨论数据仓库相关技术，欢迎你的加入，社群免费。
原文作者：数据仓库技术；如有侵权，请联系管理员删除。
上一篇：
大数据面试SQL047-泳池问题（中）
下一篇：
数据仓库的核心概念
请登录后发表评论
登录
全部评论
