• 题目地址
• 代码

题目地址

https://leetcode.cn/problems/human-traffic-of-stadium/description/

代码

# # Write your MySQL query statement below

# # 本质上就是连续签到问题呗

# SELECT  Version() # 8.0.33，用户变量编程用不了

with t1 as(
    SELECT * from stadium where people>=100 order by id asc
)
,
Ranked AS (
    SELECT *,
           LAG(id, 1, id - 1) OVER (ORDER BY id) AS prev_id,
           LEAD(id, 1, id + 1) OVER (ORDER BY id) AS next_id
    FROM t1
)
, 
Segments AS (
    SELECT *, 
           CASE 
               WHEN id - prev_id > 1 THEN 1
               ELSE 0
           END AS is_start,
           CASE 
               WHEN next_id - id > 1 THEN 1
               ELSE 0
           END AS is_end
    FROM Ranked
)
, 
GroupsWithNumber AS (
    SELECT *,
           SUM(is_start) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS group_num
    FROM Segments
)



select id,visit_date,people from 
    (SELECT *,
    COUNT(id) OVER (PARTITION BY group_num) AS segment_length
    FROM GroupsWithNumber
    ORDER BY id) t2
where segment_length>=3