[SQL/오답] 데이터 무결성을 위한 복합 정규표현식 (LeetCode 3451 Hard)

1. Problem

서버 로그 데이터에서 유효하지 않은 IPv4 주소를 식별하고 그 빈도를 계산해야 한다. 유효하지 않은 조건은 세 가지다.

1. 마디(Octet)가 4개가 아닌 경우
2. 특정 마디의 숫자가 255를 초과하는 경우
3. 마디에 불필요한 앞자리 0(Leading Zeros)이 포함된 경우

단순한 패턴 매칭을 넘어 숫자의 자릿수와 크기 범위를 정규식으로 치환하는 논리가 핵심이다.

2. Solution

각 조건을 개별적인 정규표현식 패턴으로 정의하여 OR 연산으로 결합한다.

• 자릿수 제어: [0-9]{4,}를 통해 1000 이상의 모든 숫자를 효율적으로 필터링한다.
• 범위 수치화: 256~999 사이의 숫자를 정규식의 대괄호 패턴으로 나누어 정의한다.
• 경계 정의: \\b0[0-9]+를 통해 단독 '0'은 허용하되 '001'과 같은 중복 0을 식별한다.

3. Takeaway (DA의 데이터 검수 관점)

분석 전 단계에서 데이터의 신뢰성을 확보하기 위한 '방어적 쿼리' 작성 능력을 정립한다.

• 수량자({n,})의 전략적 활용: 크기 비교가 불가능한 정규식에서 자릿수를 기준으로 1000 이상의 값을 한 번에 솎아내는 방식은 매우 효율적이다. {4,}는 최소 4번 이상의 반복을 의미하며, 이는 IP 마디의 유효 범위를 즉각적으로 판단하는 기준이 된다.
• 구조적 결함 우선 파악: NOT REGEXP를 사용하여 IP 주소의 기본적인 구조(마디 4개)를 먼저 검증함으로써, 이후의 세부 패턴 검사에서 발생할 수 있는 연산 낭비를 줄인다.
• 데이터 품질과 분석가: 데이터 분석가는 분석 전 반드시 데이터 클렌징 과정을 거쳐야 한다. 정규표현식은 수만 건의 로그 데이터 중 오염된 데이터를 찾아내고 분석 결과의 왜곡을 방지하는 가장 강력한 무기이다.

https://leetcode.com/problems/find-invalid-ip-addresses/description/

Table:  logs

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| log_id      | int     |
| ip          | varchar |
| status_code | int     |
+-------------+---------+
log_id is the unique key for this table.
Each row contains server access log information including IP address and HTTP status code.
Write a solution to find invalid IP addresses. An IPv4 address is invalid if it meets any of these conditions:

Contains numbers greater than 255 in any octet
Has leading zeros in any octet (like 01.02.03.04)
Has less or more than 4 octets
Return the result table ordered by invalid_count, ip in descending order respectively. 

The result format is in the following example.

 

Example:

Input:

logs table:

+--------+---------------+-------------+
| log_id | ip            | status_code | 
+--------+---------------+-------------+
| 1      | 192.168.1.1   | 200         | 
| 2      | 256.1.2.3     | 404         | 
| 3      | 192.168.001.1 | 200         | 
| 4      | 192.168.1.1   | 200         | 
| 5      | 192.168.1     | 500         | 
| 6      | 256.1.2.3     | 404         | 
| 7      | 192.168.001.1 | 200         | 
+--------+---------------+-------------+
Output:

+---------------+--------------+
| ip            | invalid_count|
+---------------+--------------+
| 256.1.2.3     | 2            |
| 192.168.001.1 | 2            |
| 192.168.1     | 1            |
+---------------+--------------+
Explanation:

256.1.2.3 is invalid because 256 > 255
192.168.001.1 is invalid because of leading zeros
192.168.1 is invalid because it has only 3 octets
The output table is ordered by invalid_count, ip in descending order respectively.

 

1. 정답 쿼리

SELECT ip, COUNT(*) AS invalid_count 
FROM logs
WHERE ip NOT REGEXP '^[0-9]+\\.[0-9]+\\.[0-9]+\\.[0-9]+$'                # 마디가 4개인 정상 경우에 미해당
    OR ip REGEXP '([2][5][6-9]|[2][6-9][0-9]|[3-9][0-9][0-9]|[0-9]{4,})' # 255 초과 또는 4자리 이상에 해당
    OR ip REGEXP '\\b0[0-9]+'                                            # 0으로 시작하고 그 뒤에 숫자가 더 있는 경우
GROUP BY ip
ORDER BY invalid_count DESC, ip DESC;

2. 단계별 로직 상세 설명

출제자가 제시한 세 가지 조건을 정규표현식으로 어떻게 치환했는지 객관적으로 분석해 보겠습니다.

조건 1: 마디가 4개가 아닌 경우 (ip NOT REGEXP ...)

• 패턴: ^[0-9]+\.[0-9]+\.[0-9]+\.[0-9]+$
• 설명: 숫자 덩어리([0-9]+)가 점(\.)을 사이에 두고 정확히 4번 반복되는 구조인지 확인합니다. NOT REGEXP를 썼기 때문에, 마디가 3개이거나 5개인 경우를 모두 잡아냅니다.

조건 2: 어느 마디라도 255보다 큰 경우

이 부분이 정규식에서 가장 까다로운 범위 지정입니다. 숫자의 크기를 직접 비교할 수 없으므로 패턴으로 잡아야 합니다.

• [2][5][6-9]: 256 ~ 259
• [2][6-9][0-9]: 260 ~ 299
• [3-9][0-9][0-9]: 300 ~ 999
• [0-9]{4,}: 1000 이상의 모든 숫자
• 결과: 이 중 하나라도 걸리면(| 연산자) 255를 초과하는 유효하지 않은 IP가 됩니다.

조건 3: 앞에 0이 붙은 경우 (Leading Zeros)

• 패턴: \\b0[0-9]+
• 설명:
  • \\b: 단어 경계(Boundary)를 의미합니다. 즉, 마디의 시작점을 찾습니다.
  • 0: 숫자가 0으로 시작하는지 봅니다.
  • [0-9]+: 그 뒤에 숫자가 하나 이상 더 붙어 있는지 확인합니다.
  • 결과: 이 패턴은 192.168.0.1에서 0은 통과시키고, 192.168.001.1에서 001만 정확히 잡아냅니다.

---

잠깐! 
수량자(Quantifier)란? 

 

정규표현식에서 {n,}은 **"최소 n번 이상 반복된다"**는 의미를 가진 **수량자(Quantifier)**입니다.

1. 패턴의 분해와 해석

[0-9]{4,}는 크게 두 부분으로 나뉩니다.

• [0-9]: 0부터 9까지의 숫자 중 아무거나 하나를 의미합니다.
• {4,}: 앞의 패턴([0-9])이 최소 4번 이상 연속해서 나타나야 한다는 뜻입니다.

숫자 마디(Octet)와 대조해보면?

IP 주소의 한 마디는 보통 1, 10, 192처럼 1~3자리 숫자로 이루어집니다.

• 3자리 숫자: 최대 999입니다. (숫자가 3번 반복됨)
• 4자리 숫자: 최소 1000부터 시작합니다. (숫자가 4번 반복됨)
• 5자리 숫자: 10000 이상입니다. (숫자가 5번 반복됨)

따라서 {4,}는 **"숫자가 4글자, 5글자, 그 이상인 모든 경우"**를 다 포함하게 되므로, 자연스럽게 1000 이상의 모든 숫자를 찾아내는 필터가 됩니다.

2. 왜 IP 문제에서 이 패턴을 쓰나요? (Hard 난이도의 포인트)

IP 주소의 한 마디는 0부터 255 사이여야 합니다. 우리가 유효하지 않은(Invalid) IP를 찾을 때, 255를 넘는 숫자를 잡아야 하죠?

• 3자리 숫자 중: 256부터 999까지는 별도의 패턴([2][5][6-9] 등)으로 잡아야 합니다.
• 4자리 이상의 숫자: 1000이나 9999, 혹은 오타로 들어간 12345 같은 숫자는 볼 것도 없이 무조건 255보다 큽니다.

이때 하나하나 범위를 지정하는 대신 [0-9]{4,} 한 줄을 딱 써주면, **"숫자 자릿수가 4개 이상인 녀석들은 전부 다 유효하지 않아!"**라고 아주 효율적으로 선언할 수 있는 것입니다.

3. 수량자({ })의 다른 활용법 (비교)

이왕 공부하시는 김에, 면접에서 헷갈리지 않도록 수량자의 세 가지 형태를 정리해 드릴게요.

패턴	의미	IP 문제에서의 예시
[0-9]{4}	정확히 4번 반복	연도(2026)처럼 딱 4글자만 잡을 때
[0-9]{4,}	4번 이상 반복	1000 이상의 모든 숫자 (오늘의 정답)
[0-9]{1,3}	1번에서 3번 사이 반복	유효한 IP 마디의 자릿수 범위

---

3. 면접 팁

면접관은 이 쿼리를 짤 때 경계값(Boundary)을 얼마나 세밀하게 다루는지 본다.

1. 데이터 무결성: 0과 00을 구분할 수 있는가?
2. 예외 케이스 처리: NOT REGEXP를 활용해 구조적 결함을 먼저 솎아낼 수 있는가?
3. 성능: 복잡한 CASE WHEN 문을 길게 쓰는 것보다, 정규식 한 줄로 깔끔하게 필터링하는 것이 가독성과 유지보수 면에서 뛰어나다.