【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. 找到连续区间的开始和结束数字