SQL示例：巧妙的解题思路学习（MySQL）

SQL68 短视频直播间晚上11-12点之间各直播间的在线人数

描述

现有某天短视频直播间用户观看直播间的信息表user_view_tb如下，

（其中字段包含用户id：user_id、直播间id：room_id、 进入时间：in_time，离开时间：out_time）

user_id	room_id	in_time	out_time
1	1001	10:00:00	10:30:00
2	1001	10:01:00	10:05:00
3	1001	10:05:00	10:20:00
1	1002	19:05:00	20:05:00
2	1002	19:15:00	19:55:00
2	1002	20:15:00	20:45:00
3	1002	20:15:00	20:45:00
4	1003	22:15:00	23:15:00
1	1002	23:15:00	23:45:00
4	1002	23:10:00	23:25:00
3	1002	23:00:00	23:35:00
4	1001	23:10:00	23:25:00
3	1001	23:00:00	23:35:00
4	1003	23:10:00	23:15:00
1	1001	20:10:00	20:15:00
1	1001	20:00:00	23:35:00

有直播间信息表room_info_tb如下：

room_id	room_name	room_type
1001	娱乐大王牌	娱乐
1002	声家班	搞笑
1003	嗨嗨嗨	搞笑

请你统计晚上11-12点之间各直播间的在线人数(包含边界值11:00、12:00)，并按在线人数降序排序，以上例子输出结果如下：

room_id	room_name	user_count
1001	娱乐大王牌	3
1002	声家班	3
1003	嗨嗨嗨	1

解法

SELECT u.room_id, r.room_name, COUNT(DISTINCT u.user_id) AS user_count FROM user_view_tb u JOIN room_info_tb r ON u.room_id = r.room_id WHERE u.in_time < '24:00:00' -- 进入时间在12点前 AND u.out_time > '23:00:00' -- 离开时间在11点后 GROUP BY u.room_id, r.room_name ORDER BY user_count DESC, u.room_id;

SQL60 统计加班员工占比

描述

某公司员工信息数据及单日出勤信息数据如下：

员工信息表staff_tb(staff_id-员工id,staff_name-员工姓名,staff_gender-员工性别,post-员工岗位类别,department-员工所在部门)，如下所示：

staff_id	staff_name	staff_gender	post	department
1	Angus	male	Financial	dep1
2	Cathy	female	Director	dep1
3	Aldis	female	Director	dep2
4	Lawson	male	Engineer	dep1
5	Carl	male	Engineer	dep2
6	Ben	male	Engineer	dep1
7	Rose	female	Financial	dep2

出勤信息表attendent_tb(info_id-信息id,staff_id-员工id,first_clockin-上班打卡时间,last_clockin-下班打卡时间)，如下所示：

info_id	staff_id	first_clockin	last_clockin
101	1	2022-03-22 08:00:00	2022-03-22 17:00:00
102	2	2022-03-22 08:30:00	2022-03-22 18:00:00
103	3	2022-03-22 08:45:00	2022-03-22 17:00:00
104	4	2022-03-22 09:00:00	2022-03-22 18:30:00
105	5	2022-03-22 09:00:00	2022-03-22 18:10:00
106	6	2022-03-22 09:15:00	2022-03-22 19:30:00
107	7	2022-03-22 09:30:00	2022-03-22 18:29:00

问题：请统计该公司各部门加班员工所占比例？

注：工作时长大于9.5小时定义为加班

要求输出：部门（department）、加班员工占比（ratio，以百分数形式输出并保留1位小数），查询结果按照加班员工占比降序排序；
示例数据结果如下：

department	ratio
dep1	25.0%
dep2	0.0%

解释：dep1部门共有4名员工，其中仅有员工6(Ben)在该日加班，所以结果为1/4=25.0%

其他结果同理.....

解法

with t1 as( select department,count(s.staff_id) as tol1 from attendent_tb a join staff_tb s on a.staff_id=s.staff_id group by department ), t2 as ( select department,count(s.staff_id) as tol2 from attendent_tb a join staff_tb s on a.staff_id=s.staff_id where TIMESTAMPDIFF(MINUTE, first_clockin, last_clockin) > 9.5 * 60 group by department ) select t1.department, concat(round(tol2/tol1*100.0,1),'%') as ratio from t1 join t2 on t1.department=t2.department order by ratio desc; --TIMESTAMPDIFF(hour, ...) 只会返回 10（截断小数部分），这样 10 > 9.5 判断会失效。 --改用 TIMESTAMPDIFF(MINUTE, ...)，计算分钟差，再转换为小时判断。 --其他的解法 select department, concat( round( sum( case when (unix_timestamp(last_clockin) - unix_timestamp(first_clockin) )/3600 > 9.5 then 1 else 0 end ) * 100 / count(1),1) ,'%') as ratio from staff_tb a1 inner join attendent_tb a2 on a1.staff_id = a2.staff_id group by department order by ratio desc;

SQL67 被重复观看次数最多的3个视频

描述

现有课程信息表 course_info_tb（cid：课程ID，tag：视频类别，release_date：发布日期，duration：视频时长)，示例数据如下：

id	cid	tag	release_date	duration
1	9001	sql	2022-01-01	60
2	9002	sql	2022-01-01	90
3	9003	sql	2022-01-01	45
4	9004	java	2022-01-02	45

用户观看记录表 play_record_tb（uid：用户ID，cid：课程ID，start_time：开始观看时间，end_time：结束观看时间，score：用户评分），示例数据如下：

id	uid	cid	start_time	end_time	score
1	1001	9001	2022-01-01 08:30:00	2022-01-01 09:00:00	4
2	1001	9001	2022-01-03 09:30:00	2022-01-03 10:20:00	2
3	1002	9001	2022-01-01 08:30:00	2022-01-01 09:40:00	3
4	1001	9002	2022-01-02 08:30:00	2022-01-02 09:01:00	2
5	1001	9002	2022-01-11 08:30:00	2022-01-11 08:31:01	3
6	1001	9002	2022-01-05 08:30:00	2022-01-05 08:54:01	2
7	1003	9002	2022-01-05 08:30:00	2022-01-05 08:51:01	4

请找到那些能让用户一遍接一遍重复观看的高回头率视频，输出被重复观看人次数。若某人对某视频只观看了一次，则不计为重复观看次数，如果某人对某视频观看了n次（n>1），则记为该视频重复观看次数+n。如果被重复观看次数一样大，则越晚发布的视频排名越靠前，每个视频的排名为排在他前面的视频个数+1。请找出被重复观看数排名前三的视频，输出这些视频的课程ID、被重复观看次数和排名。结果按排名升序。若被重复观看视频不足三个，按排名全部输出。示例输出如下：

cid	pv	rk
9002	3.000	1
9001	2.000	2

解法

WITH user_watch AS ( SELECT uid, cid, COUNT(*) AS watch_cnt FROM play_record_tb GROUP BY uid, cid ), repeat_stats AS ( SELECT cid, SUM(watch_cnt) AS pv FROM user_watch WHERE watch_cnt > 1 GROUP BY cid ) SELECT rs.cid, rs.pv, ROW_NUMBER() OVER (ORDER BY rs.pv DESC, ci.release_date DESC) AS rk FROM repeat_stats rs JOIN course_info_tb ci ON rs.cid = ci.cid ORDER BY rk LIMIT 3;