홈 › SQL 고급 › 07 / 10

WITH 절(CTE)로 쿼리 구조화

단계별 쿼리, 재사용, DML 과 함께 쓰기

섹션 6진행 0 / 10

4. 응용 변형 예제

소스: sql-src/adv_07_cte/02_cte_reuse.sql, 03_dialects.sql. 02 는 01 과 같은 데이터이고, 03 은 emp_bonus·order_archive 테이블을 더합니다.

변형 1: 분석 함수와 조합한 부서별 Top-N

고급 01 순위 함수에서 분석 함수는 WHERE 에 쓸 수 없어 한 번 감싸야 한다고 배웠습니다. CTE 를 쓰면 감싸는 단계가 이름이 되어 읽기 쉽습니다. 부서별 주문 합계 상위 2명을 뽑습니다.

sql

WITH emp_sales AS (
  SELECT
         e.dept
       , e.name
       , SUM(o.amt) AS total_amt
    FROM emp e
    JOIN orders o ON o.emp_id = e.id
   GROUP BY e.dept, e.name
), ranked AS (
  SELECT
         dept
       , name
       , total_amt
       , ROW_NUMBER() OVER (PARTITION BY dept ORDER BY total_amt DESC, name) AS rn
    FROM emp_sales
)
SELECT dept, name, total_amt, rn
  FROM ranked
 WHERE rn <= 2
 ORDER BY dept, rn;

text

DEPT | NAME   | TOTAL_AMT | RN
-----+--------+-----------+---
개발 | 김개발 | 360       | 1
개발 | 남개발 | 310       | 2
영업 | 이영업 | 450       | 1
영업 | 한영업 | 270       | 2
(4행)

emp_sales(집계)와 ranked(순위)가 별도 CTE 라서 각 단계의 의도가 이름에 드러납니다. 상위 N 을 바꾸려면 마지막 WHERE 만 고칩니다.

변형 2: 파라미터 CTE

조회 기간 같은 조건값이 쿼리 여러 곳에 흩어지면 바꿀 때 빠뜨리기 쉽습니다. 조건값만 담은 CTE p 를 맨 앞에 두고 CROSS JOIN 으로 붙입니다.

sql

WITH p AS (
  SELECT DATE '2024-02-01' AS from_d, DATE '2024-03-01' AS to_d
)
SELECT
       o.cust
     , SUM(o.amt) AS total_amt
  FROM orders o
 CROSS JOIN p
 WHERE o.ord_date >= p.from_d
   AND o.ord_date <  p.to_d
 GROUP BY o.cust
 ORDER BY o.cust;

text

CUST     | TOTAL_AMT
---------+----------
가람상사 | 200
다솜상사 | 90
라온상사 | 180
(3행)

2월 주문만 집계됩니다. 3월로 바꾸려면 p 의 두 날짜만 고치면 되고, 3월 결과(5행)는 02_cte_reuse.sql 에 있습니다. 범위는 >= 시작 AND < 다음 시작 형태로 씁니다. Oracle DATE 는 시·분·초를 가지므로 BETWEEN 보다 안전합니다.

변형 3: INSERT ... SELECT 에 CTE 쓰기

CTE 를 만들어 조건에 맞는 행만 다른 테이블에 넣는 작업은 실무에서 자주 합니다. Oracle 은 INSERT 다음에 WITH ... SELECT 가 옵니다. 주문 합계가 300 이상인 사원의 보너스(합계의 10%)를 emp_bonus 에 넣습니다.

sql

INSERT INTO emp_bonus (emp_id, bonus)
WITH big AS (
  SELECT emp_id, SUM(amt) AS total_amt
    FROM orders
   GROUP BY emp_id
)
SELECT emp_id, total_amt / 10
  FROM big
 WHERE total_amt >= 300;

text

EMP_ID | BONUS
-------+------
1      | 36
2      | 31
3      | 45
(3행)

H2 는 이 형태를 실행합니다. 세 사원이 들어갔고 이후 예제가 이 테이블을 씁니다.

변형 4: UPDATE·DELETE 조건에 CTE 쓰기

H2 에서는 WHERE ... IN ( 안에 WITH 를 두는 형태가 됩니다. 보너스를 받은 사원의 급여를 10 올립니다.

sql

UPDATE emp
   SET sal = sal + 10
 WHERE id IN (
       WITH b AS (SELECT emp_id FROM emp_bonus)
       SELECT emp_id FROM b
      );

세 사원(김개발 510, 남개발 460, 이영업 490)의 급여가 10 올랐고, 나머지 세 명은 그대로입니다.

DELETE 도 같습니다. 급여가 480 미만인 사원의 보너스 행을 지웁니다.

sql

DELETE FROM emp_bonus
 WHERE emp_id IN (
       WITH low AS (SELECT id FROM emp WHERE sal < 480)
       SELECT id FROM low
      );

text

EMP_ID | BONUS
-------+------
1      | 36
3      | 45
(2행)

남개발(460)의 보너스 행만 삭제되었습니다. 이 형태는 H2 가 실행한다는 뜻일 뿐, 서브쿼리 안의 WITH 를 모든 DB 가 허용하는 것은 아닙니다.

변형 5: DB 별 DML 문법 비교

WITH 를 문장 맨 앞에 두고 뒤에 UPDATE·DELETE·INSERT 를 잇는 형태는 H2 2.3 에서 문법 오류입니다. 아래는 문법 검토만 하는 블록입니다(H2 미지원, 문법 검토만).

sql

-- MSSQL (앞 문장은 세미콜론으로 끝낸다. CTE 자체를 UPDATE·DELETE)
;WITH c AS (
  SELECT id, sal FROM emp WHERE dept = '개발'
)
UPDATE c
   SET sal = sal + 10;

sql

-- MySQL 8.0 (WITH 를 문장 앞에)
WITH c AS (
  SELECT emp_id FROM emp_bonus
)
UPDATE emp
   SET sal = sal + 10
 WHERE id IN (SELECT emp_id FROM c);

sql

-- MySQL 8.0 (INSERT ... WITH ... SELECT 도 가능)
INSERT INTO order_archive (id, emp_id, amt)
WITH c AS (
  SELECT id, emp_id, amt FROM orders WHERE amt >= 200
)
SELECT id, emp_id, amt FROM c;

MSSQL 은 CTE 가 가리키는 테이블에 바로 UPDATE·DELETE 가 적용되어, 위 쿼리는 개발부 급여를 올립니다. 03_dialects.sql 은 같은 INSERT ... WITH ... SELECT 를 SET MODE Oracle, MySQL, MSSQLServer 로 각각 실행했고 셋 다 아래 2행이 들어갔습니다.

text

ID | EMP_ID | AMT
---+--------+----
3  | 3      | 200
9  | 6      | 210
(2행)

주의
H2 의 MSSQLServer 모드가 INSERT ... WITH ... SELECT 를 받아 준다고 실제 MSSQL 에서 되는 것은 아닙니다. 실제 MSSQL 은 WITH c AS (...) INSERT INTO ... SELECT ... FROM c 형태입니다. H2 결과는 문법의 근거가 되지 않습니다.