[PostgreSQL] 순차적으로 증가하는 숫자형 컬럼 중간에 비어있는 값(누락된 값)을 조회

PostgreSQL에서 특정 숫자형 컬럼이 순차적으로 증가하는 경우, 중간에 비어있는 값(누락된 값)을 찾으려면 generate_series()를 활용할 수 있어.

방법 1: generate_series() 활용

WITH missing_numbers AS (
    SELECT generate_series(MIN(id), MAX(id)) AS num
    FROM your_table
)
SELECT num
FROM missing_numbers
LEFT JOIN your_table ON missing_numbers.num = your_table.id
WHERE your_table.id IS NULL;

 

✅ 설명

1. generate_series(MIN(id), MAX(id))를 사용해 id 컬럼의 최소값부터 최대값까지의 연속된 숫자 시리즈를 생성.
2. 이 시리즈를 원래 테이블과 LEFT JOIN 한 후, id IS NULL인 값을 찾으면 누락된 번호만 조회 가능.

---

방법 2: LAG() 으로 직접 비교

만약 ID가 반드시 오름차순으로 정렬되어 있다고 가정하면, LAG()를 활용하는 것도 가능해.

SELECT prev_id + 1 AS missing_id
FROM (
    SELECT id, LAG(id) OVER (ORDER BY id) AS prev_id
    FROM your_table
) subquery
WHERE id - prev_id > 1;

 

✅ 설명

1. LAG(id) OVER (ORDER BY id)를 사용해 이전 행의 id 값을 가져옴.
2. 현재 id - prev_id > 1 인 경우, 그 사이에 누락된 값이 있다는 의미이므로 prev_id + 1 을 결과로 반환.

이 방법은 중간에 한 개의 값만 빠진 경우만 감지할 수 있고, 여러 개 연속으로 빠진 경우는 한 개만 출력되므로 주의해야 해.