[SQL] MariaDB/MySQL 테이블 일련번호 중간에 빈 값 찾기

테이블에 존재하는 순서 번호 값 중에서 빈 값을 찾는 쿼리를 작성하겠습니다.

 

순차적으로 번호가 1번부터 20번까지 존재한다고 했을 때 5번, 11번 ~ 14번, 17번 ~ 18번 이렇게 빈 값이 존재할 경우 찾아내는 방법과 빈 값에 대한 범위를 찾는 방법 그리고 마지막으로 어느 정도 비어있는지 확인 가능합니다.

 

1. 샘플 데이터

  - 샘플 데이터를 생성하고, 임시 데이터를 삽입합니다.

DROP TABLE EST_SEQUENCE;

CREATE TABLE EST_SEQUENCE (ID INT NOT NULL PRIMARY KEY) ENGINE=INNODB;

INSERT INTO EST_SEQUENCE(ID)
    SELECT 1
    UNION ALL SELECT 2
    UNION ALL SELECT 3
    UNION ALL SELECT 4
    UNION ALL SELECT 6
    UNION ALL SELECT 7
    UNION ALL SELECT 8
    UNION ALL SELECT 9
    UNION ALL SELECT 10
    UNION ALL SELECT 15
    UNION ALL SELECT 16
    UNION ALL SELECT 19
    UNION ALL SELECT 20
;

 

2. 순차적 번호 중에서 빈 값 찾기

  - 아래 쿼리 중에서 seq_1_to_10000을 이용해서 로우 데이터를 생성하고, 실제 검수해야 될 테이블을 LEFT OUTER JOIN을 해서 확인합니다.

  - 실제 테이블 데이터 중에서 가장 큰 값을 비교해서 적용하면 성능적으로 개선됩니다.

  - "r_total_time_ms": 0.1657,  => 서브 쿼리 사용 할 때
  - "r_total_time_ms": 1.3515,  => 서브쿼리 사용 안 할 때 

SELECT   A.SEQ
FROM     seq_1_to_10000 A
         LEFT OUTER JOIN EST_SEQUENCE B ON (B.ID = A.SEQ)
WHERE    B.ID IS NULL
AND      A.SEQ <= (SELECT  MAX(SQ1.ID)   
                   FROM    EST_SEQUENCE SQ1
                  )
;

[결과]

|SEQ                 |
|--------------------|
|5                   |
|11                  |
|12                  |
|13                  |
|14                  |
|17                  |
|18                  |

 

3. 순차적 번호 중에서 빈 값에 대한 사이 값 찾기

SELECT   CONCAT(X.EXPECTED, IF(X.GOT-1>X.EXPECTED, CONCAT(' ~ ',X.GOT-1), '')) AS MISSING
FROM     (
         SELECT
                  @ROWNUM:=@ROWNUM+1 AS EXPECTED,
                  IF(@ROWNUM=B.ID, 0, @ROWNUM:=B.ID) AS GOT
         FROM     (SELECT @ROWNUM:=0) AS A
                  INNER JOIN EST_SEQUENCE B
         ORDER BY B.ID 
         ) AS X
WHERE X.GOT!=0;

[결과]

|MISSING                                                                                      |
|---------------------------------------------------------------------------------------------|
|5                                                                                            |
|11 ~ 14                                                                                      |
|17 ~ 18                                                                                      |

 

4. 순차적 번호 중에서 빈 값에 대한 수 구하기

SELECT   X.CURRENT_ID AS ID
        ,X.MIN_ID AS NEXT_ID
        ,(X.MIN_ID - X.CURRENT_ID) -1 AS MISSING_INBETWEEN
FROM     (
         SELECT   A1.ID AS CURRENT_ID , MIN(A2.ID) AS MIN_ID 
         FROM     EST_SEQUENCE  AS A1
         LEFT OUTER JOIN EST_SEQUENCE AS A2 ON A2.ID > A1.ID
         WHERE    A1.ID <= 100
         GROUP BY A1.ID
         ) AS X
WHERE    X.MIN_ID > X.CURRENT_ID + 1
;

[결과]

|ID         |NEXT_ID    |MISSING_INBETWEEN|
|-----------|-----------|-----------------|
|4          |6          |1                |
|10         |15         |4                |
|16         |19         |2                |