求解连续区间是数据分析、数据仓库笔试面试中常考的SQL题目，今天为各位小伙伴分享笔试面试题，期待各位拿到心仪的offer或有所收获！
01
连续出现的数字
编写一个 SQL 查询，查找所有至少连续出现三次的数字。
+----+-----+
| Id | Num |
+----+-----+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 2 |
| 5 | 1 |
| 6 | 2 |
| 7 | 2 |
+----+-----+
例如，给定上面的 Logs 表， 1 是唯一连续出现至少三次的数字。
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1 |
+-----------------+
解法一:自连接三次,筛选l1,l2,l3的id连续且Num相等的行
注:如果数字连续出现超过三次,Num就会出现重复值,所以需要使用distinct去重
该方法实现简单，但是时间复杂度较大，且如果改成连续出现4,5..10次，代码就会非常冗余，不具备可拓展性。
select distinct l1.Num as ConsecutiveNums
from Logs l1,Logs l2,Logs l3
where l2.Id = l1.Id + 1
and l3.Id = l2.Id + 1
and l1.Num = l2.Num
and l2.Num = l3.Num
解法二:使用自定义变量进行条件判断
速度比解法一快，且适用于任一连续次数
select distinct Num ConsecutiveNums
from
(
select Num,
case
when @prev = Num then @count := @count+1 --如果与之前相等则+1
else (@prev := Num) and (@count := 1) --如果不相等 重新赋值，count变1
end CNT
from Logs,(select @prev := 0, @count := 0) t --变量初始化
) t
where t.CNT>=3
解法三:窗口函数
select distinct Num as ConsecutiveNums
from
(
select Num,Id,
lag(Id,2)over(partition by Num order by Id) as prev
from Logs
)t
where t.Id = t.prev + 2
02
找到连续区间的开始和结束数字
表:Logs
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| log_id | int |
+---------------+---------+
id 是上表的主键。
上表的每一行包含日志表中的一个 ID。
后来一些 ID 从 Logs 表中删除。编写一个 SQL 查询得到 Logs 表中的
连续区间的开始数字和结束数字。
将查询表按照 start_id 排序。
查询结果格式如下面的例子：
Logs 表：
+------------+
| log_id |
+------------+
| 1 |
| 2 |
| 3 |
| 7 |
| 8 |
| 10 |
+------------+
结果表：
+------------+--------------+
| start_id | end_id |
+------------+--------------+
| 1 | 3 |
| 7 | 8 |
| 10 | 10 |
+------------+--------------+
结果表应包含 Logs 表中的所有区间。
从 1 到 3 在表中。
从 4 到 6 不在表中。
从 7 到 8 在表中。
9 不在表中。
10 在表中。
解题思路:
(1)使用两个变量,一个@id用来记录logid,可以比较当下log_id与之前log_id的差值,判断是否连续。一个@num用来储存连续状态
select log_id,
case when @id = log_id - 1 then @num := @num
else @num := @num + 1
end num,
@id := log_id
from Logs,(select @num := 0,@id := null)t
上述输出结果如下所示
输入:{"headers":{"Logs":["log_id"]},"rows":{"Logs":[[1],[2],[3],[7],[8],[10]]}}
输出:{"headers": ["log_id", "num", "@prev_id := log_id"], "values": [[1, "1", 1], [2, "1", 2], [3, "1", 3], [7, "2", 7], [8, "2", 8], [10, "3", 10]]}
(2)得到上述结果后，用num字段分组，最小log_id为start_id，最大log_id为end_id。
select min(log_id) start_id,
max(log_id) end_id
from
(
select log_id,
case when @id = log_id - 1 then @num := @num
else @num := @num + 1
end num,
@id := log_id
from Logs,(select @num := 0,@id := null)t
)t2
group by num
03
报告系统状态的连续日期
Table: Failed
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| fail_date | date |
+--------------+---------+
该表主键为 fail_date。
该表包含失败任务的天数.
Table: Succeeded
+--------------+---------+
| Column Name | Type |
+--------------+---------+
| success_date | date |
+--------------+---------+
该表主键为 success_date。
该表包含成功任务的天数.
系统 每天 运行一个任务。每个任务都独立于先前的任务。任务的状态可以是失败或是成功。
编写一个 SQL 查询 2019-01-01 到 2019-12-31 期间任务连续同状态 period_state 的起止日期（start_date 和 end_date）。即如果任务失败了，就是失败状态的起止日期，如果任务成功了，就是成功状态的起止日期。
最后结果按照起始日期 start_date 排序
查询结果样例如下所示:
Failed table:
+-------------------+
| fail_date |
+-------------------+
| 2018-12-28 |
| 2018-12-29 |
| 2019-01-04 |
| 2019-01-05 |
+-------------------+
Succeeded table:
+-------------------+
| success_date |
+-------------------+
| 2018-12-30 |
| 2018-12-31 |
| 2019-01-01 |
| 2019-01-02 |
| 2019-01-03 |
| 2019-01-06 |
+-------------------+
Result table:
+--------------+--------------+--------------+
| period_state | start_date | end_date |
+--------------+--------------+--------------+
| succeeded | 2019-01-01 | 2019-01-03 |
| failed | 2019-01-04 | 2019-01-05 |
| succeeded | 2019-01-06 | 2019-01-06 |
+--------------+--------------+--------------+
结果忽略了 2018 年的记录，因为我们只关心从 2019-01-01 到 2019-12-31的记录
从 2019-01-01 到 2019-01-03 所有任务成功，系统状态为 "succeeded"。
从 2019-01-04 到 2019-01-05 所有任务失败，系统状态为 "failed"。
从 2019-01-06 到 2019-01-06 所有任务成功，系统状态为 "succeeded"。
解题思路:核心方法与上题一样
(1)使用union all合并两个表结果(相当于比第二题多了个state列)
(2)剩下思路同第二题,但是要维护一个@state状态，只有当日期连续增加，同时@sate不变的时候才能保持num不变
select period_state,
min(task_date) as start_date,
max(task_date) as end_date
from
(
select period_state,task_date,
case when datediff(task_date,@date) = 1 and @state = period_state then @num := @num
else @num := @num + 1
end num,
@date := task_date,
@state := period_state
from
(
select 'succeeded' as period_state,success_date as task_date
from Succeeded
where success_date between '2019-01-01' and '2019-12-31'
union all
select 'failed' as period_state,fail_date as task_date
from Failed
where fail_date between '2019-01-01' and '2019-12-31'
order by task_date
) t1,(select @date := null,@num := 0,@state := null) t2
)t3
group by period_state,num
order by start_date
04
体育馆流量
X 市建了一个新的体育馆，每日人流量信息被记录在这三列信息中：序号 (id)、日期 (visit_date)、 人流量 (people)。
请编写一个查询语句，找出人流量的高峰期。高峰期时，至少连续三行记录中的人流量不少于100。
例如，表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-08 | 188 |
+------+------------+-----------+
对于上面的示例数据，输出为：
+------+------------+-----------+
| id | visit_date | people |
+------+------------+-----------+
| 5 | 2017-01-05 | 145 |
| 6 | 2017-01-06 | 1455 |
| 7 | 2017-01-07 | 199 |
| 8 | 2017-01-08 | 188 |
+------+------------+-----------+
提示：每天只有一行记录，日期随着 id 的增加而增加。
利用自定义变量求解
1.定义一个变量cnt,如果people >= 100,加1，否则变为0，这样如果连续三行记录大于等于100，就会出现连续增加的序列
2.利用id是有序增加的,找出人流量的高峰期
select distinct s.*
from stadium s,(
select id,visit_date,people,
case when people >= 100 then @cnt := @cnt + 1
else @cnt := 0
end as cnt
from stadium,(select @cnt:=0)a
)b
where b.cnt >= 3 and s.id between b.id-b.cnt+1 and b.id
