Top N, 계층형 질의, PIVOT, 정규표현식

Top N 쿼리

Top N은 정렬한 결과에서 상위 몇 건만 가져오는 쿼리입니다. 핵심은 정렬을 먼저 한 뒤 제한해야 한다는 점입니다.

-- Oracle 12c+ / SQL Server 2012+ ANSI 표준
SELECT ename, sal
FROM emp
ORDER BY sal DESC
FETCH FIRST 3 ROWS ONLY;

FETCH · OFFSET 세 가지 패턴

-- 상위 5건만
... ORDER BY sal DESC FETCH FIRST 5 ROWS ONLY;

-- 5건 건너뛴 뒤 3건
... ORDER BY sal DESC OFFSET 5 ROWS FETCH NEXT 3 ROWS ONLY;

-- 동점 포함 (급여 3위 동률이면 모두 반환)
... ORDER BY sal DESC FETCH FIRST 3 ROWS WITH TIES;

셀프 조인

셀프 조인은 같은 테이블을 두 번 사용해 자기 자신과 조인하는 방식입니다. 사원과 관리자가 같은 emp 테이블에 있을 때 자주 사용합니다.

SELECT worker.ename AS worker_name,
       manager.ename AS manager_name
FROM emp worker
LEFT JOIN emp manager ON worker.mgr = manager.empno;

같은 테이블을 두 번 쓰므로 별칭이 필요합니다. 관리자가 없는 사원(KING)도 결과에 포함하려면 LEFT OUTER JOIN을 사용합니다.

계층형 질의

Oracle 계층형 질의는 부모-자식 구조(조직도, 카테고리 트리 등)를 펼칠 때 사용합니다.

SELECT LEVEL, empno, ename, mgr,
       LPAD(' ', (LEVEL-1)*2) || ename AS tree
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr
ORDER SIBLINGS BY ename;

의사 컬럼과 계층형 함수

PIVOT과 UNPIVOT

SELECT *
FROM (SELECT job, deptno, sal FROM emp)
PIVOT (
  SUM(sal) FOR deptno IN (10, 20, 30)
);

SELECT *
FROM sales_table
UNPIVOT (
  amount FOR quarter IN (q1, q2, q3, q4)
);

정규표현식

정규표현식은 문자열 패턴을 찾거나 바꾸는 규칙입니다. SQLD에서는 함수 이름과 기본 기호를 연결하는 문제가 자주 나옵니다.

주요 메타 문자

POSIX 문자 클래스

Oracle 정규표현식 함수

-- 이름이 'A'로 시작하는 사원
SELECT ename FROM emp
WHERE REGEXP_LIKE(ename, '^A');

-- 대소문자 무시 (i 옵션)
WHERE REGEXP_LIKE(ename, '^a', 'i');