SQL 8회차 : CTE(WITH)로 복잡한 쿼리 구조화 (유입→구매 전환율 단계별 계산)

martin blake 12월 29, 2025

SQL 8회차 : CTE(WITH)로 복잡한 쿼리 구조화 (유입→구매 전환율 단계별 계산)

한눈에 보는 요약

CTE(Common Table Expression)는 WITH 절로 “이름 붙인 중간 쿼리(임시 결과)”를 만들고, 이를 마지막 SELECT에서 조합하는 방식입니다. 복잡한 쿼리를 단계별로 쪼개서 읽기 쉽게 만들 수 있다는 점이 가장 큰 장점입니다.

CTE를 쓰면 동일한 필터/조인 로직을 반복하지 않아도 되고, 단계별 결과를 중간 점검(디버깅)하기도 쉬워집니다. 특히 전환율처럼 여러 집계가 얽힌 지표는 “유입 집계 → 구매 집계 → 합치기 → 전환율 계산”처럼 단계로 나누면 실수가 크게 줄어듭니다.

이번 글에서는 초보자 기준으로 CTE 문법을 정확히 잡고, 중간 결과 확인 전략을 익힌 뒤, 실전 예제로 유입→구매 전환율을 단계별로 계산해봅니다.

목차

0. 오늘의 목표

  • CTE(=WITH 절)이 “이름 붙인 중간 결과”라는 개념을 이해합니다.

  • 복잡한 쿼리를 단계별로 쪼개는 설계 방법을 익힙니다.

  • CTE를 활용해 유입 → 구매 전환율을 중간 결과를 검증하면서 계산할 수 있습니다.

1. CTE(WITH)란 무엇인가?

CTE(Common Table Expression)는 한국어로는 보통 “공통 테이블 표현식”이라고 번역하지만, 초보자에게는 이렇게 이해하시는 것이 가장 쉽습니다.

  • CTE = 이름을 붙인 중간 쿼리 결과입니다.

  • WITH 절에서 “중간 결과(가상 테이블)”를 여러 개 만들고, 마지막 SELECT에서 그것들을 조합합니다.

  • 결과적으로 긴 쿼리도 단계별 레고 조립처럼 읽히게 됩니다.

중요한 포인트는 CTE가 “파일로 저장되는 테이블”이 아니라, 쿼리 실행 중에만 존재하는 임시 결과라는 점입니다. 같은 쿼리 안에서만 유효합니다.

2. 언제 CTE를 쓰면 좋은가?

다음 상황에서는 CTE를 쓰는 편이 유지보수와 디버깅 면에서 확실히 유리합니다.

  • 집계가 여러 번 등장하는 경우: 유입 집계, 구매 집계, 재방문 집계처럼 단계가 많을 때입니다.

  • 필터 조건이 반복되는 경우: 날짜 범위, 국가, 채널 필터가 여러 곳에서 반복되면 실수 확률이 올라갑니다.

  • JOIN이 여러 번 중첩되는 경우: 서브쿼리로 감싸기 시작하면 읽는 사람이 길을 잃기 쉽습니다.

  • 중간 결과를 확인하고 싶은 경우: CTE는 “이 단계 결과를 먼저 보자”가 매우 쉽습니다.

3. WITH 절 기본 문법과 읽는 순서

CTE는 “위에서 아래로 정의하고, 마지막 SELECT에서 사용한다”가 핵심입니다. 아래 기본 문법을 먼저 눈에 익혀두시면 좋습니다.

코드 예시: CTE 기본 형태

WITH cte_name AS (
  SELECT ...
),
cte_name_2 AS (
  SELECT ...
)
SELECT ...
FROM cte_name
JOIN cte_name_2 ON ...;

  • WITH 다음에 CTE를 하나 이상 정의할 수 있습니다.

  • CTE는 쉼표(,)로 여러 개를 이어서 작성합니다.

  • 각 CTE는 이름 AS (쿼리) 형태입니다.

  • CTE 정의가 끝나면 마지막에 실제 결과를 반환할 SELECT가 나옵니다.

읽는 순서는 다음처럼 잡으시면 됩니다.

  • 1) “cte_name은 무엇을 만들지?”를 봅니다.

  • 2) “cte_name_2는 무엇을 만들지?”를 봅니다.

  • 3) 마지막 SELECT가 “위에서 만든 재료들을 어떻게 조합하는지”를 봅니다.

4. CTE vs 서브쿼리 vs 뷰 vs 임시 테이블

초보자 입장에서는 “서브쿼리로도 되는데 굳이 CTE가 필요할까?”라는 의문이 자연스럽습니다. 아래 표로 차이를 정리해두면 선택이 쉬워집니다.

방법 핵심 개념 장점 단점 추천 상황
CTE(WITH) 이름 붙인 중간 결과(쿼리 내부 임시) 가독성, 단계 분리, 디버깅 용이 DB/옵티마이저에 따라 성능 차이가 있을 수 있음 복잡한 집계/조인, 단계별 검증이 필요한 지표
서브쿼리 FROM/WHERE 안에 포함되는 쿼리 짧은 쿼리는 간단 중첩이 깊어지면 읽기 어려움 작은 범위의 단일 목적 처리
뷰(View) 저장된 쿼리(가상 테이블) 재사용, 표준화 운영/권한/변경 관리가 필요 여러 리포트에서 반복 사용되는 로직
임시 테이블 실제 테이블로 생성(세션/작업 단위) 큰 데이터에서 재사용 시 효율 가능 생성/정리 비용, 권한/정책 이슈 중간 결과를 여러 쿼리에서 반복 활용할 때

5. 디버깅 전략(중간 결과 확인): “CTE는 확인이 쉬워서 강력합니다”

CTE를 쓰는 순간 디버깅 난이도가 내려갑니다. 이유는 간단합니다. 각 단계가 “이름 있는 결과”이기 때문에, 필요하면 그 단계만 바로 조회할 수 있습니다.

5-1. 특정 CTE만 보고 싶을 때

마지막 SELECT를 잠깐 바꿔서 중간 결과를 직접 봅니다.

WITH inflow AS (
  SELECT ...
),
purchase AS (
  SELECT ...
)
SELECT *
FROM inflow
LIMIT 50;

  • 이 방식은 “유입 집계가 기대한 대로 나왔는지”를 가장 빠르게 확인할 수 있습니다.

  • 초보자는 LIMIT를 꼭 붙여서 결과를 작게 보면서 검증하는 습관이 좋습니다.

5-2. 단계별 행 개수(count)로 이상 징후 잡기

전환율 실습에서 가장 흔한 실수는 JOIN 때문에 행이 갑자기 늘어나거나(중복) 줄어드는(누락) 경우입니다. 이때는 단계별 count를 찍어보면 원인이 빨리 드러납니다.

WITH inflow AS (
  SELECT ...
),
purchase AS (
  SELECT ...
)
SELECT
  (SELECT COUNT(*) FROM inflow)   AS inflow_rows,
  (SELECT COUNT(*) FROM purchase) AS purchase_rows;

5-3. “중간 결과 컬럼”을 일부러 남겨서 추적하기

예를 들어 구매를 집계할 때 “어떤 유입 채널로 분류됐는지”가 의심스럽다면, 최종 계산 전에 채널 컬럼을 남긴 상태로 먼저 확인합니다. 전환율은 최종 수치만 보지 말고, “분해된 구성 요소”를 먼저 보셔야 합니다.

6. 실습: “유입→구매 전환율”을 단계별로 계산(CTE로 구조화)

이번 실습에서는 가장 단순한 형태의 퍼널을 가정합니다.

  • 유입(visit): 사이트 방문 이벤트

  • 구매(purchase): 주문/결제 완료 이벤트

  • 전환율: 구매 수 ÷ 유입 수

6-1. 예시 테이블(학습용 가정)

DB마다 실제 컬럼명은 다를 수 있으므로, 아래는 학습을 위한 예시입니다.

  • events(이벤트 로그): user_id, event_time, event_type(visit/purchase), channel

6-2. 전환율에서 “무엇을 분모/분자로 잡을지” 먼저 확정

전환율이 흔히 틀리는 이유는 “유입을 사용자 기준으로 셀지, 세션 기준으로 셀지, 이벤트 기준으로 셀지”가 정리되지 않아서입니다. 초보자 실습에서는 가장 안전한 방식으로 진행하겠습니다.

  • 유입 수(inflow_cnt) = 방문 이벤트 수(visit 이벤트 건수)

  • 구매 수(purchase_cnt) = 구매 이벤트 수(purchase 이벤트 건수)

  • 전환율(conversion_rate) = purchase_cnt / inflow_cnt

실무에서는 “사용자 기준 전환율(구매한 사용자 수 / 방문한 사용자 수)”도 자주 쓰지만, 우선은 CTE 구조화와 디버깅 감각을 잡는 것이 목표이므로 이벤트 건수 기준으로 시작합니다.

지표 정의 요약 표

지표 정의 초보자 실습에서의 장점 주의사항
유입 수 visit 이벤트 건수 집계가 단순함 동일 사용자의 반복 방문이 분모를 늘릴 수 있음
구매 수 purchase 이벤트 건수 집계가 단순함 부분취소/복수구매가 있으면 해석이 필요
전환율 구매 수 ÷ 유입 수 퍼널의 기본 형태를 빠르게 학습 0으로 나누기 방지(NULLIF 등) 필요

6-3. 단계별 CTE로 전환율 계산하기(가장 권장하는 구조)

아래 쿼리는 “기간 파라미터 → 유입 집계 → 구매 집계 → 합치기 → 전환율 계산” 순서로 설계했습니다. 실제 분석에서 가장 많이 쓰는 패턴입니다.

WITH
params AS (
  SELECT
    DATE '2025-01-01' AS start_date,
    DATE '2025-01-31' AS end_date
),
inflow AS (
  SELECT
    DATE(e.event_time) AS dt,
    e.channel,
    COUNT(*) AS inflow_cnt
  FROM events e
  JOIN params p ON 1=1
  WHERE e.event_type = 'visit'
    AND DATE(e.event_time) BETWEEN p.start_date AND p.end_date
  GROUP BY
    DATE(e.event_time),
    e.channel
),
purchase AS (
  SELECT
    DATE(e.event_time) AS dt,
    e.channel,
    COUNT(*) AS purchase_cnt
  FROM events e
  JOIN params p ON 1=1
  WHERE e.event_type = 'purchase'
    AND DATE(e.event_time) BETWEEN p.start_date AND p.end_date
  GROUP BY
    DATE(e.event_time),
    e.channel
),
merged AS (
  SELECT
    i.dt,
    i.channel,
    i.inflow_cnt,
    COALESCE(p.purchase_cnt, 0) AS purchase_cnt
  FROM inflow i
  LEFT JOIN purchase p
    ON i.dt = p.dt
   AND i.channel = p.channel
)
SELECT
  dt,
  channel,
  inflow_cnt,
  purchase_cnt,
  ROUND(purchase_cnt * 1.0 / NULLIF(inflow_cnt, 0), 4) AS conversion_rate
FROM merged
ORDER BY dt, channel;

6-4. 쿼리를 “단계별로” 해석하는 방법

  • params는 기간을 고정합니다. 날짜 범위를 여러 곳에서 반복 쓰지 않도록 “한 번만 정의”하는 역할입니다.

  • inflow는 방문(visit)만 골라서 날짜/채널별로 집계합니다. 이 단계에서 분모가 만들어집니다.

  • purchase는 구매(purchase)만 골라서 날짜/채널별로 집계합니다. 이 단계에서 분자가 만들어집니다.

  • merged는 유입을 기준으로 구매를 붙입니다. 그래서 LEFT JOIN을 사용합니다(구매가 0인 날/채널도 보고 싶기 때문입니다).

  • 최종 SELECT에서 conversion_rate를 계산합니다. 0으로 나누기를 막기 위해 NULLIF(inflow_cnt, 0)를 사용합니다.

6-5. 실습의 핵심: 중간 결과를 실제로 확인하면서 진행

초보자에게 가장 추천하는 방식은 “한 번에 완성하려고 하지 말고”, 아래처럼 중간 결과를 계속 확인하는 것입니다.

WITH
params AS (
  SELECT DATE '2025-01-01' AS start_date, DATE '2025-01-31' AS end_date
),
inflow AS (
  SELECT DATE(e.event_time) AS dt, e.channel, COUNT(*) AS inflow_cnt
  FROM events e
  JOIN params p ON 1=1
  WHERE e.event_type = 'visit'
    AND DATE(e.event_time) BETWEEN p.start_date AND p.end_date
  GROUP BY DATE(e.event_time), e.channel
)
SELECT *
FROM inflow
ORDER BY dt, channel
LIMIT 50;

  • 먼저 inflow만 확인해서 “날짜가 맞는지, 채널이 정상인지, 건수가 말이 되는지”를 체크합니다.

  • 그다음 purchase CTE를 붙이고 purchase만 조회해 동일하게 확인합니다.

  • 마지막으로 merged를 붙여 JOIN 결과가 중복/누락 없이 결합되는지 확인합니다.

7. 따라하기: 실습 체크리스트(초보자용)

  1. 데이터 확인(최소 점검)

    events 테이블에 어떤 event_type 값이 있는지 먼저 확인합니다. visit/purchase 값이 정확히 들어있는지 확인해야 이후 집계가 0으로 나오지 않습니다.

  2. 기간(params)부터 고정

    params CTE에 시작일/종료일을 적고, 이후 모든 집계가 이 기간을 사용하도록 만듭니다. 조건을 여러 곳에 흩뿌리면 디버깅이 어려워집니다.

  3. 유입(inflow)만 먼저 완성하고 출력

    inflow CTE를 만든 뒤, 마지막 SELECT를 inflow로 바꿔 결과를 직접 확인합니다. dt, channel, inflow_cnt가 기대와 맞아야 다음 단계로 넘어갑니다.

  4. 구매(purchase)도 동일하게 완성하고 출력

    purchase CTE를 붙이고 purchase만 조회해봅니다. 구매가 없더라도 “정말 구매가 없었는지” 데이터 관점에서 점검합니다.

  5. 병합(merged)에서 JOIN 키를 재확인

    dt와 channel을 기준으로 정확히 매칭되는지 확인합니다. join 조건이 어긋나면 전환율이 갑자기 튀거나 0으로 떨어집니다.

  6. 최종 전환율 계산은 마지막에

    NULLIF로 0 나누기를 방지하고, 필요하면 ROUND로 소수점 자릿수를 맞춥니다. 계산식은 쉽지만 “분모/분자 정의”가 흔히 틀립니다.

8. 초보자가 자주 하는 실수와 예방 팁

8-1. JOIN 때문에 행이 폭증(중복 집계)하는 문제

  • 증상: 유입/구매 건수가 말도 안 되게 커지거나, 전환율이 1을 훌쩍 넘어갑니다.

  • 원인: 집계 전 테이블끼리 먼저 JOIN을 해버리면, 1:N 관계에서 행이 늘어나면서 집계가 부풀려집니다.

  • 해결: 오늘 실습처럼 각각 먼저 집계(inflow/purchase)한 뒤 결과를 JOIN합니다.

8-2. 날짜 조건이 단계별로 다르게 들어가는 문제

  • 증상: 유입과 구매의 기간이 미묘하게 달라서 특정 날짜만 전환율이 튑니다.

  • 원인: WHERE 절을 여러 CTE에 복사해 붙이면서 일부가 누락되거나 값이 달라집니다.

  • 해결: params CTE로 기간을 “한 번만” 정의하고, 모든 단계가 동일한 params를 사용하도록 합니다.

8-3. 0으로 나누기 또는 NULL 처리 누락

  • 증상: 에러가 나거나, 전환율이 NULL로만 나옵니다.

  • 해결: NULLIF(inflow_cnt, 0)로 0 나누기를 막고, 구매가 없는 경우는 COALESCE(purchase_cnt, 0)로 0을 채웁니다.

8-4. 전환율의 정의가 바뀌었는데 쿼리는 그대로인 문제

  • 증상: 수치가 틀린 것 같지만 어디가 틀렸는지 감이 안 옵니다.

  • 예방: “유입/구매를 무엇 기준으로 세는지(이벤트/세션/사용자)”를 문장으로 먼저 적고, 그 정의대로 COUNT(*) 또는 COUNT(DISTINCT user_id)를 선택합니다.


이 포스팅은 쿠팡 파트너스 활동의 일환으로, 이에 따른 일정액의 수수료를 제공받습니다.

Reactions
martin blake

작성자: martin blake

댓글 쓰기

0 댓글