CTE(Common Table Expression)와 비중(Portion) 활용한 할인 안분 계산

오랜만에 다시 블로그 포스팅을 남겨본다. 최근 진행하는 프로젝트 중 Postgres 를 많이 다루고 있는데 자주쓰는게 있어서 기록차원에서 남겨봄. CTE(Common Table Expression)에 대한 개념과 실무에서 자주 활용하는 비중(Portion) 기반의 할인 안분 계산 방법을 다뤄보려고 함. 이 내용을 알면, 복잡한 할인 계산을 단순화하고, SQL 쿼리의 가독성과 효율성을 높일 수 있음.

1️⃣ CTE(Common Table Expression) 개념과 원리

1. CTE란?

CTE (Common Table Expression)는 SQL의 WITH 절을 사용해 임시 테이블을 만드는 방법
서브쿼리와 비슷하지만, 한 번만 정의해 여러 번 재사용할 수 있다는 게 가장 큰 장점
SQL의 가독성을 높이고, 복잡한 쿼리를 단순화할 때 아주 유용함
서브쿼리와의 차이점: 서브쿼리는 중첩된 형태로 작성되며 매번 새로 계산되지만, CTE는 이름을 지정해 한 번 정의한 뒤 여러 번 재사용할 수 있음. 그래서 가독성과 성능 측면에서 더 유리함.

2. CTE의 문법

WITH cte_name AS (
    -- 하위 쿼리 (임시 테이블)
    SELECT column1, column2
    FROM some_table
    WHERE some_condition
)
-- 메인 쿼리 (CTE를 참조)
SELECT * FROM cte_name;

3. CTE의 활용 사례

복잡한 서브쿼리를 단순화하고, 중복 계산을 피할 수 있음.
재귀 쿼리: 계층 구조(조직도, 카테고리) 데이터를 다룰 때 자주 써.
집계 및 임시 테이블 생성: 임시로 집계한 데이터를 메인 쿼리에서 재사용할 때도 좋아.
쿼리 재사용: CTE는 여러 메인 쿼리에서 참조할 수 있기 때문에, 반복 작업을 줄일 수 있어.

2️⃣ 비중(Portion) 기반의 할인 안분 계산

1. 문제 정의

판매 내역 데이터를 기준으로, 장바구니 기준 할인과 상품 기준 할인을 동시에 계산해야 해.

목표

이 글의 목표는 할인 안분 계산의 실전 적용 방법을 명확히 이해하고, 이를 활용해 효율적인 SQL 쿼리 작성 능력을 키우는 것이야. 구체적으로, 다음 두 가지를 다룰 거야.

장바구니 기준 할인: 장바구니(cart_id)별 총 할인 금액을 각 상품의 금액 비중으로 나눔.
상품 기준 할인: 상품(product_id)별 총 할인 금액을 각 상품의 수량 비중으로 나눔.

할인 공식

장바구니 기준 상품별 할인 = (상품 금액 / 장바구니 총 금액) × 장바구니 할인 금액
상품 기준 상품별 할인 = (상품 수량 / 상품 총 수량) × 상품별 할인 금액

3️⃣ 예제 테이블 및 데이터

1. 테이블 생성

CREATE temp TABLE t_sales (
    order_id SERIAL PRIMARY KEY,       -- 주문 ID
    cart_id INT NOT NULL,               -- 장바구니 ID
    product_id INT NOT NULL,            -- 상품 ID
    product_name VARCHAR(100) NOT NULL, -- 상품 이름
    quantity INT NOT NULL,              -- 판매 수량
    price NUMERIC(10, 2) NOT NULL,      -- 단가 (개당 가격)
    cart_discount NUMERIC(10, 2),       -- 장바구니 기준 할인 금액
    product_discount NUMERIC(10, 2)     -- 상품 기준 할인 금액
);

2. 데이터 삽입

INSERT INTO t_sales (cart_id, product_id, product_name, quantity, price, cart_discount, product_discount) VALUES
(1, 101, '노트북', 1, 1000.00, 100.00, 50.00), 
(1, 102, '마우스', 2, 25.00, 100.00, 10.00), 
(1, 103, '키보드', 1, 50.00, 100.00, 5.00), 
(2, 101, '노트북', 2, 1000.00, 200.00, 80.00), 
(2, 104, '모니터', 1, 300.00, 200.00, 40.00), 
(3, 105, 'USB 메모리', 3, 15.00, 50.00, 5.00), 
(3, 103, '키보드', 2, 50.00, 50.00, 10.00), 
(4, 102, '마우스', 5, 25.00, 30.00, 5.00), 
(4, 105, 'USB 메모리', 1, 15.00, 30.00, 2.00), 
(5, 104, '모니터', 3, 300.00, 0.00, 60.00);

4️⃣ 최적화된 CTE 쿼리

쿼리 작성

이 쿼리는 장바구니와 상품 기준으로 할인을 동시에 계산하는 예시야. 여기서의 목표는 중복 계산을 피하고, 효율성을 높이는 CTE 활용법을 보여주는 것이야. CTE에서 미리 비중(Portion)을 구해두고, 최종 쿼리에서 간단히 결과를 출력하는 방식으로 쿼리 성능을 최적화했어.

WITH cart_summary AS (
    -- 장바구니(cart_id)별 총 금액 및 총 할인 금액
    SELECT 
        cart_id,
        SUM(quantity * price) AS total_cart_price,
        SUM(cart_discount) AS total_cart_discount
    FROM t_sales
    GROUP BY cart_id
), 
product_summary AS (
    -- 상품(product_id)별 총 판매 수량 및 총 할인 금액
    SELECT 
        product_id,
        product_name,
        SUM(quantity) AS total_quantity,
        SUM(quantity * price) AS total_product_sales,
        SUM(product_discount) AS total_product_discount
    FROM t_sales
    GROUP BY product_id, product_name
), 
portion_calculations AS (
    -- CTE 레벨에서 장바구니와 상품의 비중(Portion)을 미리 계산
    SELECT 
        ts.order_id,
        ts.cart_id,
        ts.product_id,
        ts.product_name,
        ts.quantity,
        ts.price,
        ts.quantity * ts.price AS product_price,
        -- 비중 (상품 금액 / 장바구니 총 금액)
        (ts.quantity * ts.price) / cs.total_cart_price AS cart_portion,
        -- 비중 (상품 수량 / 상품 총 수량)
        ts.quantity / ps.total_quantity AS product_portion,
        -- 장바구니 기준 할인 (비중 * 장바구니 할인)
        ((ts.quantity * ts.price) / cs.total_cart_price) * cs.total_cart_discount AS cart_discount_allocation,
        -- 상품 기준 할인 (비중 * 상품 할인)
        (ts.quantity / ps.total_quantity) * ps.total_product_discount AS product_discount_allocation
    FROM t_sales ts
    JOIN cart_summary cs ON ts.cart_id = cs.cart_id
    JOIN product_summary ps ON ts.product_id = ps.product_id
)
-- 최종 결과 출력
SELECT 
    order_id,
    cart_id,
    product_id,
    product_name,
    quantity,
    price,
    product_price,
    ROUND(cart_portion, 4) AS cart_portion,
    ROUND(product_portion, 4) AS product_portion,
    ROUND(cart_discount_allocation, 2) AS cart_discount_allocation,
    ROUND(product_discount_allocation, 2) AS product_discount_allocation,
    ROUND(cart_discount_allocation + product_discount_allocation, 2) AS total_discount
FROM portion_calculations;

5️⃣ 최종 정리

왜 이렇게 하는 걸까?

효율성: 비중(Portion)을 미리 계산해두면 중복 연산을 줄일 수 있어. 예를 들어, 한 번의 쿼리 실행으로 총 실행 시간이 5초에서 3초로 줄어들 수 있어.
가독성: 메인 쿼리가 더 단순해져서, 유지보수가 쉬워. 불필요한 중복 계산이 없어지니까 쿼리의 흐름이 명확해져.
성능 최적화: CTE 레벨에서 미리 계산을 마치고 메인 쿼리에서는 간단히 조합만 하니까, 전체 실행 속도가 더 빨라져. 특히, 대용량 데이터셋에서도 효율적이야.

6️⃣ 마무리

이번 글에서는 CTE(Common Table Expression)와 비중(Portion) 기반의 할인 안분 계산 방법을 다뤘어. 장바구니 기준 할인과 상품 기준 할인을 동시에 계산하는 실전 예시도 소개했으니까, 직접 DBeaver에서 실행해보면서 익혀보길 추천할게!

💡 이 글이 유익했다면 댓글로 피드백 부탁해!

궁금한 부분이 있으면 댓글로 남겨줘. 추가로 설명할게!