소스: sql-src/adv_07_cte/01_cte_basic.sql, 02_cte_reuse.sql. 사원 6명(개발 3, 영업 3)과 주문 13건이 있습니다. 개발 평균 급여는 450, 영업 평균은 약 476.67 입니다.
부서 평균을 구하고(안쪽), 평균 이상인 사원을 고르고(중간), 그 사원들의 주문 합계를 구하는(바깥) 쿼리입니다. 인라인 뷰만으로 쓰면 이렇게 됩니다.
SELECT
t3.name
, t3.sal
, SUM(o.amt) AS total_amt
FROM (
SELECT
e.id
, e.name
, e.sal
FROM emp e
JOIN (
SELECT dept, AVG(sal) AS avg_sal
FROM emp
GROUP BY dept
) a ON a.dept = e.dept
WHERE e.sal >= a.avg_sal
) t3
JOIN orders o ON o.emp_id = t3.id
GROUP BY t3.name, t3.sal
ORDER BY t3.name;NAME | SAL | TOTAL_AMT
-------+-----+----------
김개발 | 500 | 360
남개발 | 450 | 310
이영업 | 480 | 450
한영업 | 520 | 270
(4행)들여쓰기가 깊어져서 부서 평균 쿼리가 어디서 시작하는지 찾기 어렵습니다. 조건을 하나 바꾸려면 안쪽을 헤집어야 합니다.
단계마다 이름을 붙이면 각 쿼리가 짧아지고 위에서 아래로 읽힙니다. dept_avg 는 부서 평균, high_emp 는 평균 이상 사원입니다.
WITH dept_avg AS (
SELECT dept, AVG(sal) AS avg_sal
FROM emp
GROUP BY dept
), high_emp AS (
SELECT
e.id
, e.name
, e.sal
FROM emp e
JOIN dept_avg a ON a.dept = e.dept
WHERE e.sal >= a.avg_sal
)
SELECT
h.name
, h.sal
, SUM(o.amt) AS total_amt
FROM high_emp h
JOIN orders o ON o.emp_id = h.id
GROUP BY h.name, h.sal
ORDER BY h.name;NAME | SAL | TOTAL_AMT
-------+-----+----------
김개발 | 500 | 360
남개발 | 450 | 310
이영업 | 480 | 450
한영업 | 520 | 270
(4행)예제 1 과 같은 4행이 나옵니다. 뒤의 CTE(high_emp)가 앞의 CTE(dept_avg)를 참조하는 점에 주목하세요. 바깥 SELECT 는 이제 high_emp 와 orders 두 테이블만 보면 됩니다.
주의MSSQL 에서 INT 컬럼의
AVG는 정수로 잘려avg_sal이 476 이 됩니다. 이 데이터에서는 결과가 같지만, 평균과 비교하는 쿼리는AVG(sal * 1.0)으로 소수를 보존하세요.
CTE 의 이점 하나는 디버깅입니다. 마지막 SELECT 만 바꾸면 그 단계의 결과를 바로 볼 수 있습니다.
WITH dept_avg AS (
SELECT dept, AVG(sal) AS avg_sal
FROM emp
GROUP BY dept
)
SELECT dept, avg_sal
FROM dept_avg
ORDER BY dept;DEPT | AVG_SAL
-----+------------------
개발 | 450.0
영업 | 476.6666666666667
(2행)인라인 뷰였다면 안쪽 쿼리를 복사해 따로 실행해야 합니다. CTE 는 그 문장 안에서만 존재하므로, 다음 문장에서 dept_avg 를 부르면 없다는 오류가 납니다.
SELECT * FROM dept_avg;예상 오류: Table "DEPT_AVG" not found월별 매출 m 을 한 번 정의하고 두 번 참조해 전월과 비교합니다. c 는 이번 달, p 는 전월이고 둘 다 CTE m 입니다.
WITH m AS (
SELECT
EXTRACT(MONTH FROM ord_date) AS mon
, SUM(amt) AS sales
FROM orders
GROUP BY EXTRACT(MONTH FROM ord_date)
)
SELECT
c.mon
, c.sales
, p.sales AS prev_sales
, c.sales - p.sales AS diff
FROM m c
LEFT JOIN m p ON p.mon = c.mon - 1
ORDER BY c.mon;MON | SALES | PREV_SALES | DIFF
----+-------+------------+-----
1 | 510 | NULL | NULL
2 | 470 | 510 | -40
3 | 710 | 470 | 240
(3행)인라인 뷰였다면 월별 집계 쿼리를 두 번 복사해야 합니다. 1월은 전월이 없어 LEFT JOIN 으로 NULL 이 나옵니다. 분석 함수 LAG(고급 03)로도 같은 일을 할 수 있으니, 연속된 월이 아닐 때 어느 쪽이 맞는지 판단해 고르세요.
같은 CTE 를 스칼라 서브쿼리 (SELECT AVG(sales) FROM m) 안에서 한 번 더 참조하면 "전체 평균보다 큰 달" 을 CASE 로 표시할 수 있습니다. 월별 매출 평균은 약 563 이라 3월(710)만 평균을 넘고, 1월(510)·2월(470)은 평균 이하입니다. 실행 결과는 02_cte_reuse.sql 에 있습니다.