SQL 코드카타_06

SQL 코드카타_Find Invalid IP Addresses

Find Invalid IP Addresses

문제 : Find Invalid IP Addresses

# 목적, 목표 : 유효하지 않은 ip 모두 추출
# 필요한 컬럼 : log_id, ip, invalid_count
# 컬럼 조건 : invalid_count = count(ip)
# 사용할 테이블 : logs
# join key : 없음
# 테이블 조건 : 없음
# 필요한 그룹 : ip
# 필요한 그룹 조건 : 없음
# 정렬 기준 : invalid_count desc, ip desc
# 주의사항 : 유효하지 않은 ip 조건

• 각 octet > 255
• 각 octet 0으로 시작
• octet 개수 != 4

로직

• ' . ' 기준으로 octet 분리 및 octet 개수 count
• 각 조건별로 테이블 생성
• UNION ALL
• distinct log_id, count(ip)

[코드 뜯어보기]

WITH A AS (  # '.' 기준으로 octet 분리 및 octet 개수 count
SELECT log_id, ip, substring_index(ip, '.', 1) as one,
	substring_index(substring_index(ip, '.', 2), '.', -1) as two,
    substring_index(substring_index(ip, '.', 3), '.', -1) as three,
    substring_index(substring_index(ip, '.', 4), '.', -1) as four,
    LENGTH(ip) - LENGTH(REPLACE(ip, '.', '')) as cnt_octet
FROM logs),

B AS (  # 1번 조건
SELECT log_id, ip
FROM A
WHERE one > 255 or two > 255 or three > 255 or four > 255),

C AS (  # 2번 조건
SELECT log_id, ip
FROM A
WHERE one like '0%' or two like '0%' or three like '0%' or four like '0%'),

D AS (  # 3번 조건
SELECT log_id, ip
FROM A
WHERE cnt_octet < 3 or cnt_octet > 3),

E AS (  # 각 조건별 유효하지 않은 ip UNION
SELECT log_id, ip
FROM B
UNION ALL
SELECT log_id, ip
FROM C
UNION ALL
SELECT log_id, ip
FROM D),

F AS (  # distinct log_id로 중복 제거
SELECT distinct log_id, ip
FROM E)

# 유효하지 않은 ip와 invalid_count 추출 및 내림차순 정렬
SELECT ip, count(ip) as invalid_count
FROM F
GROUP BY ip
ORDER BY invalid_count desc, ip desc

[새로 알게된 점]

• SUBSTRING_INDEX(string, delimiter, number)
  • string: 자르려는 원본 문자열
  • delimiter: 문자열을 나눌 기준이 되는 구분자
  • number: 구분자를 기준으로 가져올 문자열의 개수
• SUBSTRING_INDEX로 2번째 문자열만 추출하기
  • SUBSTRING_INDEX(SUBSTRING_INDEX(string, delimiter, 2), delimiter, -1)
• 특정 문자의 개수 세기
  • 전체 문자열 길이 - 특정 문자 제거한 문자열 길이
  • LENGTH(ip) - LENGTH(REPLACE(ip, ' . ', ''))