PostgreSQL 기초 강좌 #8 뷰·CTE·윈도 함수: 복잡한 쿼리를 읽기 쉽게

인프라 PostgreSQL 데이터베이스
4 분 소요

3편의 조인·집계가 조합되기 시작하면 쿼리는 금방 화면 하나를 넘습니다. 이번 편은 그 복잡함을 다루는 세 가지 도구입니다. 뷰는 쿼리에 이름을 붙이고, CTE는 쿼리를 단계로 나누고, 윈도 함수는 “그룹별 순위·비교"라는 집계로는 안 되는 계산을 열어 줍니다.

뷰: 반복되는 쿼리에 이름을 #

뷰 생성 
CREATE VIEW paid_order_stats AS
SELECT user_id, count(*) AS order_count, sum(amount_krw) AS total_krw
FROM orders
WHERE status = 'paid'
GROUP BY user_id;

SELECT * FROM paid_order_stats WHERE total_krw >= 100000;

뷰는 저장된 SELECT입니다. 데이터의 사본이 아니라 조회할 때마다 원본 쿼리가 실행되는 이름표라서, 항상 최신이고 저장 공간도 없습니다. 여러 곳에서 반복되는 조인·필터 조합(“결제 완료 주문”, “활성 사용자”)에 이름을 붙여 팀의 공용 어휘로 만드는 것이 뷰의 자리입니다.

집계가 무거워서 매번 실행하기 부담스러우면 구체화 뷰(materialized view)가 있습니다. CREATE MATERIALIZED VIEW는 결과를 물리적으로 저장해 조회는 빠르지만, 원본이 바뀌어도 REFRESH MATERIALIZED VIEW를 하기 전까지는 낡은 값입니다. “정확히 지금"이 필요하면 일반 뷰, “5분 전 기준이어도 되니 빠르게"면 구체화 뷰 + 주기적 REFRESH가 판단 기준입니다.

CTE: 쿼리를 위에서 아래로 서술하기 #

3편에서 중복 집계를 피하려고 서브쿼리를 조인했는데, 서브쿼리가 늘수록 쿼리는 안쪽부터 읽어야 하는 퍼즐이 됩니다. WITH 절(CTE, Common Table Expression)은 같은 일을 위에서 아래로 서술하게 해 줍니다.

CTE로 단계별 서술 
WITH order_stats AS (
    SELECT user_id, sum(amount_krw) AS total_krw
    FROM orders GROUP BY user_id
),
review_stats AS (
    SELECT user_id, count(*) AS review_count
    FROM reviews GROUP BY user_id
)
SELECT u.name, coalesce(os.total_krw, 0) AS total_krw, coalesce(rs.review_count, 0) AS review_count
FROM users u
LEFT JOIN order_stats os ON os.user_id = u.id
LEFT JOIN review_stats rs ON rs.user_id = u.id;

“주문 집계를 만들고, 리뷰 집계를 만들고, 사용자에 붙인다"라는 사고의 순서가 그대로 쿼리의 순서가 됩니다. 성능 면에서는 플래너가 CTE를 본문에 풀어 넣어(inline) 서브쿼리와 동등하게 취급하는 것이 현재의 기본 동작이라, 가독성 때문에 성능을 포기하는 것 아닌가 하는 걱정은 필요 없습니다. 확인이 필요하면 언제나처럼 EXPLAIN입니다.

윈도 함수: 행을 줄이지 않는 집계 #

GROUP BY는 그룹당 한 행으로 접습니다. 그런데 “각 주문 행에, 그 사용자의 주문 총액도 같이” 같은 요구는 행을 접으면 안 됩니다. 윈도 함수는 행을 그대로 둔 채, 각 행의 시야(윈도) 안에서 계산합니다.

윈도 함수 기본형 
SELECT id, user_id, amount_krw,
       sum(amount_krw) OVER (PARTITION BY user_id) AS user_total_krw,
       rank() OVER (ORDER BY amount_krw DESC)      AS overall_rank
FROM orders;

OVER가 윈도 함수의 표식이고, PARTITION BY는 “누구끼리 묶어 보는가”, 그 안의 ORDER BY는 “어떤 순서로 세는가"입니다. GROUP BY와 달리 결과 행 수는 그대로입니다.

대표 패턴이 3편에서 예고한 그룹별 상위 N건입니다.

사용자별 최신 주문 3건 
-- 사용자별 최신 주문 3건
SELECT *
FROM (
    SELECT o.*,
           ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
    FROM orders o
) t
WHERE rn <= 3;

ROW_NUMBER()가 사용자별로 최신부터 1, 2, 3…을 매기고, 바깥에서 3 이하만 남깁니다. 1건이면 3편의 DISTINCT ON이 짧고, N건부터는 이 패턴이 표준입니다. 윈도 함수의 결과는 WHERE에 직접 못 쓰므로(계산 순서상 WHERE가 먼저입니다) 서브쿼리나 CTE로 한 번 감싸는 모양까지가 세트입니다.

시계열 비교의 단골 LAG도 같은 구조입니다.

LAG로 전월 대비 
-- 월별 매출과 전월 대비
SELECT month, revenue,
       revenue - LAG(revenue) OVER (ORDER BY month) AS diff_from_prev
FROM monthly_revenue;

LAG는 윈도 순서상 앞 행의 값을 가져옵니다(첫 행은 NULL). 전월 대비, 직전 이벤트와의 간격처럼 “이웃 행과의 비교"가 전부 이 한 가지 형태로 풀립니다.

정리 #

  • 뷰는 저장된 SELECT이고 항상 최신입니다. 무거운 집계를 미리 계산해 두려면 구체화 뷰 + REFRESH입니다.
  • CTE는 쿼리를 위에서 아래로 서술하게 합니다. 플래너가 인라인 처리하므로 가독성의 대가로 성능을 내지 않습니다.
  • 윈도 함수는 행을 줄이지 않는 집계입니다. OVER + PARTITION BY + ORDER BY 세 부품이 전부입니다.
  • 그룹별 상위 N건은 ROW_NUMBER + 서브쿼리 필터가 표준 패턴입니다. 1건이면 DISTINCT ON이 지름길입니다.
  • LAG/LEAD로 이웃 행 비교(전월 대비 등)가 한 줄이 됩니다. 다음 편은 기초의 마무리, 롤·권한·백업입니다.
X