SQL 윈도우 함수를 사용한 복잡한 집계 최적화

소개

데이터베이스 상호 작용의 세계에서 개발자는 종종 관련 데이터에 대한 복잡한 집계를 요구하는 시나리오를 접하게 됩니다. 일반적이지만 비효율적인 패턴은 "N+1 쿼리" 문제입니다. 이는 일반적으로 부모 레코드 집합을 가져오는 초기 쿼리와, 그에 연결된 자식 레코드를 검색하고 집계하기 위한 N개의 추가 쿼리(각 부모마다 하나씩)를 포함합니다. 구현이 간단해 보이지만, 이 접근 방식은 부모 레코드 수가 늘어남에 따라 성능 병목 현상이 빠르게 발생하며 과도한 데이터베이스 왕복과 지연 시간 증가로 이어집니다. 이 글에서는 SQL 윈도우 함수가 이 문제에 대한 강력하고 우아한 솔루션을 어떻게 제공하는지 살펴봅니다. 이를 통해 단일의 고도로 최적화된 쿼리로 정교한 집계를 가능하게 하여 애플리케이션 성능을 크게 향상시키고 코드를 단순화합니다.

핵심 개념

실제 적용 사례를 자세히 살펴보기 전에, 논의의 기반이 되는 핵심 개념에 대한 명확한 이해를 정립해 봅시다:

N+1 쿼리 문제: 위에서 설명한 대로, 이 안티패턴은 기본 레코드 모음(N)을 가져온 다음 해당 N개 레코드 각각에 대한 추가 쿼리를 실행하여 관련 데이터나 집계 데이터를 가져오는 것을 포함합니다. 예를 들어, 부서 목록을 가져온 다음 총 직원 급여를 계산하기 위해 각 부서에 대해 별도의 쿼리를 실행하는 것입니다.
윈도우 함수: 전체 행 그룹에 작동하고 그룹당 단일 값을 반환하는 집계 함수(예: SUM, AVG, COUNT)와 달리, 윈도우 함수는 OVER() 절로 지정된 행의 "윈도우" 또는 "프레임"에서 작동합니다. 윈도우 내의 행에 따라 쿼리 결과의 각 행에 대한 값을 물론, 윈도우 내의 행에 따라 값을 반환합니다. 이것은 중요한 구분점입니다. 집계 함수는 행을 축소하는 반면, 윈도우 함수는 행을 축소하지 않고 기존 행에 계산된 값을 추가합니다.
OVER() 절: 이것이 윈도우 함수의 핵심입니다. 함수가 작동하는 "윈도우"를 정의합니다. 여기에는 다음이 포함될 수 있습니다:
- PARTITION BY: 행을 독립적인 그룹 또는 파티션으로 나눕니다. 윈도우 함수는 각 파티션에 개별적으로 적용됩니다. 이는 GROUP BY와 유사하지만 행을 축소하지는 않습니다.
- ORDER BY: 각 파티션 내에서 행을 정렬합니다. 이는 ROW_NUMBER(), RANK()와 같은 함수나 누적 합계를 계산하는 데 중요합니다.
- ROWS/RANGE 절: 현재 행을 기준으로 프레임을 지정하여 파티션 내의 윈도우를 더욱 구체화합니다(예: ROWS BETWEEN 1 PRECEDING AND CURRENT ROW).

윈도우 함수로 N+1 쿼리 대체하기

윈도우 함수의 강력함은 단일 쿼리 내에서 관련 데이터 세트에 대한 계산을 수행하여 반복적인 클라이언트 측 처리나 여러 데이터베이스 호출의 필요성을 없애는 능력에 있습니다.

문제 설명

일반적인 시나리오를 생각해 봅시다. Orders와 OrderItems라는 두 개의 테이블이 있습니다.

-- Orders 테이블
CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    total_amount DECIMAL(10, 2)
);

-- OrderItems 테이블
CREATE TABLE OrderItems (
    item_id INT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT,
    price_per_unit DECIMAL(10, 2)
);

-- 샘플 데이터
INSERT INTO Orders (order_id, customer_id, order_date, total_amount) VALUES
(101, 1, '2023-01-15', 150.00),
(102, 2, '2023-01-16', 200.00),
(103, 1, '2023-01-17', 75.00),
(104, 3, '2023-01-18', 300.00),
(105, 2, '2023-01-19', 120.00);

INSERT INTO OrderItems (item_id, order_id, product_id, quantity, price_per_unit) VALUES
(1, 101, 10, 2, 50.00),
(2, 101, 11, 1, 50.00),
(3, 102, 12, 3, 40.00),
(4, 102, 13, 1, 80.00),
(5, 103, 10, 1, 75.00),
(6, 104, 14, 2, 100.00),
(7, 104, 15, 1, 100.00),
(8, 105, 11, 2, 60.00);

N+1 접근 방식 예시:

각 주문과 해당 주문의 총 품목 수량을 검색한다고 가정해 봅시다.

첫 번째 쿼리 (N=1): 모든 주문을 가져옵니다.

SELECT order_id, customer_id, order_date, total_amount FROM Orders;

이 쿼리는 다음을 반환합니다:

order_id | customer_id | order_date | total_amount
---------|-------------|------------|--------------
101      | 1           | 2023-01-15 | 150.00
102      | 2           | 2023-01-16 | 200.00
...

후속 쿼리 (N개 쿼리): 첫 번째 쿼리의 각 order_id에 대해 수량을 합산하는 별도의 쿼리를 실행합니다. order_id = 101의 경우:

SELECT SUM(quantity) FROM OrderItems WHERE order_id = 101; -- 결과: 3

order_id = 102의 경우:

SELECT SUM(quantity) FROM OrderItems WHERE order_id = 102; -- 결과: 4

...모든 주문에 대해 계속됩니다.

이 접근 방식은 데이터베이스에 1 + N개의 쿼리가 포함되어 있어 주문 수가 많을 경우 매우 비효율적입니다.

윈도우 함수 솔루션

단일 쿼리를 사용하여 윈도우 함수로 동일한 결과를 얻을 수 있습니다. 주문별로 quantity의 합계를 계산하고 싶지만 OrderItems (또는 OrderItems와 조인된 Orders)의 모든 행을 반환하면서 총 수량을 첨부하고 싶습니다.

SELECT
    o.order_id,
    o.customer_id,
    oi.item_id,
    oi.product_id,
    oi.quantity,
    SUM(oi.quantity) OVER (PARTITION BY o.order_id) AS total_order_quantity
FROM
    Orders o
JOIN
    OrderItems oi ON o.order_id = oi.order_id
ORDER BY
    o.order_id, oi.item_id;

설명:

Orders와 OrderItems를 JOIN하여 관련 데이터를 단일 결과 집합으로 가져옵니다.
SUM(oi.quantity) OVER (PARTITION BY o.order_id)가 핵심입니다.
- SUM(oi.quantity): 집계 함수입니다.
- OVER (...): 윈도우 함수임을 지정합니다.
- PARTITION BY o.order_id: SUM 함수에 각 고유 order_id에 대한 수량 합계를 계산하도록 지시합니다. 이 계산은 각 order_id에 대해 다시 시작됩니다. 중요한 것은 이 합계를 행을 축소하는 대신 해당 주문에 속하는 모든 행에 첨부한다는 것입니다.

결과 출력 (부분):

order_id | customer_id | item_id | product_id | quantity | total_order_quantity
---------|-------------|---------|------------|----------|----------------------
101      | 1           | 1       | 10         | 2        | 3.00
101      | 1           | 2       | 11         | 1        | 3.00
102      | 2           | 3       | 12         | 3        | 4.00
102      | 2           | 4       | 13         | 1        | 4.00
103      | 1           | 5       | 10         | 1        | 1.00
...

total_order_quantity가 각 주문에 대해 올바르게 계산되었으며 해당 주문에 속하는 모든 항목 행에 나타나는 것을 볼 수 있습니다. 각 주문당 total_order_quantity만 (개별 항목 없이) 필요한 경우 GROUP BY 절과 집계 함수를 함께 사용할 수 있지만, 세부 데이터와 집계 컨텍스트를 함께 필요로 할 때는 윈도우 함수가 이상적입니다.

더 복잡한 시나리오: 순위 및 누적 합계

윈도우 함수는 단순 합계 이상의 시나리오에서 탁월합니다.

예시: 고객별 총 금액 기준으로 주문 순위 매기기

각 고객의 주문을 total_amount 기준으로 최신순으로 순위를 매기고 싶다고 가정해 봅시다.

SELECT
    order_id,
    customer_id,
    order_date,
    total_amount,
    RANK() OVER (PARTITION BY customer_id ORDER BY order_date DESC) as customer_order_rank
FROM
    Orders
ORDER BY
    customer_id, customer_order_rank;

여기서:

PARTITION BY customer_id: 각 고객에 대해 순위가 독립적으로 계산됩니다.
ORDER BY order_date DESC: 각 고객 파티션 내에서 주문은 날짜 기준으로 내림차순(최신순)으로 정렬됩니다.
RANK(): 각 주문에 순위를 할당합니다. 두 주문이 고객 파티션 내에서 동일한 order_date를 갖는 경우 동일한 순위를 받으며 다음 순위는 건너뜁니다. (동일한 순위를 건너뛰고 싶지 않다면 DENSE_RANK()를, 고유한 순차 번호를 원한다면 ROW_NUMBER()를 고려하십시오.)

예시: 고객별 주문 금액 누적 합계 계산

날짜별로 정렬된 각 고객의 주문 금액 누적 합계를 계산하려면:

SELECT
    order_id,
    customer_id,
    order_date,
    total_amount,
    SUM(total_amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_total_customer_amount
FROM
    Orders
ORDER BY
    customer_id, order_date;

이 경우 PARTITION BY customer_id ORDER BY order_date와 함께 사용된 SUM()은 각 고객의 total_amount 값을 연대순으로 누적합니다.

윈도우 함수 사용의 이점

성능 향상: N+1 쿼리에 비해 데이터베이스 왕복 횟수를 대폭 줄입니다. 단일의 잘 최적화된 쿼리는 거의 항상 여러 개의 작은 쿼리보다 빠릅니다.
코드 가독성 및 단순성: SQL 쿼리 자체 내에서 복잡한 로직을 간결하게 표현하여 코드를 더 쉽게 이해하고 유지 관리할 수 있습니다. 집계 로직을 애플리케이션 코드 곳곳에 분산시키는 대신 원본 데이터 소스에 가깝게 유지합니다.
유연성: PARTITION BY, ORDER BY, 프레임 절 (ROWS/RANGE)을 갖춘 OVER() 절은 정확한 계산 윈도우를 정의하는 데 엄청난 유연성을 제공합니다.
데이터베이스 최적화: 최신 SQL 엔진은 윈도우 함수에 대해 고도로 최적화되어 있으며 종종 매우 효율적으로 실행합니다.

결론

"N+1 쿼리" 문제는 데이터베이스 기반 애플리케이션에서 성능을 저하시키는 숨겨진 문제입니다. SQL 윈도우 함수는 복잡한 집계 요구 사항을 해결하는 데 있어 우아하고 성능이 뛰어나며 매우 읽기 쉬운 대안을 제공합니다. 단일 쿼리 내에서 관련 행 집합에 대한 계산을 허용함으로써, 비효율적인 반복 프로세스를 간소화된 데이터베이스 최적화 작업으로 전환합니다. 윈도우 함수를 채택하는 것은 더 효율적이고 유지 관리하기 쉬우며 확장 가능한 SQL을 작성하기 위한 중요한 단계입니다.