力扣【SQL连续问题】

发布于：2025-02-11 ⋅ 阅读:(35) ⋅ 点赞:(0)

思路：
①看是否需要对原表进行筛选：

当值num和id不是同一列时，如果要判断和返回的是连续的id，则需要先在原表中使用WHERE子句，根据num做筛选，从而先根据符合要求的值num，筛选出符合条件的记录（如603.连续空余座位）；

当值num和id不是同一列时，如果要判断和返回的是连续值num，则不需要进行筛选（如180. 连续出现的数字 方法二）

②做id和其排名之间，或者排名与排名之间的差diff；

只要值有自己专门的id列（即 id 不是 值本身），那么一定是用id和排名、或者排名与排名相减。
（如603.连续空余座位 和 180. 连续出现的数字 方法二）。

除非值本身就是id(此时，id 和 值 为一列)，才能是值本身和排名相减。
如 1285. 找到连续区间的开始和结束数字

③再使用“等差数列-等差数列=固定值”的原理，判断题目要求的字段连续时的情况；

有时候要求的是连续的id，如603；
有时候要求的是连续的值num，如180. 方法二

④看如何返回题目要求的字段的全部连续值。

当值num和id不是同一列时，且要判断和返回的是连续的id，那么就要再使用一次计数聚合窗口函数COUNT。
注意该计数聚合窗口函数COUNT中，使用PARTITION BY diff子句即可，没有ORDER BY id的子句。（如603.连续空余座位）；

当值num和id不是同一列时，且要判断和返回的是连续值num，那么就要在分组键中同时使用值num和diff，并且使用HAVING子句判断组中的个数，再在SELECT子句中使用DISTINCT对值num去重，从而返回值num
（如180. 连续出现的数字 方法二）；

180. 连续出现的数字

在这里插入图片描述
在这里插入图片描述

方法一

-- 方法一：
SELECT DISTINCT a.num 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 a.num = b.num AND b.num = c.num

-- 加 DISTINCT 是为了对连续出现四次及以上的数字进行去重

-- 只使用lag窗口函数时
select 
    distinct t.num as ConsecutiveNums 
from
(
    select
        num,
        lag(num, 1) over(order by id) as num1,
        lag(num, 2) over(order by id) as num2
    from Logs 
) t
where t.num = t.num1 and t.num1 = t.num2;

在这里插入图片描述

-- 使用LAG和LEAD窗口函数时
select 
    distinct t.num as ConsecutiveNums 
from
(
    select
        num, 
        lag(num, 1, null) over(order by id) as lag_num, 
        lead(num, 1, null) over(order by id) as lead_num
    from Logs
) t
where t.num = t.lag_num and t.num = t.lead_num;

在这里插入图片描述

方法二（id和排名做差）

-- 方法二完整解法
/* 注意此题中的难点有两个：
①值num和id不是同一列，所以要用其id和其排名相减；
②要返回num值本身，所以除了diff，还要将num写在聚合键中，在返回时再用DISTINCT对num去重。*/

SELECT DISTINCT num AS ConsecutiveNums
FROM (SELECT a.id, a.num, a.id-a.rk AS diff
        FROM (SELECT id, num, RANK() OVER(PARTITION BY num ORDER BY id) AS rk
                FROM Logs) a) b
GROUP BY num, b.diff 
HAVING COUNT(*) >= 3

（1）值 一般是窗口函数PARTITION BY中使用的 不是差值diff的 分组键。

（2） 值的id 可以是值本身，也可以不是。

即： ① 值的id 和 值本身，两个可以为一列。
如1285. 找到连续区间的开始和结束数字

值的id 和 值本身为同一列时，值/id只能是数字类型。

当值是连续的时候，返回该连续的值本身，也就是值的id.

1285没有返回所有的连续值本身，而是仅返回了最小的连续值，和最大的连续值。

除非值本身就是id(此时，id 和 值 为一列)，才能是值本身和排名相减。
如 1285. 找到连续区间的开始和结束数字

② 值的id 和 值本身 也可以是不同列，即 值有自己专门的id列。
如本题180. 连续出现的数字（值是num字段，id是id）、
603.连续空余座位（值是free字段，id是seat_id）、1225.报告系统状态的连续日期（值是构建出来的state字段，id是日期）。

值有自己专门的id列时，id可以是数字类型，也可以是日期类型。

当值是连续的时候，返回的可能是该连续的值本身（如本题180），也可能是值的id（如603和1225）.

只要值有自己专门的id列（即 id 不是 值本身），那么一定是用id和排名、或者排名与排名相减。

当值有自己专门的id列时（即 id 不是 值本身），值不能与排名直接相减。

原因：
值 和 排名的差值，系统会默认为非负数，当出现小于0的情况时，将会返回错误 “ BIGINT UNSIGNED value is out of range”.

在这里插入图片描述
在这里插入图片描述

当值有自己专门的id列时（即 id 不是 值本身）：

  1. 要么是id和排名相减;

如本题180. 连续出现的数字（值是num字段，id是id）
在这里插入图片描述

603.连续空余座位（值是free字段，id是seat_id），方法二(做差)、
1225.报告系统状态的连续日期（值是构建出来的state字段，id是日期）做差方法一

  1. 要么是用两次排名窗口函数，用排名和排名相减。

1225.报告系统状态的连续日期（值是构建出来的state字段，id是日期）做差方法二

（3）不管 值本身 是不是 其id，值都可以和使用lag和lead窗口函数时，值字段的返回值进行相减。
如 613. 直线上的最近距离。

603. 连续空余座位

在这里插入图片描述
在这里插入图片描述

方法一

只针对表a中所有空闲的a.seat_id，不是空闲的a.seat_id不会被选中。
一次判断，决定了至少两个a.seat_id要被输出。

-- 方法一：
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

-- a.seat_id+1 = b.seat_id 和 a.free = 1 AND b.free = 1，保证了 表a中空闲a.seat_id 后面一个seat_id 是空闲的。
-- a.seat_id-1 = c.seat_id 和 a.free = 1 AND c.free = 1，保证了 表a中空闲a.seat_id 前面一个seat_id 是空闲的。

/* 对表a中每个空闲a.seat_id，只要其前面或者后面的 seat_id 也是空闲的，
那么不但会输出该空闲的a.seat_id值，而且也一定会输出 其前面或者后面的 seat_id 值。
即 所有的连续 且 空闲的a.seat_id值，都会被输出。 */

/* 当表a中的某个空闲a.seat_id，因为其前面的seat_id是空闲的而被输出时(a.seat_id-1 = c.seat_id 和 a.free = 1 AND c.free = 1)，
那么当判断到a.seat_id-1时，a.seat_id-1一定会因为与其后面的a.seat_id是连续的而被输出。

当表a中的某个空闲a.seat_id，因为与其后面的 seat_id 是连续的而被输出时(a.seat_id+1 = b.seat_id 和 a.free = 1 AND b.free = 1)，
那么当判断到a.seat_id+1时，a.seat_id+1一定会因为与其前面的a.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

自联结之后的执行结果：
在这里插入图片描述

有超过三个连续的a.seat_id时，也可以全部输出。
在这里插入图片描述

方法二（id和排名做差）

只针对表a中所有空闲的a.seat_id，不是空闲的a.seat_id不会被选中。

-- 方法二：
SELECT b.seat_id  -- 输出b.cnt > 1的所有b.seat_id
FROM (SELECT a.seat_id AS seat_id, COUNT(*) OVER(PARTITION BY rkdiff) AS cnt -- COUNT还是聚合窗口函数，并没有减少表中的记录数
        FROM (SELECT seat_id, free, ROW_NUMBER() OVER(ORDER BY seat_id) AS rk,
            seat_id - ROW_NUMBER() OVER(ORDER BY seat_id) AS rkdiff
                FROM Cinema
                WHERE free = 1) a ) b
WHERE b.cnt > 1 
ORDER BY b.seat_id

表a中要先选出空闲的a.seat_id，然后对每个空闲的a.seat_id，进行从大到小的排序。
对连续且空闲的a.seat_id，其a.seat_id（3、4、5）与其排名（2、3、4）之间的差值总是固定的。

原理：等差数列的值，减去等差数列的值，结果才能是固定的。

在这里插入图片描述

COUNT还是聚合窗口函数，并没有减少表中的记录数。
（不加ORDER BY seat_id子句时，返回的）每组中的COUNT聚合窗口函数的结果值cnt，都是相同的。

在这里插入图片描述

在这里插入图片描述

这里的 COUNT聚合窗口函数 不能加ORDER BY seat_id子句。
否则将返回的是每个rkdiff值组中，每个seat_id自身的次序值，而不是整组中的记录值。
在这里插入图片描述

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) reference
      FROM Logs) a 
GROUP BY a.reference
ORDER BY start_id

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

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

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

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

在这里插入图片描述
在这里插入图片描述
两种做差方法：

做差方法一（id和排名做差）

-- 方法一：
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_date
        FROM (SELECT fail_date AS date, "failed" AS state
                FROM Failed
                UNION
                SELECT success_date AS date, "succeeded" AS state
                FROM 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 state
   FROM Failed
UNION
SELECT success_date AS date, "succeeded" AS state
   FROM 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_date
 FROM (SELECT fail_date AS date, "failed" AS state
         FROM Failed
         UNION
         SELECT success_date AS date, "succeeded" AS state
         FROM 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 rk2
    from (select fail_date as date,'failed' as state
            from failed
            union all
            select success_date as date,'succeeded' as state
            from 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 rk2
    from (select fail_date as date,'failed' as state
            from failed
            union all
            select success_date as date,'succeeded' as state
            from 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 rk2
    from (select fail_date as date,'failed' as state
            from failed
            union all
            select success_date as date,'succeeded' as state
            from succeeded) a ) b

在这里插入图片描述

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

因为没有：做值与其排名之间的差，再使用“等差数列-等差数列=固定值”的原理，判断连续。
在这里插入图片描述
在这里插入图片描述

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

-- 方法二：lag取值窗口函数，返回窗口内当前行之前的第N行记录字段x的值。
select  x - lag(x) over(order by x) as shortest
  from point
 order by shortest
 limit 1
 offset 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

在这里插入图片描述

网站公告

今日签到

点亮在社区的每一天
去签到

热门文章

最新发布