윈도우 함수를 사용하여 SQL 쿼리 간소화하기

소개: SQL 데이터 분석의 진화

관계형 데이터베이스 영역에서 분석가와 개발자는 종종 그룹 전체 데이터 집계, 파티션 내 값 비교 또는 실행 합계 계산과 같이 복잡한 데이터 조작이 필요한 시나리오에 직면합니다. 전통적으로 이러한 작업은 여러 서브쿼리와 셀프 조인을 포함하는 복잡한 SQL 쿼리로 이어지는 경우가 많았습니다. 효과적이긴 하지만 이러한 방법은 읽고, 최적화하고, 유지 관리하기 어려울 수 있으며, 쿼리의 실제 의도를 모호하게 하고 때로는 성능 병목 현상을 초래하기도 합니다. 그러나 현대 SQL은 더 우아하고 강력한 솔루션을 제공합니다. 바로 윈도우 함수입니다. 이러한 함수는 단일 요약 행으로 축소하지 않고 현재 행과 관련된 테이블 행 집합에 대한 계산을 수행하는 간결하고 표현력 있는 방법을 제공합니다. 이 문서에서는 윈도우 함수가 SQL 쿼리를 크게 간소화하여 가독성, 효율성 및 이해도를 높이는 방법을 자세히 살펴보고, 한때 필수적이었던 복잡한 서브쿼리와 셀프 조인이 필요했던 많은 시나리오를 대체할 것입니다.

윈도우 함수의 힘 이해하기

실제 예제를 살펴보기 전에 윈도우 함수의 유용성을 뒷받침하는 핵심 개념을 명확히 하겠습니다.

• 윈도우 함수: 현재 행과 관련이 있는 테이블 행 집합 전체에서 계산을 수행하는 함수입니다. SUM(), AVG(), COUNT()와 같은 집계 함수가 행을 단일 출력 행으로 집계하는 것과 달리 윈도우 함수는 원본 쿼리 결과의 각 행에 대해 값을 반환합니다.
• OVER() 절: 모든 윈도우 함수의 핵심입니다. 윈도우 함수가 작동하는 "윈도우" 또는 행 집합을 정의합니다. 세 가지 선택적 하위 절을 포함할 수 있습니다.
  • PARTITION BY: 윈도우 함수가 독립적으로 적용되는 파티션(행 그룹)으로 쿼리 결과 집합을 나눕니다. 이를 윈도우 함수의 GROUP BY로 생각할 수 있지만 행을 축소하지는 않습니다.
  • ORDER BY: 각 파티션 내의 행을 정렬합니다. 이는 ROW_NUMBER(), RANK(), LAG(), LEAD()와 같은 함수에 의존하거나 누적 합계를 계산하는 데 중요합니다.
  • ROWS / RANGE Preceding and Following: 각 파티션 내에서 슬라이딩 프레임을 정의하여 현재 행과 관련된 행 중 어떤 행을 계산에 포함해야 하는지 지정합니다. 이는 이동 평균 또는 특정 수의 이전 또는 다음 행에 대한 실행 합계에 특히 유용합니다.

마법은 어떻게 작동할까요?

기본 원칙은 윈도우 함수가 FROM, WHERE, GROUP BY, HAVING 절이 처리된 후에 메인 쿼리의 ORDER BY 절 이전에 행의 "윈도우"에서 작동한다는 것입니다. 이를 통해 개별 행 컨텍스트를 잃지 않고 관련 행 집합을 "볼" 수 있습니다.

예제를 통한 적용 및 구현

실제 예제를 통해 윈도우 함수의 강력함을 설명하고, 가능한 경우 기존 방법과 비교해 보겠습니다.

다음 스키마와 샘플 데이터를 가진 Sales라는 테이블을 고려해 보겠습니다.

CREATE TABLE Sales (
    SaleID INT PRIMARY KEY,
    StoreID INT,
    SaleDate DATE,
    Amount DECIMAL(10, 2)
);

INSERT INTO Sales (SaleID, StoreID, SaleDate, Amount) VALUES
(1, 101, '2023-01-05', 100.00),
(2, 102, '2023-01-05', 150.00),
(3, 101, '2023-01-06', 120.00),
(4, 103, '2023-01-06', 90.00),
(5, 102, '2023-01-07', 200.00),
(6, 101, '2023-01-07', 110.00),
(7, 103, '2023-01-08', 130.00);

예제 1: 각 매장 내에서의 판매 순위 매기기

문제: Amount를 기준으로 각 판매를 해당 매장 내에서 순위를 매깁니다.

전통적인 접근 방식 (서브쿼리/셀프 조인 사용은 더 많은 복잡성을 포함할 수 있으며, 직접적인 순위 지정을 위해 여기서는 일반적인 셀프 조인 로직을 보여줍니다.):

이 접근 방식은 실제 순위 지정 로직에 대해 매우 복잡해질 수 있으며, 특히 순위 지정 함수를 사용하지 않는 경우 종종 조건을 충족하는 행 수를 계산해야 하므로 대규모 데이터 세트에 대한 성능 문제가 발생합니다. 실제 순위 지정이 아닌 더 직접적인 셀프 조인은 비교를 위해 사용될 수 있지만 실제 순위는 아닙니다. 비교를 위해 각 매장의 최대값을 찾는 간단한 예를 상상해 보겠습니다.

-- 설명 예제, 실제 순위는 아님
SELECT 
    S1.SaleID,
    S1.StoreID,
    S1.SaleDate,
    S1.Amount,
    (SELECT MAX(S2.Amount) FROM Sales S2 WHERE S2.StoreID = S1.StoreID) AS MaxStoreSale
FROM 
    Sales S1;

이 서브쿼리는 최대값만 찾고 순위를 매기지는 않습니다. 윈도우 함수 없이 실제로 순위를 매기려면 종종 복잡한 셀프 조인, 행 수 세기 또는 임시 테이블이 필요하므로 번거롭습니다.

윈도우 함수 접근 방식:

SELECT
    SaleID,
    StoreID,
    SaleDate,
    Amount,
    RANK() OVER (PARTITION BY StoreID ORDER BY Amount DESC) AS RankInStore
FROM
    Sales;

설명: PARTITION BY StoreID는 판매를 각 매장별 별도의 그룹으로 나눕니다. 그런 다음 ORDER BY Amount DESC는 각 매장 내에서 가장 높은 Amount부터 가장 낮은 Amount까지 판매 순위를 매깁니다. RANK()는 동점인 경우 숫자를 건너뛰면서 순위를 할당합니다. 동점 처리 요구 사항에 따라 ROW_NUMBER()(고유한 순차 번호 할당) 또는 DENSE_RANK()(간격 없이 밀집 순위 할당)와 같은 다른 순위 지정 함수도 사용할 수 있습니다.

예제 2: 매장별 판매 실행 합계 계산

문제: 각 판매에 대해 해당 매장에서 해당 날짜까지 판매된 실행 합계를 계산합니다.

전통적인 접근 방식 (서브쿼리 사용):

SELECT
    S1.SaleID,
    S1.StoreID,
    S1.SaleDate,
    S1.Amount,
    (SELECT SUM(S2.Amount)
     FROM Sales S2
     WHERE S2.StoreID = S1.StoreID 
       AND S2.SaleDate <= S1.SaleDate) AS RunningTotal
FROM
    Sales S1
ORDER BY
    S1.StoreID, S1.SaleDate;

이 중첩된 서브쿼리는 외부 쿼리의 각 행에 대해 실행되며, 적절하게 인덱싱되지 않은 경우 특히 대규모 데이터 세트의 경우 매우 비효율적일 수 있습니다.

윈도우 함수 접근 방식:

SELECT
    SaleID,
    StoreID,
    SaleDate,
    Amount,
    SUM(Amount) OVER (PARTITION BY StoreID ORDER BY SaleDate) AS RunningTotal
FROM
    Sales
ORDER BY
    StoreID, SaleDate;

설명: 여기서 PARTITION BY StoreID는 다시 매장별 그룹을 만듭니다. ORDER BY SaleDate는 각 매장 내에서 날짜 순서대로 합계가 계산되도록 합니다. 기본적으로 ORDER BY를 사용한 SUM()은 윈도우 프레임 내에서 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW를 의미하며, 실행 합계가 생성됩니다.

예제 3: 동일 매장의 현재 판매와 이전 판매 비교

문제: 각 판매에 대해 동일 매장의 이전 판매 금액을 찾습니다.

전통적인 접근 방식 (셀프 조인 사용):

SELECT
    S1.SaleID,
    S1.StoreID,
    S1.SaleDate,
    S1.Amount,
    S2.Amount AS PreviousSaleAmount
FROM
    Sales S1
LEFT JOIN
    Sales S2 ON S1.StoreID = S2.StoreID AND S2.SaleDate < S1.SaleDate
LEFT JOIN ( -- 직전 판매를 찾기 위한 서브쿼리
    SELECT
        StoreID,
        SaleDate,
        MAX(SaleDate) AS MaxPreviousDate
    FROM
        Sales
    GROUP BY
        StoreID, SaleDate
) AS MaxPrev ON S1.StoreID = MaxPrev.StoreID AND S1.SaleDate = MaxPrev.SaleDate AND S2.SaleDate = MaxPrev.MaxPreviousDate
ORDER BY
    S1.StoreID, S1.SaleDate;

이 셀프 조인은 직전 행을 정확하게 식별하기가 매우 까다로우며, 종종 조인 조건 내에 추가 서브쿼리가 필요하거나 MAX 집계를 사용해야 하므로 쿼리가 매우 장황해지고 디버그하기 어렵습니다.

윈도우 함수 접근 방식:

SELECT
    SaleID,
    StoreID,
    SaleDate,
    Amount,
    LAG(Amount, 1, 0) OVER (PARTITION BY StoreID ORDER BY SaleDate) AS PreviousSaleAmount
FROM
    Sales
ORDER BY
    StoreID, SaleDate;

설명: LAG() 함수를 사용하면 동일한 파티션 내의 이전 행에서 데이터에 액세스할 수 있습니다. LAG(Amount, 1, 0)는 현재 행 이전 1행의 Amount를 가져온다는 것을 의미합니다. 이전 행이 없는 경우(예: 매장의 첫 번째 판매) 기본값은 0입니다. LEAD()는 후속 행의 데이터에 액세스하기 위해 유사하게 사용할 수 있습니다.

결론: SQL 쿼리의 패러다임 전환

윈도우 함수는 SQL에서 복잡한 데이터 분석에 접근하는 방식을 근본적으로 변화시킵니다. 전체 결과 집합을 집계하지 않고 정의된 행 집합에 대한 계산을 수행하는 메커니즘을 제공함으로써 많은 복잡한 서브쿼리와 리소스 집약적인 셀프 조인의 필요성을 없앱니다. 결과적으로 SQL 코드는 더 간결하고, 읽기 쉽고, 유지 관리하기 쉬우며, 종종 성능이 훨씬 뛰어납니다. 윈도우 함수를 채택하는 것은 단순히 더 짧은 SQL을 작성하는 것이 아니라 데이터베이스 쿼리 내에서 복잡한 분석 패턴을 표현하는 보다 선언적이고 효율적인 방법을 채택하는 것입니다. 이는 모든 진지한 SQL 개발자나 데이터 분석가를 위한 필수 도구입니다.