概述

最近刷題時遇到了一些有意思的題，查詢連續出現多次的數或者最長的連續序列，而且出現了多次，對于這種題的實現思想就是利用等差數列的思想來解決。

一個連續的序列減一個連續的序列的差值一定是相同的。

例如有一個整數序列A:[4,5,6,7,8,10,11,12,15,19] 可以看到其中有些數據是連續的，有些不是連續的。為了找出這些 連續的序列可以按照從小到大給這組數排序，得到一個排名序列B，如下： 
[1,2,3,4,5,6,7,8,9,10]利用序列A - 序列B得到的答案是[3,3,3,3,3,4,4,4,6,9]可以很明顯的看出只要是連續的值得到的差值都是相同的。

利用這個思想，就可以很方便的解決這類問題。

題目:體育館的人流量

表：Stadium
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| visit_date    | date    |
| people        | int     |
+---------------+---------+
visit_date 是該表中具有唯一值的列。
每日人流量信息被記錄在這三列信息中：序號 (id)、日期 (visit_date)、 人流量 (people)
每天只有一行記錄，日期隨著 id 的增加而增加編寫解決方案找出每行的人數大于或等于 100 且 id 連續的三行或更多行記錄。返回按 visit_date 升序排列 的結果表。查詢結果格式如下所示。示例 1:輸入：
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-09 | 188       |
+------+------------+-----------+
輸出：
+------+------------+-----------+
| id   | visit_date | people    |
+------+------------+-----------+
| 5    | 2017-01-05 | 145       |
| 6    | 2017-01-06 | 1455      |
| 7    | 2017-01-07 | 199       |
| 8    | 2017-01-09 | 188       |
+------+------------+-----------+
解釋：
id 為 5、6、7、8 的四行 id 連續，并且每行都有 >= 100 的人數記錄。
請注意，即使第 7 行和第 8 行的 visit_date 不是連續的，輸出也應當包含第 8 行，因為我們只需要考慮 id 連續的記錄。
不輸出 id 為 2 和 3 的行，因為至少需要三條 id 連續的記錄。

題解

因為要求解id連續的三行或者更多行的記錄，且每行人數大于或等于100，這個就是典型的 求解 連續序列的問題。對于這類問題的核心思想就是利用等差數列來求解，一個連續遞增的序列減另一個連續遞增的序列，得到的差值是相同的。

步驟一：構造出一個連續序列

利用row_number() 函數，按照id的升序排列構造出一個連續的序列（1，2，3，4，5等等）并且過濾出人數大于等于100的行。再使用id減去row_number，會得到一個值

with grouped_table AS(SELECT id,id - row_number() over(order by id ASC) AS groupID,visit_date,peopleFROM Stadium where people >= 100
)

步驟二：找出符合條件的組的序號

第一步ID減去row_number后，如果id是連續的，那么得到的差值group_id一定是相等的，所以可以通過groupID來做分組查詢，統計出連續序列的長度，并過濾出連續的三行或者更多行的groupID。

hinted_group_table As(SELECT  groupID from grouped_table group by groupID having count(*) >= 3
)

步驟三：fetch結果，使用內連接過濾出符合條件的記錄。

SELECT id,visit_date,people FROM grouped_table T1,hinted_group_table T2 where T1.groupID = T2.groupID;

完整SQL

with grouped_table AS(SELECT id,id - row_number() over(order by id ASC) AS groupID,visit_date,peopleFROM Stadium where people >= 100
),hinted_group_table As(SELECT  groupID from grouped_table group by groupID having count(*) >= 3
)
SELECT id,visit_date,people FROM grouped_table T1,hinted_group_table T2 where T1.groupID = T2.groupID;

題目二：連續出現的數字

表：Logs+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| num         | varchar |
+-------------+---------+
在 SQL 中，id 是該表的主鍵。
id 是一個自增列。找出所有至少連續出現三次的數字。返回的結果表中的數據可以按 任意順序 排列。結果格式如下面的例子所示：示例 1:輸入：
Logs 表：
+----+-----+
| id | num |
+----+-----+
| 1  | 1   |
| 2  | 1   |
| 3  | 1   |
| 4  | 2   |
| 5  | 1   |
| 6  | 2   |
| 7  | 2   |
+----+-----+
輸出：
Result 表：
+-----------------+
| ConsecutiveNums |
+-----------------+
| 1               |
+-----------------+
解釋：1 是唯一連續出現至少三次的數字。

題解

步驟一：分區并構建連續的序列

按照num分區，并按照id排序，將相同數字劃分到統一窗口中，生成一個連續的排序序號。

with t1 AS (SELECT id, num, row_number() over(partition by num order by id) AS rn from  Logsorder by num asc,id asc
),

輸出

| id | num | rn |
| -- | --- | -- |
| 1  | 1   | 1  |
| 2  | 1   | 2  |
| 3  | 1   | 3  |
| 5  | 1   | 4  |
| 4  | 2   | 1  |
| 6  | 2   | 2  |
| 7  | 2   | 3  |

步驟二：使用id減去連續序列

select id,num,id-rn AS rn from t1

輸出

| id | num | rn |
| -- | --- | -- |
| 1  | 1   | 0  |
| 2  | 1   | 0  |
| 3  | 1   | 0  |
| 5  | 1   | 1  |
| 4  | 2   | 3  |
| 6  | 2   | 4  |
| 7  | 2   | 4  |

從上面的數據既可以看到數字1：連續出現的數字1的id減去連續的排名序列時的值都為0.

步驟三：分組統計得出次數

利用差值和num分組統計出現次數就可以得出連續出現的數字

select distinct num AS ConsecutiveNums from t1 group by num,rn having count(*) >= 3;

輸出

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

完整SQL

with t1 AS (SELECT id, num, row_number() over(partition by num order by id) AS rn from  Logsorder by num asc,id asc
),
t2 AS (select id,num,id-rn AS rn from t1
)select distinct num AS ConsecutiveNums from t2 group by num,rn having count(*) >= 3;

其他解法

這道題還有其他解法，因為只需要統計出 連續出現至少3次的數字，所以可以通過下移來解決，如下所示：

with  t1 AS (select id,num,lag(num,1) over(order by id) as prev_num from Logs
),
t2 as (select id,num,prev_num,lag(prev_num,1) over(order by id) as prev_num2 from t1
)
select distinct num as ConsecutiveNums from t2 where num-prev_num=0 and num-prev_num2=0;

當然這種方式 有一個局限性，僅僅適用于連續出現次數比較少的場景，需要下移n-1次。