表:Stadium
+---------------+---------+ | Column Name | Type | +---------------+---------+ | id | int | | visit_date | date | | people | int | +---------------+---------+ visit_date 是该表中具有唯一值的列。 每日人流量信息被记录在这三列信息中:序号 (id)、日期 (visit_date)、 人流量 (people) 每天只有一行记录,日期随着 id 的增加而增加
编写解决方案找出每行的人数大于或等于 100
且 id
连续的三行或更多行记录。
返回按 visit_date
升序排列 的结果表。
查询结果格式如下所示。
示例 1:
输入:
Stadium
表:
+------+------------+-----------+
| 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 |
+------+------------+-----------+
输出:
+------+------------+-----------+
| 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 连续的记录。
先上sql:
SELECT
t2.id,
t2.visit_date,
t2.people
FROM
(
SELECT
count(*) over ( PARTITION BY t1.step ) count,
t1.id,
t1.visit_date,
t1.people
FROM
(
SELECT
s2.id -row_number () over ( ORDER BY s2.id ) step,
s2.id,
s2.visit_date,
s2.people
FROM
Stadium s2
WHERE
s2.people >= 100
ORDER BY
s2.id
) t1
) t2
WHERE
t2.count >= 3
order by t2.id
解题思路:
这个问题就是解决连续列的问题,但是本题对连接数量进行了限制(即大于等于3)。
也就是说要判断当前行id要与下行以及下下行id的连续性,在不考虑使用游标的情况下,自连接能解决此类问题,但是总觉得自连接性能太差(单表数据量如果过大的话,自连接进行笛卡尔积会出现很恐怖的数据量)。
所以要换个角度考虑此问题,如果一个id是连续的,那么他在连续的期间内增幅应该是一致的,但是增幅需要两行相减获取,嗯~,貌似又绕回去了...... 那么,除了id外,每行还有什么东西是连续的呢?对,就是行号,也就是说,在一个连续的区间内,step = id - row_number 一定是固定的。所以反过来step重复即数据连续。
按题目中的用例举例
1.人数大于100过滤后的数据为
一目了然,id(2,3)连续 id - row_number 相同,id(5,6,7,8)连续 id - row_number 相同。
据题目要求,当step的重复数量>=3时,id连续的三行或更多行记录。
id(2,3)重复的数为1,重复了2个,不满足需求,pass。
id(5,6,7,8)重复的数为2,重复了4个,满足需求,bingo。
重复数量mysql可以通过分组统计获取,count(*) over ( PARTITION BY t1.step ) count。