你知道如何去求解連續(xù)區(qū)間嗎？

求解連續(xù)區(qū)間是數(shù)據(jù)分析、數(shù)據(jù)倉庫筆試面試中常考的SQL題目，今天為各位小伙伴分享筆試面試題，期待各位拿到心儀的offer或有所收獲！

1、連續(xù)出現(xiàn)的數(shù)字

編寫一個(gè) SQL 查詢，查找所有至少連續(xù)出現(xiàn)三次的數(shù)字。

+----+-----+| Id | Num |+----+-----+| 1 | 1 || 2 | 1 || 3 | 1 || 4 | 2 || 5 | 1 || 6 | 2 || 7 | 2 |+----+-----+例如，給定上面的Logs表，1是唯一連續(xù)出現(xiàn)至少三次的數(shù)字。

+-----------------+| ConsecutiveNums |+-----------------+| 1 |+-----------------+

解法一:自連接三次,篩選l1,l2,l3的id連續(xù)且Num相等的行

注:如果數(shù)字連續(xù)出現(xiàn)超過三次,Num就會出現(xiàn)重復(fù)值,所以需要使用distinct去重

該方法實(shí)現(xiàn)簡單，但是時(shí)間復(fù)雜度較大，且如果改成連續(xù)出現(xiàn)4,5..10次，代碼就會非常冗余，不具備可拓展性。

select distinct l1.Num as ConsecutiveNumsfrom Logs l1,Logs l2,Logs l3where l2.Id = l1.Id + 1and l3.Id = l2.Id + 1and l1.Num = l2.Numand l2.Num = l3.Num

解法二:使用自定義變量進(jìn)行條件判斷

速度比解法一快，且適用于任一連續(xù)次數(shù)

select distinct Num ConsecutiveNumsfrom( 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

解法三:窗口函數(shù)

select distinct Num as ConsecutiveNumsfrom( select Num,Id, lag(Id,2)over(partition by Num order by Id) as prev from Logs)twhere t.Id = t.prev + 2

2、找到連續(xù)區(qū)間的開始和結(jié)束數(shù)字

表:Logs

+---------------+---------+| Column Name | Type |+---------------+---------+| log_id | int |+---------------+---------+ id 是上表的主鍵。上表的每一行包含日志表中的一個(gè) ID。后來一些 ID 從 Logs 表中刪除。編寫一個(gè) SQL 查詢得到 Logs 表中的 連續(xù)區(qū)間的開始數(shù)字和結(jié)束數(shù)字。 將查詢表按照 start_id 排序。查詢結(jié)果格式如下面的例子： Logs 表：

+------------+| log_id |+------------+| 1 || 2 || 3 || 7 || 8 || 10 |+------------+結(jié)果表：

+------------+--------------+| start_id | end_id |+------------+--------------+| 1 | 3 || 7 | 8 || 10 | 10 |+------------+--------------+結(jié)果表應(yīng)包含 Logs 表中的所有區(qū)間。 從 1 到 3 在表中。 從 4 到 6 不在表中。 從 7 到 8 在表中。 9 不在表中。 10 在表中。

解題思路:

(1)使用兩個(gè)變量,一個(gè)@id用來記錄logid,可以比較當(dāng)下log_id與之前l(fā)og_id的差值,判斷是否連續(xù)。一個(gè)@num用來儲存連續(xù)狀態(tài)

select log_id, case when @id = log_id - 1 then @num := @num else @num := @num + 1 end num, @id := log_idfrom Logs,(select @num := 0,@id := null)t

上述輸出結(jié)果如下所示

輸入:{"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)得到上述結(jié)果后，用num字段分組，最小log_id為start_id，最大log_id為end_id。

select min(log_id) start_id, max(log_id) end_idfrom( 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)t2group by num

3、報(bào)告系統(tǒng)狀態(tài)的連續(xù)日期

Table: Failed

+--------------+---------+| Column Name | Type |+--------------+---------+| fail_date | date |+--------------+---------+該表主鍵為 fail_date。 該表包含失敗任務(wù)的天數(shù). Table: Succeeded

+--------------+---------+| Column Name | Type |+--------------+---------+| success_date | date |+--------------+---------+該表主鍵為 success_date。該表包含成功任務(wù)的天數(shù). 系統(tǒng)每天運(yùn)行一個(gè)任務(wù)。每個(gè)任務(wù)都獨(dú)立于先前的任務(wù)。任務(wù)的狀態(tài)可以是失敗或是成功。編寫一個(gè) SQL 查詢 2019-01-01 到 2019-12-31 期間任務(wù)連續(xù)同狀態(tài) period_state 的起止日期（start_date 和 end_date）。即如果任務(wù)失敗了，就是失敗狀態(tài)的起止日期，如果任務(wù)成功了，就是成功狀態(tài)的起止日期。 最后結(jié)果按照起始日期start_date排序查詢結(jié)果樣例如下所示: 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 |+--------------+--------------+--------------+結(jié)果忽略了2018年的記錄，因?yàn)槲覀冎魂P(guān)心從2019-01-01到2019-12-31的記錄從 2019-01-01 到 2019-01-03 所有任務(wù)成功，系統(tǒng)狀態(tài)為 "succeeded"。從 2019-01-04 到 2019-01-05 所有任務(wù)失敗，系統(tǒng)狀態(tài)為 "failed"。從 2019-01-06 到 2019-01-06 所有任務(wù)成功，系統(tǒng)狀態(tài)為 "succeeded"。

解題思路:核心方法與上題一樣

(1)使用union all合并兩個(gè)表結(jié)果(相當(dāng)于比第二題多了個(gè)state列)

(2)剩下思路同第二題,但是要維護(hù)一個(gè)@state狀態(tài)，只有當(dāng)日期連續(xù)增加，同時(shí)@sate不變的時(shí)候才能保持num不變

select period_state, min(task_date) as start_date, max(task_date) as end_datefrom( 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)t3group by period_state,numorder by start_date

4、體育館流量

X 市建了一個(gè)新的體育館，每日人流量信息被記錄在這三列信息中：序號 (id)、日期 (visit_date)、 人流量 (people)。

請編寫一個(gè)查詢語句，找出人流量的高峰期。高峰期時(shí)，至少連續(xù)三行記錄中的人流量不少于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 |+------+------------+-----------+

對于上面的示例數(shù)據(jù)，輸出為：

+------+------------+-----------+| 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.定義一個(gè)變量cnt,如果people >= 100,加1，否則變?yōu)?，這樣如果連續(xù)三行記錄大于等于100，就會出現(xiàn)連續(xù)增加的序列

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)bwhere b.cnt >= 3 and s.id between b.id-b.cnt+1 and b.id

責(zé)任編輯：lq6