一、力扣原题:体育馆的人流量
题目来源:力扣(LeetCode)
链接:https://leetcode-cn.com/problems/human-traffic-of-stadium
以下是力扣原题:
X 市建了一个新的体育馆,每日人流量信息被记录在这三列信息中:序号 (id)、日期 (visit_date)、 人流量 (people)。
请编写一个查询语句,找出人流量的高峰期。高峰期时,至少连续三行记录中的人流量不少于100。
例如,表 stadium:
对于上面的示例数据,输出为:
提示:
每天只有一行记录,日期随着 id 的增加而增加。
题解:
方法:使用 JOIN
和 WHERE
子句。
思路:
-
在表
stadium
中查询人流量超过 100 的记录,将查询结果与其自身的临时表连接,再使用WHERE
子句获得满足条件的记录。 -
a,b 和 c 相同,重点是需要考虑添加哪些条件能够得到想要的结果。以 a为例,它有可能是高峰期的第 1 天,第 2 天,或第
3 天。 -
用排列的方式的话有6个条件
(1、a是高峰期第1天,b是高峰期第2天,c是高峰期第3天;
2、a是高峰期第1天,b是高峰期第3天,c是高峰期第2天;
3、a是高峰期第2天,b是高峰期第1天,c是高峰期第3天;
4、a是高峰期第2天,b是高峰期第3天,c是高峰期第1天;
5、a是高峰期第3天,b是高峰期第1天,c是高峰期第2天;
6、a是高峰期第3天,b是高峰期第2天,c是高峰期第1天;)
根据上面的思路给出SQL语句如下:
select distinct a.* from stadium a,stadium b,stadium c
where a.people>=100 and b.people>=100 and c.people>=100
and ((a.id = b.id - 1 and b.id = c.id - 1) or #(1,2,3)
(a.id = b.id - 2 and b.id = c.id + 1) or #(1,3,2)
(a.id = b.id + 1 and a.id = c.id - 1) or #(2,1,3)
(a.id = b.id - 1 and a.id = c.id + 1) or #(2,3,1)
(a.id = b.id + 2 and b.id = c.id - 1) or #(3,1,2)
(a.id = b.id + 1 and b.id = c.id + 1) ) #(3,2,1)
实际上以上6个条件只需要选择3个(a分别第高峰期第1天,第2天,第3天就行了),上面的sql与下面这个效果一样:
select distinct a.* from stadium a,stadium b,stadium c
where a.people>=100 and b.people>=100 and c.people>=100
and (
(a.id = b.id-1 and b.id = c.id -1) or #(1,2,3)
(a.id = b.id-1 and a.id = c.id +1) or #(2,3,1)
(a.id = b.id+1 and b.id = c.id +1) #(3,2,1)
) order by a.id
感兴趣的可以去力扣搜原题进行练习或者查看更多的解题思路。
二、延伸:连续签到天数计算
这道题跟连续签到天数计算题的原理相似。假设将求体育馆的人流量超过100的连续日期,换成求连续签到天数,该怎么做呢?
我的思路:按照上面求出来的结果,继续取max(visit_date)-min(visit_date)+1就是最大连续签到天数了。
当然,实际工作中,以上题目并不算是一个好的案例。接下来分享几个我平时看到的题和实际案例给大家,最后附上原文,有兴趣可以点击阅读原文。
这题是从公众号上刷到的,是鹅厂的SQL笔试题。(点击进入原文。)
题目:
有一张用户签到表【t_user_attendence】,标记每天用户是否签到(说明:该表包含所有用户所有工作日的出勤记录) ,包含三个字段:日期【fdate】,用户id【fuser_id】,用户当天是否签到【fis_sign_in:0否1是】;
问题1:
请计算截至当前每个用户已经连续签到的天数(输出表仅包含当天签到的所有用户,计算其连续签到天数)
输出表【t_user_consecutive_days】:用户id【fuser_id】,用户联系签到天数【fconsecutive_days】
问题2:
请计算每个用户历史以来最大的连续签到天数(输出表为用户签到表中所有出现过的用户,计算其历史最大连续签到天数)
输出表【t_user_max_days】:用户id【fuser_id】,用户最大连续签到天数【fmax_days】
还有一个案例是用SQL存储过程解决的。
《获取连续登陆天数,连续签到天数 ,方法优化》
文章思路清晰,易懂。认真负责任的程序员GG讲了一个好故事,生动有趣。