복붙노트

[SQL] SQL은 - 주로 주문 순차적으로 직렬 INT 값 누락 찾기

SQL

SQL은 - 주로 주문 순차적으로 직렬 INT 값 누락 찾기

나는 그들이 반드시 순서대로 도착하지 않지만 고유 한 정수 ID의 순서가 완전히, 하루의 끝에 표시됩니다있는 메시지 기반 시스템을 관리 할 수 ​​있습니다.

나는 SQL을 사용하여이 시리즈에서 누락 ID를 찾는 데 도움을 찾고 있습니다. 내 열 값은 다음과 같은 경우, 어떻게 내가이 경우 6,이 순서에서 누락하고있는 id를 찾을 수 있습니까?

최소 및 최대 각 실행에 따라 다를 것이다, 그래서 순서는, 매일 임의의 지점에서 시작하고 끝납니다. 거기에 어떤 정규식을 통해 펄 배경 I에서 오는.

ids
1
2
3
5
4
7
9
8
10

도움이 많이 주시면 감사하겠습니다.

편집 : 우리는 오라클을 실행

EDIT2 : 모든 주셔서 감사합니다. 나는 사무실에서 다음 주 당신의 솔루션을 통해 실행됩니다.

EDIT3 : 나는 시간이 ORIG_ID 원래 id 컬럼 인 및 MY_TABLE 소스 테이블 인 상태, 아래처럼 뭔가 것에 대해 정착했다. 가까이 내 데이터의보고에서, 문자열 단지 숫자 데이터 넘어 다양한 사례가있다. 어떤 경우에는 숫자가 아닌 문자의 접두사 나 접미사가있다. 다른 사람에서 숫자 ID로 혼합 대시 또는 공백이있다. 나는 별개 포함 있도록이 외에도, ID는 주기적으로 여러 번 나타납니다.

내가 특별히 숫자가 아닌 문자를 제거의 최적의 경로와 관련하여, 더 이상의 입력을 부탁드립니다.

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

해결법

  1. ==============================

    1.난 거기에 가본 적있어.

    난 거기에 가본 적있어.

    오라클 :

    전에 아래 언급, 그러나 지금 사이트를 기억하지 않는 동안 당신이 구글에서 'GAP 분석 "을 검색 할 수는 그물에이 매우 유용 쿼리를 발견했다.

    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
  2. ==============================

    2.이것은 때때로 제외 조인이라고합니다. 즉, 조인 할 수와 일치하는이없는 경우에만 행을 반환하려고합니다.

    이것은 때때로 제외 조인이라고합니다. 즉, 조인 할 수와 일치하는이없는 경우에만 행을 반환하려고합니다.

    SELECT t1.value-1
FROM ThisTable AS t1
LEFT OUTER JOIN ThisTable AS t2
  ON t1.id = t2.value+1
WHERE t2.value IS NULL

    이 항상 MIN 값이 될 것이다 적어도 하나 개의 행을보고 있습니다.

    두 개 이상의 숫자의 차이가있는 경우 또한, 그것은 단지 하나 개의 누락 된 값을보고합니다.

  3. ==============================

    3.내가 PostgreSQL의 있으리라 믿고있어, 그래서 당신은 당신의 DBMS를 언급하지 않았다 :

    내가 PostgreSQL의 있으리라 믿고있어, 그래서 당신은 당신의 DBMS를 언급하지 않았다 :

    select aid as missing_id
from generate_series( (select min(id) from message), (select max(id) from message)) as aid
  left join message m on m.id = aid
where m.id is null;

    이것은 (하나보다 큰 간격 포함) 테이블의 최소 및 최대 ID 사이의 시퀀스에서 누락 된 값을보고합니다

    psql (9.1.1)
Type "help" for help.

postgres=> select * from message;
 id
----
  1
  2
  3
  4
  5
  7
  8
  9
 11
 14
(10 rows)


postgres=> select aid as missing_id
postgres-> from generate_series( (select min(id) from message), (select max(id) from message)) as aid
postgres->   left join message m on m.id = aid
postgres-> where m.id is null;
 missing_id
------------
          6
         10
         12
         13
(4 rows)
postgres=>
  4. ==============================

    4.내가 MySQL의에 적용, 그것은했다 ..

    내가 MySQL의에 적용, 그것은했다 ..

    mysql> select * from sequence;
+--------+
| number |
+--------+
|      1 |
|      2 |
|      4 |
|      6 |
|      7 |
|      8 |
+--------+
6 rows in set (0.00 sec)

mysql> SELECT t1.number - 1 FROM sequence AS t1 LEFT OUTER JOIN sequence AS t2 O
N t1.number = t2.number +1 WHERE t2.number IS NULL;
+---------------+
| t1.number - 1 |
+---------------+
|             0 |
|             3 |
|             5 |
+---------------+
3 rows in set (0.00 sec)
  5. ==============================

    5.

    SET search_path='tmp';

DROP table tmp.table_name CASCADE;
CREATE table tmp.table_name ( num INTEGER NOT NULL PRIMARY KEY);
-- make some data
INSERT INTO tmp.table_name(num) SELECT generate_series(1,20);
-- create some gaps
DELETE FROM tmp.table_name WHERE random() < 0.3 ;

SELECT * FROM table_name;

-- EXPLAIN ANALYZE
WITH zbot AS (
    SELECT 1+tn.num  AS num
    FROM table_name tn
    WHERE NOT EXISTS (
        SELECT * FROM table_name nx
        WHERE nx.num = tn.num+1
        )
    )
, ztop AS (
    SELECT -1+tn.num  AS num
    FROM table_name tn
    WHERE NOT EXISTS (
        SELECT * FROM table_name nx
        WHERE nx.num = tn.num-1
        )
    )
SELECT zbot.num AS bot
    ,ztop.num AS top
FROM zbot, ztop
WHERE zbot.num <= ztop.num
AND NOT EXISTS ( SELECT *
    FROM table_name nx
    WHERE nx.num >= zbot.num
    AND nx.num <= ztop.num
    )
ORDER BY bot,top
    ;

    결과:

    CREATE TABLE
INSERT 0 20
DELETE 9
 num 
-----
   1
   2
   6
   7
  10
  11
  13
  14
  15
  18
  19
(11 rows)

 bot | top 
-----+-----
   3 |   5
   8 |   9
  12 |  12
  16 |  17
(4 rows)

    참고 : 재귀 CTE (그리고 아마도 짧은)도 가능합니다.

    업데이트 : 여기에 재귀 CTE가 온다 ... :

    WITH RECURSIVE tree AS (
    SELECT 1+num AS num
    FROM table_name t0
    UNION
    SELECT 1+num FROM tree tt
    WHERE EXISTS ( SELECT *
        FROM table_name xt
        WHERE xt.num > tt.num
        )
    )
SELECT * FROM tree
WHERE NOT EXISTS (
    SELECT *
    FROM table_name nx
    WHERE nx.num = tree.num
    )
ORDER BY num
    ;

    결과 : (동일한 데이터)

     num 
-----
   3
   4
   5
   8
   9
  12
  16
  17
  20
 (9 rows)
  6. ==============================

    6.

    select student_key, next_student_key
      from (
    select student_key, lead(student_key) over (order by student_key) next_fed_cls_prgrm_key
      from student_table
           )
where student_key <> next_student_key-1;

    7. from https://stackoverflow.com/questions/8364247/sql-find-missing-int-values-in-mostly-ordered-sequential-series by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 바로 단어를 검색 MYSQL | 철자 오류를 수정  (0) 2020.07.09
[SQL] 데이터는 SQL 테이블에 영구적으로 저장되지  (0) 2020.07.09
[SQL] 열이 시작하거나 문자로 끝날 수있을 때 부호로 캐스트 수치 MySQL의 정렬 VARCHAR 열을 사용하여  (0) 2020.07.09
[SQL] 당신은 어떻게 변수를 사용하여 동적 쿼리에서 IN 절을 지정합니까?  (0) 2020.07.09
[SQL] 테이블에 연속 3 개 행을 비교  (0) 2020.07.09
,

티스토리툴바