[SQLD 요약노트] 계층형 쿼리, PIVOT, 정규표현식

SQLD 2과목 Part 2에 대한 내용입니다.

<2025 이기적 SQL개발자 이론서+기출문제> 교재 및 강의(12~13)를 참고하여 정리한 내용입니다.

(SQL 쿼리 관련하여 이미 알고 있는 내용은 생략/축소하여 기록되어있습니다)

---

1. 계층형 쿼리

• 상위/하위 N개를 확인할 때 사용하는 쿼리로, 인라인 뷰와 ROWNUM을 함께 활용함

1.1. 기본 개념

• 한 테이블 내에 계층 관계가 있는 컬럼이 존재할 때 이를 표현하기 위한 쿼리
• 주로 메뉴 정보나 회사 조직 정보를 조회할 때 사용

• 전개에 쓰는 주요 함수
  1. START WITH : 계층을 전개할 시작 위치(=루트)를 지정 (ex. '홈' 메뉴를 시작으로 계층 전개 시작)
  2. CONNECT BY : 계층 전개 조건을 입력
     • PRIOR가 붙은 쪽을 현재 행으로 해석하면 됨 (아래 예시 참고)
     • NOCYCLE : 전개하다가, 사이클 생기면 전개를 멈추겠다는 조건
  3. ORDER SIBLINGS BY : 정렬을 하되, 같은 계층끼리(형제 노드끼리) 정렬하도록 설정
• 그 외 편의상 쓰는 함수
  1. LEVEL : 현재 행의 계층 레벨을 보여주는 가상 컬럼 (루트의 레벨은 1)
  2. SYS_CONNECT_BY_PATH : 현재 전개 중인 행까지의 경로를 보여줌 (구분자는 원하는 대로 입력 가능)
  3. CONNECT_BY_ROOT : 현재 전개 중인 행의 최상위 루트의 정보를 보여줌 (어떤 컬럼을 보여줄지 선택 가능)
  4. CONNECT_BY_ISLEAF : 현재 행이 단말인지 확인 (단말이면 1, 아니면 0)
  5. CONNECT_BY_ISCYCLE : 현재 행이 사이클 있는지 확인 (있으면 1, 없으면 0)
  6. LPAD(구분자, 구분자 개수) || 컬럼명 : 계층구조를 트리 형태로 보기 좋게 출력하려고 쓰는 구문

 

1.2. 실행 원리 (전개 과정)

👉기본적으로, FROM ➔ 계층 전개를 모두 수행한 후에 (START WITH ... CONNECT BY ...) ➔ WHERE절로 넘어감

예제 테이블

 

1. START WITH의 조건에 맞는 행을 루트 행(시작점)으로 지정한다.

 

2. CONNECT BY의 조건에 따라 전개할 행을 찾고, 전개한다.

• '상위메뉴ID'가 현재 행(루트)의 '메뉴ID'가 같은 것들은 자식으로 연결 = '상위메뉴ID'가 'M000'인 행들은 아래에 붙여라

 

3. 더 전개할 행이 없으면 다음 행으로 넘어가서 전개를 시도한다. (반복)

• '상위메뉴ID'가 'M000'인 행이 더 없음 → 이제 2번째 행을 기준(PRIOR)으로 두고 전개 시도
• '상위메뉴ID'가 'M001'인 행을 찾아봤지만 없음 → 이제 3번째 행으로 넘어감
• ... 이런 식으로 계속 전개 시도 ... (전개 완료될 때까지)
• '상위메뉴ID'가 'M005'인 행 찾았음! → 아래에 붙여서 자식으로 연결!

 

4. 전개 완료 후, 정렬 문법 있으면 계층별로 정렬한다.

• 각 레벨 내에서 '메뉴ID' 기준으로 오름차순 정렬 (지금은 그냥 숫자 순서대로라 정렬해도 그대로긴 함)
  • 레벨1 : M000
  • 레벨2 : M001, M002, M003, M004, M005, M006
  • 레벨3 : M007, M008
• 만약 DESC로 했다면, 결과 테이블 2번째 행에 M006이 나왔을 것!

 

5. 이제 WHERE절로 넘어간다 (있을 경우에 ㅇㅇ)

• ex) WHERE 메뉴ID = 'M001'

 

[✅전체 쿼리 & 실행 결과]

-- 전체 쿼리 --
SELECT 메뉴ID
     , 상위메뉴ID
     , 메뉴이름
     , LEVEL
     , LPAD(' ', (LEVEL-1)*2 ) || 메뉴이름 -- (보기 좋게 출력하는 부분 추가)
 FROM 메뉴
 WHERE 1=1 -- 조건 필요하면 여기다 넣으면 됨 (ex. WHERE 메뉴ID = 'M001')
 START WITH 상위메뉴ID IS NULL
 CONNECT BY NOCYCLE 상위메뉴ID = PRIOR 메뉴ID
 ORDER SIBLINGS BY 메뉴ID;

 

👨‍🏫순방향 VS 역방향 (just for 시험)
 • PRIOR가 부모에 붙어있으면 역방향
 • PRIOR가 자식에 붙어있으면 순방향
➔ 위 예시도 '상위메뉴ID'가 부모, '메뉴ID'가 자식이니까 순방향이었음!

 

[✅추가 예시 & 실행 결과]

SELECT 메뉴ID
     , 상위메뉴ID
     , 메뉴이름
     , SYS_CONNECT_BY_PATH(메뉴ID, '-' ) AS 경로 -- 구분자는 다른 거 써도 됨!
     , CONNECT_BY_ROOT 메뉴ID AS 최상위메뉴 -- 메뉴ID 말고 메뉴이름을 출력해도 됨!
FROM 메뉴
START WITH 상위메뉴ID IS NULL
CONNECT BY NOCYCLE 상위메뉴ID = PRIOR 메뉴ID
ORDER SIBLINGS BY 메뉴ID;

 

1.3. 셀프 조인 (참고)

• 자기 자신과 조인하는 것
• 계층형 쿼리가 구현되기 전에는 이 셀프조인으로 계층형 정보를 조회했었음!
  • 단, 이 방식은 바로 상위의 정보만 가져올 수 있음 (상위의 상위 정보를 가져오려면 조인을 3번 해야 함....)

---

2. PIVOT

엑셀의 피벗 테이블을 생각하면 됨!

2.1. PIVOT

• PIVOT: 특정 행 값들을 펼쳐서 컬럼으로 만들어주는 작업 (행 ➔ 열로 바꾸는 작업)
• ⚠️AS 절 주의사항 : 컬럼명은 큰따옴표("), 값은 작은따옴표(')로 입력!

출처: 이기적 영진닷컴 강의

 

2.2. UNPIVOT

• UNPIVOT : 피봇 테이블을 다시 열 ➔ 행으로 바꾸는 작업

---

3. 정규표현식 (REGEXP)

3.1. 정규표현식에 사용되는 메타문자

• 정규표현식 : 특정한 규칙을 가진 문자열을 표현하기 위해 사용하는 형식 언어
  • 이전 글 참고 가능
• ✅주요 메타 문자

문자	의미	예시
.	임의의 문자 (또는 공백) 1개	.e : he, we, me, ... 전부 매치됨
^ $	- 문자열의 시작 부분 - 문자열의 끝 부분	- ^김 : 라인 맨 앞에 있는 '김' - e$ : 라인 맨 끝에 있는 'e'
|	OR 연산자 기능	김|박 : '김' 또는 '박'과 매치됨
\	특수문자를 이스케이프 처리(일반문자로 취급)	\. : (메타문자 말고) 실제 온점을 찾고 싶을 때 사용
[ ... ] [^ ... ]	- 대괄호 안에 있는 문자들 중 1개 - 위 집합의 여집합 개념	[abc] : a또는 b또는 c가 매치됨 [^가-힣] : 한글 음절 빼고 전부 매치됨
( ... )	- 괄호 안의 문자를 하나의 단어로 취급	(abc) : 'abc'라는 문자가 통째로 매치됨
\d \w	- 숫자 1개 - 문자 1개
POSIX 문자 클래스	[[:alpha:]] : 알파벳 문자 [[:digit:]] : 숫자 [[:alnum:]] : 알파벳과 숫자 [[:lower:]] : 소문자 [[:upper:]] : 대문자 [[:space:]] : 공백 문자 [[:punct:]] : 특수 문자
? * +	- 앞 문자가 0개 또는 1개 있음을 의미 - 앞 문자가 0개 이상 있음을 의미 - 앞 문자가 1개 이상 있음을 의미
{m} {m,} (m,n}	- 앞 문자가 정확히 m번 일치 - 앞 문자가 최소 m번 이상 일치 - 앞 문자가 최소 m번 이상, 최대 n번 이하 일치

 

 

3.2. 정규표현식 함수

1) REGEXP_LIKE

• 정규표현식 검색 (매칭되면 True, 아니면 False 반환)

SELECT *
FROM 직원
WHERE REGEXP_LIKE(이름, '^김(철|홍|구)수$'); -- '김철수, 김홍수, 김구수'만 출력

SELECT *
FROM 직원
WHERE REGEXP_LIKE(주민등록번호, '-1'); -- 2000년생 미만의 남성만 출력

2) REGEXP_REPLACE

• 정규표현식 검색하여 문자열 변경

SELECT REGEXP_REPLACE('hello@temp.com', '@.+', '@realdomain.net') AS new_email
FROM dual; -- @ 뒷부분을 다 특정 도메인으로 변경

SELECT REGEXP_REPLACE('hello@temp.com', '@temp', '@real') AS new_email
FROM dual; -- #temp 부분만 @real로 변경

 

3) REGEXP_SUBSTR

• 정규표현식 검색하여 특정 문자열을 추출
• Ex1) 주민등록번호에서 특정 부분 추출하기

select 주민등록번호,
       regexp_substr(주민등록번호, '\d{6}', 1, 1) as 앞6자리, -- 숫자 6개짜리로 매칭되는 1번째 문자열로부터, 1번째에 있는 문자열을 추출
       regexp_substr(주민등록번호, '\d{6}', 1, 2) as 두번째6자리 -- 숫자 6개짜리로 매칭되는 1번째 문자열로부터, 2번째에 있는 문자열을 추출
       regexp_substr(주민등록번호, '\d{7}', 1, 1) as 뒤7자리,
       regexp_substr(주민등록번호, '\d{6}-\d{7}') as 전체번호,
       regexp_substr(주민등록번호, '\d{1}', 7, 1) as 성별코드
from 직원;

• Ex) 이메일 주소 끝자리 확인하기

--이메일 주소의 끝이 .com으로 끝이 나는가?
SELECT 이메일,
       REGEXP_SUBSTR(이메일, '\.com$', 1, 1) AS COM_CHECK
FROM 직원;              -- .을 그대로 쓰기 위해 이스케이프(\) 사용

 

4) REGEXP_INSTR

• 정규표현식 검색하여 위치를 반환 (매칭값 없으면 0 반환)

-- 단순 매칭 연습
SELECT REGEXP_INSTR('123456789', '23') AS TEST1 -- 결과=2
     , REGEXP_INSTR('123456789', '4') AS TEST2 -- 결과=4
     , REGEXP_INSTR('123456789', '68') AS TEST3 -- 결과=0
     , REGEXP_INSTR('123456789', '9') AS TEST4 -- 결과=9
  FROM DUAL;
  
-- 'https' 형태인지 확인하기
SELECT 'https://www.inflearn.com',
       REGEXP_INSTR('https://www.inflearn.com', 'https://')
FROM DUAL; -- 결과 = 1이면 https 형태인 거고, 0이면 아닌 것

5) REGEXP_COUNT

• 정규표현식 검색하여 개수를 반환

SELECT REGEXP_COUNT('ABCABCDABCDABCD', 'ABC') AS TEST1 -- 결과=4
     , REGEXP_COUNT('ABCABCABC', 'ABC') AS TEST2 -- 결과=3
     , REGEXP_COUNT('ABCABCABCABC', 'AC') AS TEST3 -- 결과=0
     , REGEXP_COUNT('오늘은 기분이 좋다. 오늘은 즐겁다. 오늘은 토요일이다', '오늘은') AS TEST4 -- 결과=3
FROM DUAL;

---