답변

누적 합계 및 누적 총계를 SQL에서 전통적으로 계산하는 방법은 윈도우 함수 (예: SUM() OVER(ORDER BY ...))를 사용하는 것이지만, 초기 또는 간소화된 데이터베이스 시스템에서는 서브쿼리 및 그룹화를 사용해야 합니다. 역사적으로 데이터베이스 설계자들은 윈도우 함수 지원을 포함한 SQL:2003 표준이 등장하기 전까지 우회 방법을 찾고 있었습니다.

문제 — 윈도우 함수가 없을 경우 각 행에 대해 모든 이전 값의 합계를 명시적으로 계산해야 하며, 이는 큰 데이터셋에서 O(N^2) 중첩 쿼리를 초래하게 됩니다.

해결책:

일반적으로 상호 연관된 서브쿼리나 값을 업데이트하는 임시 테이블을 사용합니다:

코드 예시:

-- 상관 서브쿼리를 통한 누적 합계
SELECT t1.id, t1.amount,
   (
     SELECT SUM(t2.amount)
     FROM transactions t2
     WHERE t2.id <= t1.id
   ) AS running_total
FROM transactions t1
ORDER BY t1.id;

-- 수동으로 값을 업데이트하는 임시 테이블 사용
CREATE TEMPORARY TABLE temp_running (id INT, amount INT, running_total INT);
-- 외부 코드(예: pl/pgsql)를 사용하여 행을 순회하면서 합계를 추가합니다.

주요 특징:

이 방법은 고유 정렬 기준(id, 생성일)이 있을 때만 작동합니다.
상관 서브쿼리는 스케일이 좋지 않으며, 실행 시간의 기하급수적 증가를 초래합니다.
큰 데이터 볼륨의 경우 SQL 외부에서 집계하는 ETL을 사용하는 것이 더 합리적입니다.

눈여겨봐야 할 질문.

상관 서브쿼리에서 ORDER BY가 보장된 정렬을 제공합니까?

아니오 — 서브쿼리는 결과에 직접 영향을 주지 않습니다. 최종 선택의 정렬은 항상 기반 쿼리에서 외부적으로 정의되며, 결과는 WHERE 필터링에만 의존합니다.

이러한 방식으로 누적 합계 계산을 병렬 처리할 수 있습니까?

아니오 — 순서가 매우 중요하며, 특히 이전 행에 의존하는 계산 시 단순한 병렬 처리가 불가능합니다.

대량의 행에서 상관 서브쿼리가 느린 이유는 무엇입니까?

각 행에 대해 이전 행 집합에 대한 합계를 다시 계산해야 하기 때문입니다. 이는 O(N^2) 작업을 초래합니다. 10만 행의 예시에서는 이미 몇 분 또는 몇 시간이 걸릴 수 있습니다.

일반적인 오류 및 안티패턴

실제 날짜 대신 id로 잘못 필터링 — id의 빈틈에서 합계가 "점프"합니다.
데이터 정렬 없이 합계를 계산하려고 시도
ETL 또는 파티셔닝 처리가 필요한 대규모 테이블에 이러한 접근 방식을 사용

실제 사례

부정적인 케이스

분석가는 상관 서브쿼리를 통해 날짜별 누적 수익을 계산했으나, 테이블에 간헐적으로 삭제된 id(빈틈)가 나타나고 있었습니다. 최종 합계는 비정상적으로 불규칙하여 날짜에 의존하지 않고 id의 순서에 따라 달라졌습니다.

장점:

소규모 선택에 대해 작동하며, 윈도우 함수를 요구하지 않습니다.

단점:

데이터가 부정확하며, 기대한 대로 계산되지 않습니다.
유지 보수가 어렵습니다.

긍정적인 케이스

엔지니어는 누적 합계 처리를 ETL 스크립트(Python/pandas)로 이동시켰고, 최종 값을 별도의 테이블에 로드하며 새로운 데이터를 동기화했습니다. 결과는 항상 날짜에 따라 일치하며, 코드는 수백만 개의 기록에 대해 빠르게 작동합니다.