sql连续问题通解基础(公式化解题简单易学)

于 2025-05-11 15:07:21 发布
阅读量1.2k 收藏 24
点赞数 32
文章标签： sql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/2202_75556204/article/details/147869258
版权

文章目录

前言

在我学习sql的过程中，连续性问题常常以各种各样的形态出现，在掌握构造辅助列方法前我一直使用join方法来解题，适用性差且做不同的题时因为没有结构化的解法思路很乱。在学习并总结了方法后使用row_number窗口函数构造辅助列的方法我总结出了一套公式化连续性问题解法。

一、连续性问题是什么？

连续性问题的形态各种各样，但基本只要题目中出现了“连续”这两个字时便可认为这是连续问题，可以使用row_number函数构造辅助列的方法解决，以下是leetcode中的一些连续性问题

leetcode180 中等
在这里插入图片描述

leetcode1225 困难
在这里插入图片描述

leetcode1454 中等
leetcode1454 中等

leetcode1811 中等leetcode1811 中等

可见连续问题的变体多种多样，给出的表的形态不固定，结果要求也是各不相同，但神奇的是博主解出它们的解法都是同一套思路。
本文接下来将以leetcode180以及leetcode1454为例给出上述连续问题以及连续问题的解题步骤

二、解题步骤

1.准备具有连续标识列，以及连续判断列的表

什么叫连续标识列，以及连续判断列呢？简而言之连续标识列是一个连续递增的等差数列，而连续判断列（连续判断的目标列）是我们需要判断连续的列（有时题目会需要我们判断连续的离散值），连续判断列不能为离散值必须为连续值，我们通过求他们的差来得到一个判断列，这个判断列中每个连续的部分得出的差会是相同的，从而让我们能够使用group by进行后续操作。听着有点抽象？往下看实例就会清晰很多了。

这里以leetcode180演示构造连续判断列。

leetcode180题目描述
题目
leetcode180数据结构
在这里插入图片描述

可见连续列id以及我们要判断连续的num列，id便是我们连续递增的连续标识列，但我们要判断的num列不是一个连续值，而是离散值，那么它就不是我们所需要的连续递增的等差数列，那么我们就需要自己去通过num列构造出我们的连续判断列

代码如下

SELECT
    id,
    num,
    ROW_NUMBER() OVER (
        PARTITION BY num    -- 按数值分组
        ORDER BY id          -- 按ID升序排列
    ) AS rn                  -- 生成分组内序号（连续判断列）
FROM
    Logs
ORDER BY
    id                       -- 最终按原始ID顺序输出便于观察

得到表
输出

至此leetcode180第一步完成，得到了连续标识列id，连续判断列rn。

连续判断对象列大部分题目都会给出（以日期的形式），但连续标识列给出的较少，往往需要我们自己去构造，而在构造连续标识列时基本用到的也是row_number函数，这里要注意，许多连续判断题目是有要求不同的类别（用户id，state等等）单独判断连续的，因此我们在构造连续标识列时就需要用partition字段去为不同的类别单独排序了。

这里以较为简单的leetcode1454为例，演示构造连续判断列

leetcode1454题目描述
leetcode1454

leetcode1454数据结构
leetcode1454
这是一道非常经典的连续登录问题，可见连续判断列已经给出（login_date）作为连续值的日期，我们可以直接拿来作为连续判断列用，但题目中的id不是连续标识列，而是用于分类判断连续的用户id，没有连续标识列，那么我们自己构造，要注意不同的用户要单独排序，因为不同的用户可能会在同一天登陆导致我们构造时出错

代码如下：

-- 生成临时表tmp，去重并排序以处理重复记录
WITH tmp AS (
    SELECT DISTINCT *
    FROM Logins
    ORDER BY id, login_date
)

-- 从临时表tmp中查询数据，生成连续标识列用于连续登录检测
SELECT
    id,
    login_date,
    ROW_NUMBER() OVER (
        PARTITION BY id              -- 按用户ID分组
        ORDER BY login_date          -- 按登录日期排序
    ) AS rn                          -- 生成连续序号（连续标识列）
FROM
    tmp

得到表
输出表
至此leetcode1454第一步完成，得到了连续标识列rn，连续判断列login_date。

在许多颇具难度的题的难点往往就是这第一步，这些题给出的表形态各异，而我们要将形态各异的表进行操作，得出我们需要的连续标识列以及连续判断列，而当我们得出了具有这两列的表后，后续几步的连续判断反而是最简单的了。

2.用连续标识列-连续判断对象列（减就完事了！）

这一步便是这个解法最妙的地方了
先以leetcode180为例，我们看一下连续标识列id减去连续判断列rn会得到什么.

leetcode180
在这里插入图片描述
可见在num列中连续的部分在id-rn列中具有相同的值。

而在leetcode1454中，我们要使用连续标识列rn减去连续判断列login_date就会发现一个问题，rn是我们用row_number窗口函数构造出来的整数列，但login_date是日期型数值列，它们不能直接用减号处理，因此我们使用date_sub函数，并用interval day将rn转化为日期型数值进行处理。

代码如下

-- 生成用户登录序列分析表
WITH tmp AS (
    SELECT DISTINCT *  -- 去重重复登录记录
    FROM Logins
    ORDER BY id, login_date  -- 按用户和时间排序（便于观察）
)
 
SELECT 
    id,
    login_date,
    -- 生成连续登录序列号（关键逻辑）
    ROW_NUMBER() OVER (PARTITION BY id ORDER BY login_date ) AS rn,
    
    -- 创建连续日期分组标识（核心逻辑）
    DATE_SUB(
        login_date,
        INTERVAL ROW_NUMBER() OVER (PARTITION BY id ORDER BY login_date)  	
        DAY
    ) AS 'rn-login_date'
FROM tmp

leetcode1454
在这里插入图片描述
可见rn-login_date列是一列日期值，但同样的，在login_date列中连续的部分在rn-login_date列的值是相同的。

但是注意！由于有多个用户，我们要分别判断他们的连续登陆情况，我们在第一步构造列时用partition by进行了分组处理，因此在rn-login_date列中的非连续部分出现了相同的值，这就需要我们在下一步group by处理时加上分类（partition by）所用到的的字段，否则会混淆不同用户的连续登陆情况

3.group by 连续标识列-连续判断对象列与分组依据列

到了这一步，不同的题目要求不同，因此我们只以leetcode180与leetcode1454为例讲解使用group by与having进行筛选连续n次的方法。除此之外部分变式题（如leetcode1225、leetcode1285）还有求连续的起始与结束时间等要求，但方法都不外乎是group by后使用聚合函数进行select或having筛选。

leetcode180，经过前面的步骤所得到的表
在这里插入图片描述
我们先尝试通过只group by id-rn列看一下为什么我们必须同时group by id-rn与num两个字段

只group by id-rn

SELECT 
    num ConsecutiveNums
FROM (
    -- 生成连续数字的分组标识
    SELECT 
        *,
        id - ROW_NUMBER() OVER (
            PARTITION BY num  -- 按数字分组
            ORDER BY id       -- 按ID顺序生成行号
        ) AS id_rn  -- 辅助列（连续标识列-连续判断列）
    FROM Logs
    ORDER BY id  -- 保证原始顺序（方便观察）
) a
GROUP BY id_rn  -- 只按辅助列分组
having count(*)>=3

在这里插入图片描述
可见在第18个测试用例出错,观察一下为什么
在这里插入图片描述
可见在不同的num中存在相同的id-rn，group by时没加上字段num导致我们将不同组的num当作同一个num来计算了连续，因此我们必须在group by时增加我们前面partition by所用到的字段num

正确示范 同时group by id-rn,num

SELECT 
    num ConsecutiveNums
FROM (
    -- 生成连续数字的分组标识
    SELECT 
        *,
        id - ROW_NUMBER() OVER (
            PARTITION BY num  -- 按数字分组
            ORDER BY id       -- 按ID顺序生成行号
        ) AS id_rn  -- 辅助列（连续标识列-连续判断列）
    FROM Logs
    ORDER BY id  -- 保证原始顺序（方便观察）
) a
GROUP BY id_rn,num  -- 同时按辅助列与num（partition by所用字段）分组
having count(*)>=3

在这里插入图片描述
可见group by+having筛选出了所有连续三次以上的数字

leetcode1454同理 同时group by id, ld_rn

-- 查询连续登录超过5天的用户
WITH a AS (
    -- 预处理数据：去重并排序登录记录
    SELECT DISTINCT 
        id, 
        login_date
    FROM Logins
    ORDER BY id, login_date  -- 方便观察
)
 
SELECT DISTINCT  -- 去重最终结果
    b.id,
    ac.name      -- 获取用户名
FROM (
    SELECT 
        id,
        login_date,
        DATE_SUB(
            login_date,
            INTERVAL ROW_NUMBER() OVER ( -- 生成连续标识列
                PARTITION BY id          -- 按用户分组
                ORDER BY login_date      -- 按登录日期排序
            ) DAY
        ) AS ld_rn  -- 连续标识列-连续判断列
    FROM a
) b
LEFT JOIN Accounts ac 
    ON b.id = ac.id  -- 关联用户信息表
GROUP BY id, ld_rn   -- 同时按用户和连续日期组分组
HAVING COUNT(ld_rn) >=5  -- 筛选连续5天以上的组

成功通过!
在这里插入图片描述

重点：一定要同时group by连续标识列-连续判断列与分组依据两个字段！

总结

至此，我们完成了leetcode1454与leetcode180两个连续判断题目，其他题目连续判断的步骤与这两题别无二致，但给出的表五花八门，接下来会单独发贴讲解具有代表性的一部分。

总结一下连续判断步骤
1.准备具有连续标识列，以及连续判断列的表
2.用连续标识列减去连续判断列构造辅助列
3.同时group by 辅助列与分组依据列，配合聚合函数完成题目要求

以上便是我解决连续问题的通解，谢谢阅读！

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值