SQL-在大多数有序的序列系列中查找缺失的int值 [英] SQL - Find missing int values in mostly ordered sequential series

问题描述

我管理一个基于消息的系统，在该系统中，唯一的整数ID序列将在一天结束时完全表示出来，尽管它们不一定会按顺序到达.

I manage a message based system in which a sequence of unique integer ids will be entirely represented at the end of the day, though they will not necessarily arrive in order.

我正在寻找有关使用SQL查找本系列中缺少的ID的帮助.如果我的列值如下所示，如何找到此序列中缺少的ID，在这种情况下为6?

I am looking for help in finding missing ids in this series using SQL. If my column values are something like the below, how can I find which ids I am missing in this sequence, in this case 6?

序列将每天在任意点开始和结束，因此每次运行的最小值和最大值将有所不同.来自Perl背景，我通过其中的一些正则表达式.

The sequence will begin and end at an arbitrary point each day, so min and max would differ upon each run. Coming from a Perl background I through some regex in there.

ids
1
2
3
5
4
7
9
8
10

我们将不胜感激.

我们运行oracle

We run oracle

Edit2:谢谢.下周我将在办公室讨论您的解决方案.

Thanks all. I'll be running through your solutions next week in the office.

Edit3:我暂时选择了类似以下的内容，其中ORIG_ID是原始ID列，而MY_TABLE是源表.在仔细查看我的数据时，除了字符串中的数字数据以外，还有多种情况.在某些情况下，会有非数字字符的前缀或后缀.在其他情况下，数字ID中混有破折号或空格.除此之外，id会定期出现多次，因此我将其包括在内.

I settled for the time being on something like the below, with ORIG_ID being the original id column and MY_TABLE being the source table. In looking closer at my data, there are a variety of cases beyond just number data in a string. In some cases there is a prefix or suffix of non-numeric characters. In others, there are dashes or spaces intermixed into the numeric id. Beyond this, ids periodically appear multiple times, so I included distinct.

我将不胜感激，尤其是在去除非数字字符的最佳途径方面.

I would appreciate any further input, specifically in regard to the best route of stripping out non-numeric characters.

SELECT 
   CASE
      WHEN NUMERIC_ID + 1 = NEXT_ID - 1
         THEN TO_CHAR( NUMERIC_ID + 1 )
      ELSE TO_CHAR( NUMERIC_ID + 1 ) || '-' || TO_CHAR( NEXT_ID - 1 )
   END
   MISSING_SEQUENCES
   FROM
   (
      SELECT
         NUMERIC_ID,
         LEAD (NUMERIC_ID, 1, NULL)
            OVER 
            (
               ORDER BY
                 NUMERIC_ID
                 ASC
            )
            AS NEXT_ID
         FROM 
         (
             SELECT
                DISTINCT TO_NUMBER( REGEXP_REPLACE(ORIG_ID,'[^[:digit:]]','') ) 
                AS NUMERIC_ID
             FROM MY_TABLE
         )
    ) WHERE NEXT_ID != NUMERIC_ID + 1

推荐答案

我去过那里.

FOR ORACLE:

FOR ORACLE:

我前一段时间在网上发现了这个极其有用查询并记录下来，但是现在我不记得该站点了，您可以搜索 "GAP ANALYSIS" 在Google上.

I found this extremely useful query on the net a while ago and noted down, however I don't remember the site now, you may search for "GAP ANALYSIS" on Google.

SELECT   CASE
             WHEN ids + 1 = lead_no - 1 THEN TO_CHAR (ids +1)
          ELSE TO_CHAR (ids + 1) || '-' || TO_CHAR (lead_no - 1)
         END
             Missing_track_no
   FROM   (SELECT   ids,
                    LEAD (ids, 1, NULL)
                     OVER (ORDER BY ids ASC)
                        lead_no
             FROM   YOURTABLE
             )
   WHERE   lead_no != ids + 1

在这里，结果是:

MISSING _TRACK_NO
-----------------
       6

如果存在多个缺口，例如2,6,7,9，则为:

MISSING _TRACK_NO
-----------------
        2
       6-7
        9

这篇关于SQL-在大多数有序的序列系列中查找缺失的int值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！