一道很有意思的sql题

X city built a new stadium, each day many people visit it and the stats are saved as these columns: id, date, people

Please write a query to display the records which have 3 or more consecutive rows and the amount of people more than 100(inclusive).

For example, the table 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 |
+——+————+———–+
For the sample data above, the output is:

+——+————+———–+
| id | date | people |
+——+————+———–+
| 5 | 2017-01-05 | 145 |
| 6 | 2017-01-06 | 1455 |
| 7 | 2017-01-07 | 199 |
| 8 | 2017-01-08 | 188 |
+——+————+———–+

针对这道题，本人想了以下三种方案：
方法一：
思路：
如果某一条数据和前一条数据id的差值为1，那么证明中间没有断层。
如果我们添加一列标识列类似于：
id people 标识列
1 101 a
2 122 a
4 131 b
5 324 b
6 235 b
7 214 b
…
那么就可以统计标识列快速找到答案。
经过思考，我们可以用id - rownum来做标识列。
SELECT C.ID,C.DATA_DT,C.PEOPLE FROM
(SELECT B.ID,B.DATA_DT,B.PEOPLE,COUNT(B.BSL) OVER (PARTITION BY B.BSL) AS BSL FROM
(SELECT A.ID,A.DATA_DT,A.PEOPLE,CASE WHEN A.ID - LAG(A.ID,1) OVER (ORDER BY A.ID) = 1 OR LEAD(A.ID,1) OVER (ORDER BY A.ID) - A.ID = 1
THEN A.ID-A.RN ELSE NULL END AS BSL FROM
(select id,DATA_DT,people,rownum AS RN from TEMP WHERE PEOPLE >=100 ORDER BY ID) A
) B
WHERE B.BSL IS NOT NULL
) C WHERE C.BSL >= 3 ORDER BY ID;
方法二：思路：
这个方案是方法一的优化，既然id-rownum可以作为标识列，那么我们直接取之后分组就行了。
SELECT B.ID,B.DATA_DT,B.PEOPLE FROM
(SELECT A.ID,A.DATA_DT,A.PEOPLE,COUNT(A.ID-A.RN) OVER (ORDER BY A.ID-A.RN) AS CNT FROM
(select id,DATA_DT,people,rownum AS RN from TEMP WHERE PEOPLE >=100 ORDER BY ID) A
) B
WHERE B.CNT>=3 ORDER BY B.ID;
方法三：思路：
我们只需要判断任意三个相邻的数都相差1即可。
select DISTINCT a.id, a.datA_DT, a.people
from temp a,TEMP b ,TEMP c
where a.people >= 100 and b.people >= 100 and c.people >= 100
and ((a.id -b.id = 1 and a.id - c.id = 2)
or (a.id - b.id = -1 and a.id - c.id = 1)
or(a.id - b.id = -1 and a.id - c.id = -2))
order by a.id;

经过测试，以上三种方法都可以实现。（注：方法一和方法二需要略微调整）