专栏首页SQL实现SQL 获取定长连续子序列

SQL 获取定长连续子序列

有一张表 savior,它的表结构及数据如下:

CREATE TABLE `savior` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `status` int NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8
    id  status  
------  --------
     1         1
     2         1
     3         0
     4         0
     5         0
     6         1
     7         0
     8         0
     9         0
    10         0
    11         0
    12         1
    13         1
    14         0
    15         0

savior 表有两个字段,id 是主键,设置了自动递增;status 表示状态,它只有 0/1 两种状态。

要求:从 savior 表中获取状态为 0 的 id,并且这些 id 能够组成长度为 3 的连续子序列。

比如,id = 3、4、5 的数据,它们的状态为 0,且它们构成的序列长度正好为 3。满足这个规则的还有(7,8,9)、(8,9,10)、(9,10,11)构成的序列。

最终期望能获取的结果:

subseq      
--------
3~5     
7~9     
8~10    
9~11    

在历史的文章里有提到过判断连续子序列的方法,就是根据目标字段的排序规则生成序号,目标字段减去它对应的序号得到的的结果相同的数据则说明它们是连续的子序列。

请看演示:

SELECT 
  id,
  rn,
  id - rn AS rs 
FROM
  (SELECT 
    id,
    row_number () over (
  ORDER BY id) AS rn 
  FROM
    savior 
  WHERE STATUS = 0) t ;
  

    id      rn      rs  
------  ------  --------
     3       1         2
     4       2         2
     5       3         2
     7       4         3
     8       5         3
     9       6         3
    10       7         3
    11       8         3
    14       9         5
    15      10         5

id 为 3 ~ 5 是一个连续子序列,7 ~ 11 是一个连续子序列,14 ~ 15 是一个连续子序列。

由于我们只要获取长度为 3 的子序列,根据判断连续子序列的规则,反过来说,如果一组数据是连续子序列,那么目标字段和它对应的序号分别加上固定的值,目标字段得到的结果和新序号的差值仍和做加法操作前保持一致。

比如,在 rs = 2 的子序列中,id = 3 和 rn = 1 分别加上 2,得到新的 id = 5 和 rn = 3,5 - 3 仍是 2 。

因此,可以将这个固定值作为定长子序列的长度参照(子序列的长度 = 固定值 + 1)。在这个需求里,这个固定值取值 2 。

WITH cte AS 
(SELECT 
  *,
  row_number() over (
ORDER BY id) AS rn 
FROM
  savior 
WHERE STATUS = 0) 
SELECT 
  CONCAT_WS('~', a.id, b.id) AS subseq 
FROM
  cte a 
  INNER JOIN cte b 
    ON a.id + 2 = b.id 
    AND a.rn + 2 = b.rn 

本文分享自微信公众号 - SQL实现(gh_684ee9235a26),作者:zero

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-12-08

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 最长上升连续子序列

    一份执着✘
  • 397. 最长上升连续子序列

    给定一个整数数组(下标从 0 到 n-1, n 表示整个数组的规模),请找出该数组中的最长上升连续子序列。(最长上升连续子序列可以定义为从右到左或从左到右的序列...

    和蔼的zhxing
  • 最长连续递增子序列问题

    给定一个长度为N的数组,给定一个长度为N的数组,找出一个最长的单调自增子序列(不一定连续,但是顺序不能乱)。例如:给定一个长度为6的数组A{5, 6, 7, 1...

    xujjj
  • SQL 获取最长的日期序列

    有一张学习打卡表 his_sign 表,简单起见,只设置了两个字段(id,create_ts),一个是主键,另一个是打卡时间。his_sign 表的数据如下,我...

    白日梦想家
  • 【简单】最长连续不重复子序列

    给定一个长度为 n 的整数序列,请找出最长的不包含重复数字的连续子序列,输出它的长度。

    CSTHenry
  • 最长连续不重复子序列(双指针)

    给定一个长度为n的整数序列,请找出最长的不包含重复数字的连续区间,输出它的长度。输入格式

    dejavu1zz
  • 1-9 最长连续递增子序列 (20 分)

    版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。

    韩旭051
  • pta 习题集 5-5 最长连续递增子序列 (dp)

    Count the Sheep Time Limit: 3000/1500 MS (Java/Others)    Memory Limit: 65536/6...

    ShenduCC
  • Excel公式技巧22: 从字符串中提取指定长度的连续数字子串

    本文给出了一种从可能包含若干个不同长度的数字的字符串中提取指定长度的数字的解决方案。在实际的工作表中,存在着许多此类需求,例如从字符串中获取6位数字账号。

    fanjy
  • Excel公式练习49: 获取指定区域中2个及以上连续数值并按顺序排列

    导语:继续研究来自于excelxor.com的案例。建议结合本文阅读原文,会了解更多的细节,会有更大的收获。

    fanjy
  • LintCode 最长上升连续子序列题目样例分析1(普通解法)分析2(使用队列)

    给定一个整数数组(下标从 0 到 n-1, n 表示整个数组的规模),请找出该数组中的最长上升连续子序列。(最长上升连续子序列可以定义为从右到左或从左到右的序列...

    desperate633
  • 算法工程师-SQL进阶:神奇的自连接与子查询

    自连接与子查询是SQL中非常重要的两项技术,自连接是针对相同的表的联结方法,是SQL语言面向集合编程思想的集中体现,而子查询有很多变式,关联子查询技术是在实际中...

    小萌哥
  • Spark SQL/Hive实用函数大全

    本篇文章主要介绍Spark SQL/Hive中常用的函数,主要分为字符串函数、JSON函数、时间函数、开窗函数以及在编写Spark SQL代码应用时实用的函数算...

    大数据学习与分享
  • 资源等待类型sys.dm_os_wait_stats

    动态管理视图  sys.dm_os_wait_stats  返回执行的线程所遇到的所有等待的相关信息。可以使用该聚合视图来诊断 SQL Server 以及特定查...

    欢醉
  • MySQL数据库(良心资料)

    我们所说的数据库泛指“关系型数据库管理系统(RDBMS-Relational database management system)”,即“数据库服务器”。

    阮键
  • 【21】进大厂必须掌握的面试题-65个SQL面试

    一个 数据库管理系统 (DBMS)是一个软件应用程序与用户,应用程序和数据库本身交互,以捕获和分析数据。

    全栈程序员站长
  • 数据库优化 6. 启用MySQL查询缓存

    昨天遇到一个问题, 200万的表里查询9万条数据, 耗时达63秒. 200万数据不算多, 查询9万也还好. 怎么用了这么长的时间呢? 问题是一句非常简单的sql...

    用户7798898
  • 自制小工具大大加速MySQL SQL语句优化(附源码)

    优化SQL,是DBA常见的工作之一。如何高效、快速地优化一条语句,是每个DBA经常要面对的一个问题。在日常的优化工作中,我发现有很多操作是在优化过程中必不可少的...

    宜信技术学院
  • MySQL进阶笔记-3(MySQL优化)

    在应用的的开发过程中,由于初期数据量小,开发人员写 SQL 语句时更重视功能上的实现,但是当应用系统正式上线后,随着生产数据量的急剧增长,很多 SQL 语句开始...

    千羽

扫码关注云+社区

领取腾讯云代金券