4 体育馆的人流

[astak16]

题目

查找出每行人数大于 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 操作符，就能把数据查出来了。

Tips

row_number()语法

在一条 SQL 中不能用两个一样的变量