Top-N 계층형 질의 PIVOT - ROWNUM LIMIT 재귀 WITH CONNECT BY 정규 표현식 (SQLD 기본 12장)

이 장에서 배우는 것

11장에서 순위 함수를 익혔다. 이 장은 그 순위로 "상위 N 개"를 정확히 자르는 법에서 시작한다. 이어서 조직도처럼 한 테이블 안에서 부모·자식이 이어지는 데이터를 훑는 계층형 질의, 행을 열로 바꾸는 PIVOT, 문자열 패턴을 다루는 정규 표현식을 정리한다. 이 셋은 제품마다 문법이 가장 많이 다른 영역이라, 개념을 먼저 잡고 문법은 대응표로 기억하는 편이 낫다.

정렬과 번호 매기기의 순서를 지켜 Top-N 을 자르고, 동점 포함(WITH TIES)과 페이지 나누기를 확인한다.
재귀 WITH 로 조직도를 위에서 아래로, 아래에서 위로 훑고 Oracle 의 CONNECT BY 와 하나씩 대응시킨다.
CASE 집계로 PIVOT, UNION ALL 로 UNPIVOT 을 만들고, 정규 표현식으로 형식을 검사한다.

핵심 개념

"급여 상위 3명"을 뽑아 달라는 요청에 3명을 줬더니, 4위가 3위와 급여가 같다며 왜 빠졌냐는 질문이 돌아왔다. Top-N 은 문법보다 동점을 어떻게 할지가 먼저 정해져야 하는 문제다.

Top-N 과 Oracle 의 ROWNUM

Oracle 의 ROWNUM 은 WHERE 조건을 통과한 행에 1부터 차례로 붙는 번호다. 번호가 ORDER BY 보다 먼저 붙는다. 그래서 WHERE ROWNUM <= 3 ORDER BY salary DESC 는 아무 3행을 고른 뒤 정렬하므로 틀린다. 정렬을 인라인 뷰 안에서 끝낸 뒤 바깥에서 ROWNUM 으로 잘라야 한다. 또 WHERE ROWNUM = 2 나 ROWNUM > 1 은 0행이다. 첫 행이 조건을 통과하지 못하면 번호 1이 다음 행에 다시 붙고, 결국 어떤 행도 2가 되지 못한다.

표준은 FETCH FIRST n ROWS ONLY(동점 포함은 WITH TIES), SQL Server 는 TOP (n), SQLite 와 MySQL 은 LIMIT n 이다. 어느 것을 쓰든 정렬 기준이 유일하지 않으면 경계의 동점 행 중 누가 들어갈지 보장되지 않는다.

Oracle ROWNUM 은 ORDER BY 전에 붙으므로 아무 3행을 고른 뒤 정렬한다(비교 설명, 실행하지 않음). 정렬을 먼저 끝낸 뒤 번호로 자르면 급여 상위 3명이 나오고, 4300 동점까지 넣으려면 RANK 로 4명을 고른다.

그림 · 번호를 먼저 붙이면 Top-N 이 틀린다 — Oracle ROWNUM 은 ORDER BY 전에 붙으므로 아무 3행을 고른 뒤 정렬한다(비교 설명, 실행하지 않음). 정렬을 먼저 끝낸 뒤 번호로 자르면 급여 상위 3명이 나오고, 4300 동점까지 넣으려면 RANK 로 4명을 고른다.

계층형 질의

직원 테이블의 manager_id 는 같은 테이블의 staff_id 를 가리킨다(순환 관계). 이런 데이터를 뿌리부터 따라 내려가는 것이 계층형 질의다.

Oracle CONNECT BY	재귀 WITH(표준, 이 책에서 실행)
`START WITH manager_id IS NULL`	앵커(첫 SELECT)의 WHERE
`CONNECT BY PRIOR staff_id = manager_id`(부모 → 자식, 순방향)	재귀 부분의 조인 `s.manager_id = o.staff_id`
`CONNECT BY staff_id = PRIOR manager_id`(자식 → 부모, 역방향)	재귀 부분의 조인 `m.staff_id = u.manager_id`
`LEVEL`	재귀마다 1씩 더하는 컬럼
`SYS_CONNECT_BY_PATH(name, '/')`	재귀마다 이어 붙이는 문자열 컬럼
`CONNECT_BY_ISLEAF`	자식 존재 여부를 EXISTS 로 검사
`ORDER SIBLINGS BY`	경로 문자열로 정렬해 깊이 우선 순서를 흉내

PRIOR 가 붙은 쪽이 "이미 방문한 행"이다. PRIOR staff_id = manager_id 는 "이미 방문한 행의 staff_id 를 manager_id 로 가진 행", 즉 자식을 찾아 내려간다.

PIVOT 과 UNPIVOT

PIVOT 은 행 값을 열로 올린다(월별 행 → 7월 열, 8월 열). UNPIVOT 은 반대다. 전용 문법이 없어도 PIVOT 은 SUM(CASE WHEN ... THEN 값 END) 로, UNPIVOT 은 열마다 SELECT 를 UNION ALL 로 이어 만들 수 있다.

예제 스키마

7장과 같은 스키마다. 조직도는 한지수(101) 아래에 오민재·이도현·정우진이 있고, 오민재 아래 박서윤, 이도현 아래 최하늘·강예린, 정우진 아래 윤태오가 있는 3단계 구조다.

staff_id	name	team_id	manager_id	salary	bonus	hired
101	한지수	10	NULL	5200	NULL	2019-03-02
102	오민재	10	101	4300	300	2021-07-15
103	박서윤	10	102	3900	NULL	2023-01-09
104	이도현	20	101	4300	500	2020-11-30
105	최하늘	20	104	3600	0	2024-02-19
106	정우진	30	101	4800	NULL	2018-05-21
107	강예린	NULL	104	3100	200	2025-06-02
108	윤태오	30	106	3900	NULL	2022-09-12

SQL과 실행 결과

LIMIT 와 동점

SELECT staff_id, name, salary FROM staff
ORDER BY salary DESC, staff_id
LIMIT 3;

실행 결과:

staff_id  name    salary
--------  ------  ------
101       한지수  5200
106       정우진  4800
102       오민재  4300

3위 자리에 4,300 이 두 명 있다. 정렬 기준에 staff_id 를 더했기 때문에 오민재(102)가 들어갔다. 이 두 번째 기준이 없으면 누가 들어갈지 보장되지 않는다.

동점 포함(WITH TIES)을 RANK 로

SELECT staff_id, name, salary FROM (
  SELECT staff_id, name, salary, RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM staff)
WHERE rnk <= 3
ORDER BY salary DESC, staff_id;

실행 결과:

staff_id  name    salary
--------  ------  ------
101       한지수  5200
106       정우진  4800
102       오민재  4300
104       이도현  4300

RANK 가 3 이하인 행을 모두 남기면 3위 동점 두 명이 함께 나와 4행이 된다. 표준의 FETCH FIRST 3 ROWS WITH TIES, SQL Server 의 TOP (3) WITH TIES 와 같은 결과다.

ROWNUM 방식: 정렬 먼저, 번호는 나중

-- 정렬을 먼저 끝낸 뒤 번호를 붙이고, 번호로 거른다
SELECT rn, name, salary FROM (
  SELECT ROW_NUMBER() OVER (ORDER BY salary DESC, staff_id) AS rn, name, salary
  FROM staff)
WHERE rn BETWEEN 3 AND 4;

실행 결과:

rn  name    salary
--  ------  ------
3   오민재  4300
4   이도현  4300

인라인 뷰 안에서 정렬과 번호를 끝낸 뒤 바깥에서 3~4번을 골랐다. Oracle 의 ROWNUM 으로 "3~4위"를 구할 때도 인라인 뷰를 두 겹 쓰는 같은 구조가 필요하다. ROW_NUMBER 는 정렬 뒤에 번호가 붙으므로 rn BETWEEN 3 AND 4 가 바로 동작한다.

SELECT name, salary FROM staff
ORDER BY salary DESC, staff_id
LIMIT 2 OFFSET 2;

실행 결과:

name    salary
------  ------
오민재  4300
이도현  4300

표 · Top-N 을 자르는 방법과 동점 처리

방법	동점 4300	이 책의 실행
`ORDER BY … LIMIT 3`	한 명만 들어감	3행 (12-limit)
`RANK() … WHERE rnk <= 3`	둘 다 들어감	4행 (12-with-ties)
`ROW_NUMBER() … BETWEEN 3 AND 4`	번호로 3·4위를 자름	2행 (12-rownum-like)
`FETCH FIRST 3 ROWS WITH TIES` (표준)	둘 다 들어감	실행하지 않음 (RANK 방식과 같은 뜻)
Oracle `ROWNUM <= 3` (인라인 뷰에서 정렬 뒤)	한 명만 들어감	실행하지 않음 (LIMIT 방식과 같은 뜻)

위에서 아래로: 조직도 펼치기

WITH RECURSIVE org(staff_id, name, lvl, path) AS (
  SELECT staff_id, name, 1, '/' || name
  FROM staff WHERE manager_id IS NULL              -- START WITH 에 해당
  UNION ALL
  SELECT s.staff_id, s.name, o.lvl + 1, o.path || '/' || s.name
  FROM staff s JOIN org o ON s.manager_id = o.staff_id   -- CONNECT BY PRIOR staff_id = manager_id
)
SELECT lvl, path,
       CASE WHEN EXISTS (SELECT 1 FROM staff c WHERE c.manager_id = org.staff_id)
            THEN 0 ELSE 1 END AS is_leaf
FROM org
ORDER BY path;

실행 결과:

lvl  path                   is_leaf
---  ---------------------  -------
1    /한지수                0
2    /한지수/오민재         0
3    /한지수/오민재/박서윤  1
2    /한지수/이도현         0
3    /한지수/이도현/강예린  1
3    /한지수/이도현/최하늘  1
2    /한지수/정우진         0
3    /한지수/정우진/윤태오  1

LEVEL 에 해당하는 lvl, SYS_CONNECT_BY_PATH 에 해당하는 path, CONNECT_BY_ISLEAF 에 해당하는 is_leaf 를 직접 만들었다. 경로 문자열로 정렬하면 부모 바로 아래에 자식이 오는 깊이 우선 순서가 된다. 재귀 WITH 자체는 결과 순서를 보장하지 않으므로 이 정렬이 필요하다.

아래에서 위로: 결재선 올라가기

WITH RECURSIVE up(staff_id, name, manager_id, lvl) AS (
  SELECT staff_id, name, manager_id, 1 FROM staff WHERE staff_id = 105
  UNION ALL
  SELECT m.staff_id, m.name, m.manager_id, u.lvl + 1
  FROM staff m JOIN up u ON m.staff_id = u.manager_id
)
SELECT lvl, name FROM up ORDER BY lvl;

실행 결과:

lvl  name
---  ------
1    최하늘
2    이도현
3    한지수

최하늘에서 시작해 관리자를 따라 올라갔다. 조인 방향만 바꾸면 역방향 전개가 된다.

manager_id 가 NULL 인 한지수에서 시작해 부하를 한 단계씩 찾아 내려간다(12-hier-down, lvl 1~3). 최하늘에서 manager_id 를 따라 올라가면 이도현, 한지수 순이다(12-hier-up).

그림 · 재귀 WITH 로 펼친 조직도 — manager_id 가 NULL 인 한지수에서 시작해 부하를 한 단계씩 찾아 내려간다(12-hier-down, lvl 1~3). 최하늘에서 manager_id 를 따라 올라가면 이도현, 한지수 순이다(12-hier-up).

PIVOT: 월을 열로

SELECT region,
       SUM(CASE WHEN sale_month = '2026-07' THEN amount END) AS m07,
       SUM(CASE WHEN sale_month = '2026-08' THEN amount END) AS m08
FROM sale
GROUP BY region
ORDER BY region;

실행 결과:

region  m07  m08
------  ---  ---
부산    100  120
서울    150  120

서울 8월은 120 과 NULL 이 합쳐져 120 이다. CASE 에 ELSE 가 없으면 조건에 맞지 않는 행은 NULL 이 되고, SUM 이 건너뛴다.

UNPIVOT: 열을 다시 행으로

WITH wide AS (
  SELECT region,
         SUM(CASE WHEN sale_month = '2026-07' THEN amount END) AS m07,
         SUM(CASE WHEN sale_month = '2026-08' THEN amount END) AS m08
  FROM sale GROUP BY region
)
SELECT region, '2026-07' AS sale_month, m07 AS total FROM wide
UNION ALL
SELECT region, '2026-08', m08 FROM wide
ORDER BY region, sale_month;

실행 결과:

region  sale_month  total
------  ----------  -----
부산    2026-07     100
부산    2026-08     120
서울    2026-07     150
서울    2026-08     120

정규 표현식으로 형식 검사

SQLite 명령행 도구에는 REGEXP 연산자가 들어 있다(라이브러리 기본 기능은 아니다).

WITH contact(name, phone) AS (
  VALUES ('가', '010-1234-5678'), ('나', '01012345678'),
         ('다', '010-12-5678'),   ('라', '02-555-0101')
)
SELECT name, phone,
       phone REGEXP '^010-[0-9]{4}-[0-9]{4}$' AS mobile_ok,
       phone REGEXP '^0[0-9]{1,2}-'            AS has_area_dash
FROM contact;

실행 결과:

name  phone          mobile_ok  has_area_dash
----  -------------  ---------  -------------
가    010-1234-5678  1          1
나    01012345678    0          0
다    010-12-5678    0          1
라    02-555-0101    0          1

^ 는 시작, $ 는 끝, [0-9]{4} 는 숫자 네 개다. 휴대전화 형식을 온전히 갖춘 것은 '가' 하나다. '다'는 가운데 자리가 두 자리라 걸리지 않았다.

SELECT name FROM staff WHERE name REGEXP '^(박|윤)' ORDER BY staff_id;

실행 결과:

name
------
박서윤
윤태오

표준과 구현의 차이

아래 Oracle·SQL Server 동작은 비교 설명이며 실행하지 않았다.

항목	SQLite(실행)	Oracle	SQL Server
상위 N 행	`LIMIT n OFFSET m`	`ROWNUM`, 12c 부터 `FETCH FIRST n ROWS ONLY`	`TOP (n)`, `OFFSET m ROWS FETCH NEXT n ROWS ONLY`
동점 포함	없음(RANK 로 대체)	`FETCH FIRST n ROWS WITH TIES`	`TOP (n) WITH TIES`
계층형 질의	`WITH RECURSIVE`	`CONNECT BY`, 재귀 WITH(`RECURSIVE` 키워드 없이)	재귀 CTE(`RECURSIVE` 키워드 없이)
PIVOT·UNPIVOT 전용 문법	없음	지원	지원
정규 표현식	CLI 의 `REGEXP` 연산자(참·거짓만)	`REGEXP_LIKE`, `REGEXP_SUBSTR`, `REGEXP_REPLACE`, `REGEXP_INSTR`, `REGEXP_COUNT`	SQL Server 2025 부터 `REGEXP_LIKE` 등 추가, 이전은 LIKE 패턴만

-- 비교용 문법(실행하지 않음): Oracle
SELECT LEVEL, LPAD(' ', (LEVEL - 1) * 2) || name AS name,
       SYS_CONNECT_BY_PATH(name, '/') AS path, CONNECT_BY_ISLEAF AS is_leaf
FROM staff
START WITH manager_id IS NULL
CONNECT BY PRIOR staff_id = manager_id
ORDER SIBLINGS BY name;

SELECT *
FROM (SELECT region, sale_month, amount FROM sale)
PIVOT (SUM(amount) FOR sale_month IN ('2026-07' AS m07, '2026-08' AS m08));

[구현 차이] Oracle 의 PIVOT 은 FROM 절 결과에서 PIVOT 에 쓰지 않은 컬럼을 모두 그룹 기준으로 삼는다. 그래서 위 예시처럼 필요한 컬럼만 고른 인라인 뷰를 먼저 만든다. sale 을 그대로 쓰면 sale_id 까지 그룹 기준이 되어 행이 줄지 않는다.

시험에서 헷갈리는 지점

판단 1. "Oracle 에서 SELECT ... WHERE ROWNUM <= 3 ORDER BY salary DESC 는 급여 상위 3명을 돌려준다"

틀렸다. ROWNUM 이 정렬 전에 붙으므로 임의의 3행을 뽑아 정렬한 결과다. 정렬을 인라인 뷰 안에 넣어야 한다.

판단 2. "CONNECT BY PRIOR manager_id = staff_id 는 부모에서 자식으로 내려간다"

틀렸다. PRIOR 가 붙은 쪽이 이미 방문한 행이다. 이미 방문한 행의 manager_id 를 staff_id 로 가진 행, 즉 부모로 올라간다(역방향).

판단 3. "계층형 질의에서 WHERE 조건은 전개 전에 적용되어 조건에 맞지 않는 행의 자식까지 모두 제외된다"

틀렸다(Oracle CONNECT BY 기준). WHERE 는 전개가 끝난 뒤 각 행에 적용되므로, 걸러진 행의 자식은 남는다. 가지째 잘라 내려면 조건을 CONNECT BY 절에 쓴다. 재귀 WITH 에서는 재귀 부분의 WHERE 에 쓰면 가지째 잘린다.

연습 문제

RANK() OVER (ORDER BY salary DESC) <= 5 인 직원은 몇 명인가?
조직도에서 윤태오의 LEVEL(대표가 1)은?
이도현 아래에 있는 직원(모든 하위 단계 포함)은 몇 명인가?
판매를 상품별 행, 지역별 열(서울, 부산)로 PIVOT 하라.
ORDER BY salary DESC, staff_id LIMIT 3 OFFSET 5 가 돌려주는 이름을 순서대로 쓰라.

정답과 해설

1. 6. 순위가 1, 2, 3, 3, 5, 5, 7, 8 이므로 5 이하가 여섯 명이다. 3,900 동점 두 명이 모두 5위다.

SELECT COUNT(*) AS n FROM (SELECT RANK() OVER (ORDER BY salary DESC) AS r FROM staff) WHERE r <= 5;

실행 결과:

n
-
6

2. 3. 한지수(1) → 정우진(2) → 윤태오(3).

WITH RECURSIVE org(staff_id, name, lvl) AS (
  SELECT staff_id, name, 1 FROM staff WHERE manager_id IS NULL
  UNION ALL
  SELECT s.staff_id, s.name, o.lvl + 1 FROM staff s JOIN org o ON s.manager_id = o.staff_id
)
SELECT lvl FROM org WHERE name = '윤태오';

실행 결과:

lvl
---
3

3. 2. 최하늘과 강예린이다. 두 사람 아래에는 아무도 없다.

WITH RECURSIVE sub(staff_id) AS (
  SELECT staff_id FROM staff WHERE manager_id = 104
  UNION ALL
  SELECT s.staff_id FROM staff s JOIN sub ON s.manager_id = sub.staff_id
)
SELECT COUNT(*) AS n FROM sub;

실행 결과:

n
-
2

4. A 는 서울 220·부산 140, B 는 서울 50·부산 80.

SELECT product,
       SUM(CASE WHEN region = '서울' THEN amount END) AS seoul,
       SUM(CASE WHEN region = '부산' THEN amount END) AS busan
FROM sale GROUP BY product ORDER BY product;

실행 결과:

product  seoul  busan
-------  -----  -----
A        220    140
B        50     80

5. 6~8번째 행이다. 정렬 순서가 한지수, 정우진, 오민재, 이도현, 박서윤, 윤태오, 최하늘, 강예린이므로 윤태오, 최하늘, 강예린.

SELECT name FROM staff ORDER BY salary DESC, staff_id LIMIT 3 OFFSET 5;

실행 결과:

name
------
윤태오
최하늘
강예린

이 책은 여기서 끝난다. 7~12장의 예제를 다른 조건으로 바꿔 가며 결과를 먼저 적고 실행해 보는 연습을 반복하면, 시험의 결과 예측 문제는 같은 원리의 변형으로 보이기 시작한다. 실행 계획과 튜닝은 SQLP 과정의 영역이다.

참고 자료

SQLite WITH 절과 재귀 CTE
SQLite 명령행 도구
Oracle Database 19c SQL Language Reference — CONNECT BY·PIVOT·ROWNUM 문법 확인용

Top-N 계층형 질의 PIVOT - ROWNUM LIMIT 재귀 WITH CONNECT BY 정규 표현식 (SQLD 기본 12장)

이 장에서 배우는 것

핵심 개념

Top-N 과 Oracle 의 ROWNUM

계층형 질의

PIVOT 과 UNPIVOT

예제 스키마

SQL과 실행 결과

LIMIT 와 동점

동점 포함(WITH TIES)을 RANK 로

ROWNUM 방식: 정렬 먼저, 번호는 나중

위에서 아래로: 조직도 펼치기

아래에서 위로: 결재선 올라가기

PIVOT: 월을 열로

UNPIVOT: 열을 다시 행으로

정규 표현식으로 형식 검사

표준과 구현의 차이

시험에서 헷갈리는 지점

판단 1. "Oracle 에서 SELECT ... WHERE ROWNUM <= 3 ORDER BY salary DESC 는 급여 상위 3명을 돌려준다"

판단 2. "CONNECT BY PRIOR manager_id = staff_id 는 부모에서 자식으로 내려간다"

판단 3. "계층형 질의에서 WHERE 조건은 전개 전에 적용되어 조건에 맞지 않는 행의 자식까지 모두 제외된다"

연습 문제

정답과 해설

참고 자료

댓글 0