601. Human Traffic of Stadium - 体育馆的人流量＜Hard＞ - 重点警告

最新推荐文章于 2024-03-11 10:48:40 发布

原创最新推荐文章于 2024-03-11 10:48:40 发布 · 261 阅读

0 ·

CC 4.0 BY-SA版权

LeetCode - sql 专栏收录该内容

15 篇文章

订阅专栏

此博客介绍如何使用SQL查询从Stadium表中筛选出人流量大于或等于100且id连续的至少三天记录，结果按visit_date升序排列。查询技巧涉及连续计数和子查询，以找到满足条件的连续id区间。

表：Stadium
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| visit_date | date |
| people | int |
+---------------+---------+
visit_date 是表的主键
每日人流量信息被记录在这三列信息中：序号 (id)、日期 (visit_date)、人流量 (people)
每天只有一行记录，日期随着 id 的增加而增加

编写一个 SQL 查询以找出每行的人数大于或等于 100 且 id 连续的三行或更多行记录。

返回按 visit_date 升序排列的结果表。

查询结果格式如下所示。

Stadium table:
+------+------------+-----------+
| id | visit_date | people |
+------+------------+-----------+
| 1 | 2017-01-01 | 10 |
| 2 | 2017-01-02 | 109 |
| 3 | 2017-01-03 | 150 |
| 4 | 2017-01-04 | 99 |
| 5 | 2017-01-05 | 145 |
| 6 | 2017-01-06 | 1455 |
| 7 | 2017-01-07 | 199 |
| 8 | 2017-01-09 | 188 |
+------+------------+-----------+

Result table:
+------+------------+-----------+
| id | visit_date | people |
+------+------------+-----------+
| 5 | 2017-01-05 | 145 |
| 6 | 2017-01-06 | 1455 |
| 7 | 2017-01-07 | 199 |
| 8 | 2017-01-09 | 188 |
+------+------------+-----------+
id 为 5、6、7、8 的四行 id 连续，并且每行都有 >= 100 的人数记录。
请注意，即使第 7 行和第 8 行的 visit_date 不是连续的，输出也应当包含第 8 行，因为我们只需要考虑 id 连续的记录。
不输出 id 为 2 和 3 的行，因为至少需要三条 id 连续的记录。

Write an SQL query to display the records with three or more rows with consecutive id's, and the number of people is greater than or equal to 100 for each. Return the result table ordered by visit_date in ascending order.

# 对于相同表实例join,left join效果一样
# 设置临时变量cnt作为连续记录的个数, 使用新的stadium表实例t进行条件过滤，得到每一个满足条件的[id-cnt+1,id]区间

select distinct s.* from stadium s
left join (select id,visit_date,people,(@cnt:=IF(people>99,@cnt+1,0)) cnt from stadium,(select @cnt:=0) init) t on s.id between t.id-t.cnt+1 and t.id
where t.cnt>2

select distinct s.* 
from stadium s,(select id,visit_date,people,(@cnt:=IF(people>99,@cnt+1,0)) cnt from stadium,(select @cnt:=0) init) t
where s.id between t.id-t.cnt+1 and t.id and t.cnt>2

另解：

建立三个表实例l1，l2， l3分别内交（或左连接），l1与l2 id的下一个位置比，l1与l3 id下两个位置比；

考虑到t1，t2和t3是相同的，我们可以考虑其中一个来考虑我们应该添加哪些条件来过滤数据并获得最终结果。以t1为例，它可能存在于连续3天的开始，或中间或最后一天。

select distinct a.* from stadium a,stadium b,stadium c
where (
    a.id = b.id -1 && a.id = c.id -2 ||
    b.id = a.id -1 && b.id = c.id -2 ||
    b.id = c.id -1 && b.id = a.id -2
)  
and a.people >= 100 && b.people >= 100 && c.people >= 100
order by a.id