【LeetCode601. 体育馆的人流量】MySQL 使用lag，lead得到该行记录所在连续段长度
目录
- 题目地址
- 代码
题目地址
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