LeetCode601查询体育馆的人流量问题详解(MySQL三种方法实现)

题目如下：

X 市建了一个新的体育馆，每日人流量信息被记录在这三列信息中：序号 (id)、日期 (date)、 人流量 (people)。

请编写一个查询语句，找出高峰期时段，要求连续三天及以上，并且每天人流量均不少于100。

例如，表 stadium：

+------+------------+-----------+
| id   | 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-08 | 188       |
+------+------------+-----------+

对于上面的示例数据，输出为：

+------+------------+-----------+
| id   | date       | people    |
+------+------------+-----------+
| 5    | 2017-01-05 | 145       |
| 6    | 2017-01-06 | 1455      |
| 7    | 2017-01-07 | 199       |
| 8    | 2017-01-08 | 188       |
+------+------------+-----------+
Note:
每天只有一行记录，日期随着 id 的增加而增加。

方法一：使用自关联。

完整代码：

select distinct s1.* from stadium as s1,stadium as s2,stadium as s3 
where 
((s1.id+1=s2.id and s2.id+1=s3.id) or (s1.id+2=s2.id and s2.id-1=s3.id)
or (s1.id-1=s2.id and s2.id+2=s3.id) or (s1.id+1=s2.id and s2.id-2=s3.id)
or (s1.id-1=s2.id and s2.id-1=s3.id) or (s1.id-2=s2.id and s2.id+1=s3.id))
and
(s1.people>=100 and s2.people>=100 and s3.people>=100) order by s1.id;

解析：

三个连续整数，比如说5,6,7，它们的排列有6种可能：
5,6,7
5,7,6
6,5,7
6,7,5
7,5,6
7,6,5
每列都是5,6,7这三个整数的重复

从四个连续整数中选出三个连续整数的排列有12种可能：
5,6,7
5,7,6
6,5,7
6,7,5
7,5,6
7,6,5
6,7,8
6,8,7
7,6,8
7,8,6
8,6,7
8,7,6
每列都是5,6,7，8这四个整数的重复

从五个连续整数中选出三个连续整数的排列有18种可能：
5,6,7
5,7,6
6,5,7
6,7,5
7,5,6
7,6,5
6,7,8
6,8,7
7,6,8
7,8,6
8,6,7
8,7,6
7,8,9
7,9,8
8,7,9
8,9,7
9,7,8
9,8,7
每列都是5,6,7,8,9这五个整数的重复

自关联三次就是利用这种思想，stadium表自关联三次后，在结果集中会出现三列id。
在本题中，对于id1,id2,id3每个id的取值都有八中可能，因此一共有8*8*8=512种可能。
我们的查询目标是从数据集种查询出连续三天及以上人流量均不小于100的信息记录，所以可以先从自关联三次的结果集中
筛选出人流量均不小于100的信息记录，然后从id1,id2,id3三列id的所有排列中筛选出id1,id2,id3的排列属于三个连续
整数的排列的所有可能，最后再去重即可实现查询目标。

这种方法适用于数据量较小，或者查询的连续天数较少的情况。比如说，如果查询要求变成连续5天及以上，五个连续整数的排列有120种可能，用这种方法实现起来就较为复杂。

方法二：使用case when函数和用户变量。

完整代码：

SELECT e.id,e.date,e.people FROM
(SELECT id, s.date,
@sdate1 := 
(CASE
WHEN s.people >=100 THEN @sdate1
ELSE @sdate1 + 1
END) AS sdate,s.people FROM  stadium as s,(SELECT @sdate1 := 0) as b)  as e,
(SELECT c.sdate FROM (SELECT id,s.date,
@sdate := 
(CASE
WHEN s.people >= 100 THEN @sdate
ELSE @sdate + 1 END) AS sdate,
s.people FROM stadium as s,(SELECT @sdate := 0) as b) as c WHERE c.people >= 100 
GROUP BY c.sdate HAVING COUNT(c.sdate) >=3) as d
WHERE d.sdate = e.sdate      
AND e.people >= 100;

解析：

基本思路：
步骤一：利用case when函数和用户变量给stadium原表添加一列辅助列，以表明stadium表中people列的
特征。辅助列的添加方法是设置用户变量@sdate的初值为0，根据人流量是否不小于100确定@sdate的变化，
如果people>=100,@sdate保持不变，否则@sdate的值自动增加，stadium表中每行中的people都对应一个
@sdate值，所有的@sdate值组成辅助列。

例如对于本题来说，在stadium表中：
id    people    @sdate
1     10        1
2     109       1
3     150       1
4     99        2
5     145       2
6     1455      2
7     199       2
8     188       2

若people发生变化，则@sdate也随之而变：
id    people    @sdate
1     105       0
2     109       0
3     150       0
4     99        1
5     78        2
6     145       2
7     199       2
8     188       2
9     87        3

步骤二：在给stadium表添加完辅助列然后筛选出people不小于100的信息记录的基础上，我们可以根据辅助
列@sdate的值对stadium表中的信息记录进行分组，并根据每组内的计数值是否小于3筛选出相应的@sdate值.
利用步骤一中的结果与步骤二中的结果进行关联查询，并做进一步筛选从而完成查询目标。

与方法一使用自关联相比，方法二显然更加通用。但方法二的代码稍微复杂(其中有重复用到的代码段)，而且辅助列内的值不容易想到。

方法三：使用嵌套case when函数和用户变量。

完整代码：

select id,date,people from
(select temp.*,f.*,
case
when num = 1 then num
else
(case
when @previd=id-1 then @feature
else @feature:=@feature+1
end)
end as id_feature,@previd:=id
from
(select t.*,@count := @count + 1 as num from (select * from stadium where people >=
100) as t,(select @count := 0) as count) as temp,
(select @previd :=0,@feature := 1) as f) as new_temp
where id_feature in
(select id_feature1 from
(select temp1.*,f1.*,
case
when num1 = 1 then num1
else
(case
when @previd1=id-1 then @feature1
else @feature1:=@feature1+1
end)
end as id_feature1,@previd1:=id
from
(select t1.*,@count1 := @count1 + 1 as num1 from (select * from stadium where people
>= 100) as t1,(select @count1 := 0) as count1) as temp1,
(select @previd1 :=0,@feature1 := 1) as f1) as new_temp1 group by id_feature1 
having count(id_feature1)>=3);

解析：

思路：同样是添加辅助列，但与方法二不同的是。要先从stadium表中筛选出people不小于100的信息记录，
然后在筛选结果中添加一列用于表示id特征的辅助列。
对于本题来说，就是先从
id    people    
1     10       
2     109       
3     150       
4     99        
5     145       
6     1455      
7     199       
8     188 中筛选出：

id    people          
2     109       
3     150              
5     145       
6     1455      
7     199       
8     188 

然后，根据id的是否连续添加一列辅助列：
id    people    id_feature       
2     109       1
3     150       1      
5     145       2
6     1455      2
7     199       2
8     188       2

注：基本思想就是每一组连续id都对应一个相同的@feature值，更多数据的示例如下：
id    people    id_feature          
2     109       1
3     150       1      
5     145       2
6     1455      2
7     199       2
8     188       2
15    182       3
16    189       3
28    301       4
29    556       4
30    412       4

在此基础上，根据id_feature的值对数据进行分组，并根据每组内id_feature值的统计个数是否不小于3确定
满足查询要求的id_feature值，然后以这些id_feature值为条件，进一步筛选出符合查询要求的id,date,people信息。

方法三与方法二相比，添加至stadium表的辅助列中的值更容易想到，但实现辅助列内的值的代码也更复杂(有一段重复用到的代码)，需要用到嵌套的case when函数，这是笔者的想法，如果有朋友有更加容易的实现方法，还请不吝赐教！

参考：

http://www.cnblogs.com/linux-java/p/9766433.html

PS：本文为博主原创文章，转载请注明出处。