Leetcode刷SQL 4、体育馆的人流量

X 市建了一个新的体育馆，每日人流量信息被记录在这三列信息中：序号 (id)、日期 (date)、人流量 (people)。

请编写一个查询语句，找出高峰期时段，要求连续三天及以上，并且每天人流量均不少于100。

例如，表 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       |
+------+------------+-----------+

对于上面的示例数据，输出为：

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

Note:
每天只有一行记录，日期随着 id 的增加而增加。

思路：
这一题同样很简单，和上一题连续数字是非常类似的，首先做一轮查询，记录连续大于100的天数，默认为1，如果下一天符合则+1，否则重置；同时给一个标识，用于标识出当前连续段的特征值。
得到以上结果，我们就可以查出所有连续次数大于等于3的的天数记录及其标识，这样也可以得到需要的结果了。

SELECT
  e.id,
  e.date,
  e.people
FROM
  (SELECT
    id,
    s.date,
    @sdate1 := (
      CASE
        WHEN s.people >= 100
        THEN @sdate1
        ELSE @sdate1 + 1
      END
    ) AS sdate,
    s.people
  FROM
    stadium s,
    (SELECT
      @sdate1 := 0) b) e,
  (SELECT
    c.sdate
  FROM
    (SELECT
      id,
      s.date,
      @sdate := (
        CASE
          WHEN s.people >= 100
          THEN @sdate
          ELSE @sdate + 1
        END
      ) AS sdate,
      s.people
    FROM
      stadium s,
      (SELECT
        @sdate := 0) b) c
  GROUP BY c.sdate
  HAVING COUNT(c.sdate) > 3) d
WHERE d.sdate = e.sdate
  AND e.people >= 100

由于mysql没有with as，这里复用部分的子查询就显得很长很难看了。
我用的方法是比较死板的遍历查找，只能击败88.48%，其他提交的普遍采用以id+1,id+2为条件做全表查询，更巧妙吧。

SELECT DISTINCT
  s1.*
FROM
  stadium AS s1,
  stadium AS s2,
  stadium AS s3
WHERE (
    (s1.id + 1 = s2.id
      AND s1.id + 2 = s3.id)
    OR (s1.id - 1 = s2.id
      AND s1.id + 1 = s3.id)
    OR (s1.id - 2 = s2.id
      AND s1.id - 1 = s3.id)
  )
  AND s1.people >= 100
  AND s2.people >= 100
  AND s3.people >= 100
ORDER BY s1.id

Leetcode刷SQL 4、体育馆的人流量

推荐阅读更多精彩内容