SQL中有查询序列的习惯用法吗

Question

我正在学习面试一份涉及大量SQL的工作。我注意到一些练习围绕着基于跨行序列返回值，我很想知道是否有标准的方法。类似于下面的子查询，我发现它对选择最大/最小值很有用：

( SELECT column FROM table ... ORDER BY column [DESC] LIMIT 1 )

下面是LeetCode的一个相关示例：

Table: Stadium
+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| id            | int     |
| visit_date    | date    |
| people        | int     |
+---------------+---------+
visit_date is the primary key for this table.

此表的每一行都包含体育场的访问日期和访问id以及访问期间的人数。没有两行具有相同的visit_date，并且随着id的增加，日期也会增加。

编写一个SQL查询，以显示具有连续id的三行或三行以上的记录，并且每个记录的人数大于或等于100。

按visit_date升序返回结果表。

查询结果格式如下例所示。

Stadium table:
+------+------------+-----------+
| 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       |
+------+------------+-----------+
Result table:
+------+------------+-----------+
| id   | visit_date | people    |
+------+------------+-----------+
| 5    | 2017-01-05 | 145       |
| 6    | 2017-01-06 | 1455      |
| 7    | 2017-01-07 | 199       |
| 8    | 2017-01-09 | 188       |
+------+------------+-----------+

我试图解决这个问题时使用了一个用户变量。下面的代码是我最好的解决方案：

SET @rowIndex = 0;
SELECT s1.id, s1.visit_date, s1.people
FROM ( SELECT @rowIndex:=@rowIndex+1 as rowIndex, s.id, s.visit_date, s.people
FROM Stadium as s
WHERE s.people >=100 ) as s1
GROUP BY rowIndex - s1.id, s1.id, s1.visit_date, s1.people
HAVING COUNT(s.id) >= 3
ORDER BY s1.visit_date

上面的查询在某个地方出现语法错误消息：

您的SQL语法有错误；查看手册与您的MySQL服务器版本相对应，以便使用正确的语法靠近'SELECT s1.id，s1.visit_date，s1.people FROM(SELECT@rowIndex：=@rowIndex+1作为'在第4行

有人喜欢根据序列选择行的方法吗？也许不那么重要的是，有人能发现我上面查询中的错误吗？

Answer 1

我将把它作为一个缺口和孤岛问题来解决。以下是一种使用窗口函数的方法：

select id, visit_date, people
from (
select s.*, count(*) over(partition by id - rn) cnt
from (
select s.*, row_number() over(order by id) rn
from stadium s
where people > 100
) s
) s
where cnt >= 3

这个想法是过滤出访问次数少于100次的天数，然后使用id和单调递增的秩之间的差来识别岛屿(访问次数超过100次的连续天数(。然后，我们可以保留只有3行以上的组。