当前位置：首页 > news >正文

力扣【SQL连续问题】

news 2025/1/8 9:45:34

180. 连续出现的数字

在这里插入图片描述

SELECT DISTINCT if(a.num = b.num AND b.num = c.num,a.num,null) AS ConsecutiveNums
FROM Logs a
LEFT OUTER JOIN Logs b
ON a.id+1 = b.id
LEFT OUTER JOIN Logs c
ON a.id+2 = c.id
WHERE if(a.num = b.num AND b.num = c.num,a.num,null) IS NOT NULL

603. 连续空余座位

在这里插入图片描述

SELECT a.seat_id AS seat_id
FROM Cinema a
LEFT OUTER JOIN Cinema b
ON a.seat_id + 1 = b.seat_id
LEFT OUTER JOIN Cinema c
ON a.seat_id - 1 = c.seat_id
WHERE a.free = 1 AND (b.free = 1 OR c.free = 1) 
ORDER BY seat_id

-- 执行速度慢
SELECT DISTINCT a.seat_id AS seat_id
FROM Cinema a
LEFT OUTER JOIN Cinema b
ON ABS(a.seat_id - b.seat_id) = 1
WHERE a.free = 1 AND b.free = 1
ORDER BY seat_id

613. 直线上的最近距离（其实这不是连续问题，仅为窗口函数的简单应用）

在这里插入图片描述

-- 方法一：
SELECT MIN(ABS(a.x - b.x)) AS shortest
FROM Point a
LEFT OUTER JOIN Point b
ON a.x <> b.x

-- 方法二：
select  x - lag(x) over(order by x) as shortestfrom pointorder by shortestlimit 1offset 1

-- 方法二中的中间输出 
select  x, lag(x) over(order by x) AS lag_x,    -- 取比每个 x 小的值中，但又最靠近x（数值上最大的）的那一个值 x - lag(x) over(order by x) as shortest  -- 计算每个 x 与其 lag_x 之间的距离from point

在这里插入图片描述

1285. 找到连续区间的开始和结束数字

在这里插入图片描述

①先给每个数进行排名

②用这些数减去自己的排名，如果减了之后的结果是一样的，说明这几个数是连续的
（原理：等差数列的值，减去等差数列的值，结果才能是一样的。）

在这里插入图片描述

③用logid减去排名得出来的数进行group by，也就是把连续的数全都放在一个一个小组里面，求出每个小组的最大值和最小值就可以了

SELECT MIN(a.log_id) AS START_ID, MAX(a.log_id) AS END_ID
FROM (SELECT log_id, ROW_NUMBER() OVER (ORDER BY log_id ASC) rn,log_id - ROW_NUMBER() OVER (ORDER BY log_id ASC) referenceFROM Logs) a 
GROUP BY a.reference
ORDER BY start_id

1225. 报告系统状态的连续日期

需要观察数据，不断尝试，找到方法。

一般都是对要求的连续的字段，减去其排名。
原理仍然是等差数列减去等差数列，不管值是什么数据类型（日期或者数字），是多少（0或者其它），它们都是一样的。

可以倒推，如果同一个状态中，每个值是连续的，那么每个值与其排名之间，会存在什么样的关系。
如果不连续之后，这样的关系又会发生什么变化。

在这里插入图片描述

两种做差方法：

做差方法一

-- 方法一：
SELECT  b.state AS period_state, MIN(date) AS start_date, MAX(date) AS end_date 
FROM (SELECT a.*,ROW_NUMBER() OVER(PARTITION BY a.state ORDER BY a.date) AS rk,SUBDATE(a.date, ROW_NUMBER() OVER(PARTITION BY a.state ORDER BY a.date)) AS diff_dateFROM (SELECT fail_date AS date, "failed" AS stateFROM FailedUNIONSELECT success_date AS date, "succeeded" AS stateFROM Succeeded) a) b 
WHERE b.date BETWEEN '2019-01-01' AND '2019-12-31'         
GROUP BY b.state, b.diff_date  
--注意此处使用的聚合键是哪两个。要清楚b表中每个字段对表中记录的聚合程度，从高到低：state——diff_date——date/rk
ORDER BY start_date

-- 表a执行结果
SELECT fail_date AS date, "failed" AS stateFROM Failed
UNION
SELECT success_date AS date, "succeeded" AS stateFROM Succeeded

在这里插入图片描述

-- 表b执行结果（还没有对表b进行分组）
SELECT a.*,ROW_NUMBER() OVER(PARTITION BY a.state ORDER BY a.date) AS rk,SUBDATE(a.date, ROW_NUMBER() OVER(PARTITION BY a.state ORDER BY a.date)) AS diff_dateFROM (SELECT fail_date AS date, "failed" AS stateFROM FailedUNIONSELECT success_date AS date, "succeeded" AS stateFROM Succeeded) a--要清楚b表中每个字段对表中记录的聚合程度，从高到低：state——diff_date——date/rk

【可以发现在每个状态state中，连续的日期，日期值减去排名rk，差值diff_date值总是相同的。
所以要想到用state 和差值diff 进行分组。】

在这里插入图片描述

做差方法一

-- 方法二：
select  b.state AS period_state, MIN(date) AS start_date, MAX(date) AS end_date
FROM (select a.date AS date, a.state AS state,row_number() over(partition by a.state order by a.date asc) as rk1,row_number() over(order by a.date asc) as rk2from (select fail_date as date,'failed' as statefrom failedunion allselect success_date as date,'succeeded' as statefrom succeeded) a) b
WHERE YEAR(b.date) = 2019  --或者 DATE_FORMAT(b.date, "%Y") = 2019               
GROUP BY state, b.rk2 - b.rk1
ORDER BY start_date

-- 表a执行结果同上-- 表b执行结果
select a.date AS date, a.state AS state,row_number() over(partition by a.state order by a.date asc) as rk1,row_number() over(order by a.date asc) as rk2from (select fail_date as date,'failed' as statefrom failedunion allselect success_date as date,'succeeded' as statefrom succeeded) a

在这里插入图片描述
不分组时，对表b中的 rk2 和 rk1 做差（差值diff = b.rk2 - b.rk1）的结果：
【可以发现在每个状态state中，连续的日期，差值diff值总是相同的。
所以要想到用state 和差值diff 进行分组。】

select b.date AS date, b.state AS period_state, b.rk1, b.rk2, b.rk2 - b.rk1 AS diff
from (select a.date AS date, a.state AS state,row_number() over(partition by a.state order by a.date asc) as rk1,row_number() over(order by a.date asc) as rk2from (select fail_date as date,'failed' as statefrom failedunion allselect success_date as date,'succeeded' as statefrom succeeded) a ) b