专栏首页SQL实现SQL 确定序列里缺失值的范围

SQL 确定序列里缺失值的范围

有一个序列表 seq,它有一个存整数序列值的字段叫作 id,原本序列的值是连续递增的,但因某些原因,有的值丢失了,我们希望能通过 SQL 找出缺失值的范围。

先来构造有缺失值的 seq 表,可以用 SQL 派生出这个表。

WITH seq AS(
SELECT 1 AS id
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
UNION ALL SELECT 8
UNION ALL SELECT 12
UNION ALL SELECT 13
UNION ALL SELECT 15
UNION ALL SELECT 18
UNION ALL SELECT 19
UNION ALL SELECT 20
)

我们观察数据可知,seq 表中目前最大的数是 20,缺失的值有:4、9、10、11、14、16、17。这些缺失值的范围是:

start

stop

4

4

9

11

14

14

16

17

上表就是我们需要通过 SQL 生成的结果。

接下来说说实现 SQL 的思路。

第一,把 seq 表中 id 字段的每个值 + 1 后再和 seq 表中的数比较,如果不在 seq 表中,说明该数 + 1 是缺失值,且是一段缺失值的范围的起始值。

SELECT 
  s.id + 1 AS START 
FROM
  seq AS s 
  LEFT JOIN seq AS r 
    ON s.id = r.id - 1 
WHERE r.id IS NULL 

查出来的结果如下,21 不应该出现,后面我们会加条件过滤掉。

 START  
--------
       4
       9
      14
      16
      21

第二,在找到所有缺失数据的范围的起始值后,再从 seq 表中找到大于起始值的最小值 upper,upper - 1 就是该段缺失范围的结束值。比如对于缺失值 9,在 seq 表中能找到大于 9 的最小值是 12,12 - 1 = 11 就是该段缺失数据的范围的结束值。

SELECT 
  (SELECT 
    MIN(id) - 1 
  FROM
    seq AS xx 
  WHERE xx.id > s.id) AS STOP 
FROM
  seq AS s 
  LEFT JOIN seq AS r 
    ON s.id = r.id - 1 
WHERE r.id IS NULL 

下面是找到的结束值,因为 21 原本不存在,所以它的结束值是 NULL。

  STOP  
--------
       4
      11
      14
      17
  (NULL)

最后,加上过滤条件,完整的 SQL 如下:

SELECT 
  START,
  STOP 
FROM
  (SELECT 
    s.id + 1 AS START,
    (SELECT 
      MIN(id) - 1 
    FROM
      seq AS xx 
    WHERE xx.id > s.id) AS STOP 
  FROM
    seq AS s 
    LEFT JOIN seq AS r 
      ON s.id = r.id - 1 
  WHERE r.id IS NULL) AS t 
WHERE STOP IS NOT NULL 

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

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

原始发表时间:2020-05-30

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

我来说两句

0 条评论
登录 后参与评论

相关文章

  • SQL 生成斐波那契数列

    你没看错标题,在这篇文章我将会给大家介绍使用 SQL 生成斐波那契数列,并且不需要借助任何物理表。

    白日梦想家
  • 不用 UNION 操作符实现 UNION 的效果

    当我们要合并两个表或者多个表的结果时,可使用 UNION ALL 或者 UNION 操作符, UNION 和 UNION ALL 的区别在于前者会对结果集去重...

    白日梦想家
  • SQL 统计用户留存

    统计出头部客户、腰部客户、尾部客户在上个月(2020-06-01 ~ 2020-06-30)的留存情况。

    白日梦想家
  • Python全栈(一)基础之12.面向对象(1)

    面向对象OOP 什么是对象: 对象就是内存中存储指定数据的一块区域。 实际上对象就是一个容器,专门用来存数据。 程序运行的通俗解释: 代码存在硬盘,C...

    cutercorley
  • 教程 | 理解XGBoost机器学习模型的决策过程

    机器之心
  • JVM与字节码——2进制流字节码解析 原

    本位将详细介绍字节码的2进制结构和JVM解析2进制流的规范。规范对字节码有非常严格的结构要求,其结构可以用一个JSON来描述:

    随风溜达的向日葵
  • web性能优化之:no-cache与must-revalidate深入探究

    稍微了解HTTP协议的前端同学,相比对Cache-Control不会感到陌生,性能优化时经常都会跟它打交道。

    IMWeb前端团队
  • 国内有哪些好的刷题网站?

    CoderAfterWork靠写代码挣钱去浪... 刷题也就这么点地方 1、Leetcode 鼎鼎大名的Leetcode,据不完全统计在上面被刷过的题可以围绕地...

    前朝楚水
  • CXK恶作剧勒索2.0分析

    勒索软件的流行程度近几年来广为人知,于是就有人喜欢模仿,这种炫技行为真的是用错了地方。该样本之前1.0版本已分析过,而本次分析的是2.0版本,发现新增与改变了一...

    网e渗透安全部
  • 为什么部分看起来不太复杂的网站,比如Facebook需要大量顶尖高手来开发?

    网站的复杂度不是只能看表面的文章,网站背后是强大的负载能力,以及强大的数据处理能力才是网站背后的技术核心,像国内的大型互联网公司都有自己的网站的研发团队,而且网...

    程序员互动联盟

扫码关注云+社区

领取腾讯云代金券