day4 体育馆的人流 mysql

本题使用的是 MySQL8.0，没有在 MySQL5.6 版本中测验过，不保证正确。
题目题目来源：体育馆的人流
查找出每行人数大于 100 且 id 连续的三行或者更多好记录

create table stadium ( id int, visit_date date, people int )insert into stadium values (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);

SQL：方法一

select * from ( select *, count(*) over(partition by diff) as counts from ( select *, id - row_number() over(order by id) as diff from stadium where people >= 100 ) as base ) as s where counts >= 3;

解析问题的关键在于如何知道哪几天的 id 是连续的。
这里用的方法是先筛选出 peope 大于等于 100 的数据，然后对这些数据进行排名，在用 id 减去排名，如果他们之间的差一样，就说明那几天是连续的。
具体步骤：

先筛选出 people 大于等于 100 的数据
使用 row_number() 对 id 计算出排名
在用 id 减去排名，计算出 id 和排名之间的差（作为临时表 base）
对 base 进行查询并按照 diff 进行分组，命名为 counts（作为临时表 s）
- 这里使用 over(partition by diff) 比 group by 更加准确。因为 group by 需要和 select 字段一一对应。
对 s 表进行查询，筛选出 counts 大于等于 3 的数据

SQL：方法二

select * from ( select *, (id - (@rrk:=@rrk + 1)) as diff from stadium, (select @rrk:=0) as init where people >= 100 ) as s1 where diff in ( select diff from ( select (id - (@rk:=@rk + 1)) as diff from stadium, (select @rk:=0) as init where people >= 100 ) as s2 group by diff having count(*) >= 3 );

解析和方法一的思路是一样的，实现的方式不一样，这里是通过 group by 进行分组，所以相对于使用 partition by 的方式步骤更加复杂一点。

先筛选出 people 大于等于 100 的数据。
然后使用变量计算出 diff （也可以使用 row_number()），作为临时表 s1 。
查询 s1 表，筛选出我们想要的数据
这里我们想到如果有一个 diff 的分组就好了，我们可以通过 in 来筛选。
这一步就是重复上面计算 diff 的步骤，然后作为临时表 s2 。
这里外面还要在包一层查询 diff ，就是 select diff from s2 ，使用 group by 对 diff 进行分组，并用 having 筛选出大于等于 3 的 diff
- group by 需要和 select 字段一一对应，如果不做这一次查询，使用 group by 将会有问题。
查询 s1 表，使用 in 操作符，就能把数据查出来了。