(某条件)连续出现N次的问题
# 180. 连续出现的数字（难度：<font color=orange>**中等**</font>）
编写一个 SQL 查询，查找所有至少连续出现三次的数字。
例如，给定上面的 Logs 表， 1 是唯一连续出现至少三次的数字。
**解法：**
**（1）笛卡尔积** 395 ms
```sql
SELECT DISTINCT
l1.Num AS ConsecutiveNums
FROM
Logs l1,
Logs l2,
Logs l3
WHERE
l1.Id = l2.Id - 1
AND l2.Id = l3.Id - 1
AND l1.Num = l2.Num
AND l2.Num = l3.Num
```
**（2）定义自变量** 387 ms
```sql
SELECT DISTINCT Num AS ConsecutiveNums
FROM (
SELECT Num , @P:=IF( @T=Num , @P:=@P+1 , @P:=1 )AS P, @T:=Num AS T, IF( @P>=3 , @Z:=1 , @Z:=0 ) AS FLAG
FROM `Logs` , ( SELECT @T:=NULL ,@P:=1 , @Z:=0 ) L ) K
WHERE FLAG=1
ORDER BY Num
```
**（3）连接** 409 ms
```sql
SELECT DISTINCT L1.Num ConsecutiveNums
FROM `Logs` L1 LEFT JOIN `Logs` L2
ON ( L1.Id-1=L2.Id OR L1.Id+1=L2.Id ) AND L1.Num=L2.Num
GROUP BY L1.Id
HAVING COUNT(L2.Id)=2
ORDER BY ConsecutiveNums
```
**（4）窗口函数** 343 ms
- 使用窗口函数的偏差函数完美实现。 可以这样理解：将num复制两列num1和num2，然后num1整体向上移动一行，num2整体向上移动两行，如下 :
```sql
select distinct num as ConsecutiveNums from
(
select num,lead(num,1)over()as num1,lead(num,2)over()as num2
from logs
) as c
where c.num = c.num1 and c.num1 = c.num2
```
# 601. 体育馆的人流量（难度：<font color=red>**困难**</font>）
X 市建了一个新的体育馆，每日人流量信息被记录在这三列信息中：序号 (id)、日期 (visit_date)、 人流量 (people)。
请编写一个查询语句，找出人流量的高峰期。高峰期时，至少连续三行记录中的人流量不少于100。
例如，表 stadium：
对于上面的示例数据，输出为：
**提示：** 每天只有一行记录，日期随着 id 的增加而增加。
**解法：**
**（1）<font color=blue>找到满足条件的起始与终止id</font>**
- 此处寻找起始与终止id用的是窗口函数，也可以用变量法更快些
```sql
select stadium.*
from stadium
join (
#找到连续人流>99的起始与终止id
select min(id) `min`,max(id) `max`
from (
#编号并算出编号与id差值
select *,(id - row_number()over()) group_num
from stadium
where people > 99
) tmp
#按照差值分组
group by group_num
#筛掉连续数量小于3的
having count(id) > 2
) `range`
#匹配范围中的
on id between `min` and `max`;
```
**（2）自定义变量+笛卡尔积** 243 ms
```sql
select distinct s.*
from stadium s,
( select id,visit_date,people,( @cnt:=IF(people>99,@cnt+1,0) ) as cnt
from stadium,(select @cnt:=0) b) c
where c.cnt>2 and s.id between c.id-c.cnt+1 and c.id
```
**（3）笛卡尔积**
```sql
select distinct t1.*
from stadium t1, stadium t2, stadium t3
where t1.people >= 100 and t2.people >= 100 and t3.people >= 100
and
(
(t1.id - t2.id = 1 and t1.id - t3.id = 2 and t2.id - t3.id =1) -- t1, t2, t3
or
(t2.id - t1.id = 1 and t2.id - t3.id = 2 and t1.id - t3.id =1) -- t2, t1, t3
or
(t3.id - t2.id = 1 and t2.id - t1.id =1 and t3.id - t1.id = 2) -- t3, t2, t1
)
order by t1.id
;
```
# 1225. 报告系统状态的连续日期（难度：<font color=red>**困难**</font>）
<img src="https://cos.easydoc.net/17082933/files/keh2383c.png" style="zoom: 80%;" />
系统 **每天** 运行一个任务。每个任务都独立于先前的任务。任务的状态可以是失败或 是成功。
编写一个 SQL 查询 **2019-01-01** 到 **2019-12-31** 期间任务连续同状 态 period_state 的起止日期（ start_date 和 end_date ）。即如果任务失败 了，就是失败状态的起止日期，如果任务成功了，就是成功状态的起止日期。
最后结果按照起始日期 start_date 排序 查询结果样例如下所示:
<img src="https://cos.easydoc.net/17082933/files/keh25klw.png" style="zoom: 80%;" />
结果忽略了 **2018** 年的记录，因为我们只关心从 **2019-01-01** 到 **201912-31** 的记录 从 **2019-01-01** 到 **2019-01-03** 所有任务成功，系统状态为 **"succeeded"**。 从 **2019-01-04** 到 **2019-01-05**所有任务失败，系统状态为 **"failed"**。 从 **2019-01-06** 到 **2019-01-06** 所有任务成功，系统状态为 **"succeeded"**。
**解法：**
**（1）定义自变量**
```sql
select * from
((select 'succeeded' period_state, min(success_date)start_date, max(success_date)end_date
from (select success_date, if(datediff(success_date, @prev)=1, @count, @count:=@count+1) num, @prev:=success_date
from Succeeded, (select @prev:=null, @count:=0)i
where year(success_date)='2019') t
group by t.num)
union all
(select 'failed' period_state, min(fail_date)start_date, max(fail_date)end_date
from (select fail_date, if(datediff(fail_date, @prev)=1, @count, @count:=@count+1) num, @prev:=fail_date
from Failed, (select @prev:=null, @count:=0)i
where year(fail_date)='2019') t
group by t.num)) t
order by start_date;
```
```SQL
SELECT * FROM(
SELECT 'failed' AS period_state,MIN(fail_date) AS start_date ,MAX(fail_date) AS end_date
FROM(
SELECT fail_date,ADDDATE(fail_date,-row_number() over(ORDER BY fail_date ASC)) AS r
FROM failed
WHERE fail_date BETWEEN '2019-01-01' AND '2019-12-31'
)a
GROUP BY r
UNION ALL
SELECT 'succeeded' AS period_state,MIN(success_date)AS start_date,MAX(success_date)AS end_date
FROM(
SELECT success_date,ADDDATE(success_date,-row_number() over(ORDER BY success_date ASC)) AS r
FROM succeeded
WHERE success_date BETWEEN '2019-01-01' AND '2019-12-31'
)a
GROUP BY r)b
ORDER BY start_date ASC;
```