【LeetCode601. 体育馆的人流量】MySQL使用lag，lead得到连续段的：开始标志，结束标志，分组号，长度

目录

• 题目地址
• 代码
• 用该模板代码可以解决的题目

题目地址

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

代码

with t1 as(
    SELECT * from stadium where people>=100 order by id asc
)
,
Ranked AS (
    SELECT *,
           LAG(id, 1, id - 2) OVER (ORDER BY id) AS prev_id,  -- 这里-2和+2是为了做哨兵
           LEAD(id, 1, id + 2) OVER (ORDER BY id) AS next_id  -- 这里-2和+2是为了做哨兵
    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
)
,
comprehensive_statistic as(
    SELECT *,
    COUNT(id) OVER (PARTITION BY group_num) AS segment_length
    FROM GroupsWithNumber
    ORDER BY id
)



select id,visit_date,people from 
comprehensive_statistic
where segment_length>=3

用该模板代码可以解决的题目

https://leetcode.cn/problems/consecutive-available-seats/description/ 603. 连续空余座位
https://leetcode.cn/problems/find-the-start-and-end-number-of-continuous-ranges/description/ 1285. 找到连续区间的开始和结束数字