REMINDER 06-04. 기간별 반복 집계는 조건부 집계로 통합하자

상황: 전체 회원별로 2022년 12월과 2023년 12월의 주문금액을 비교한다. 회원에게 해당 기간 주문이 없어도 회원 행은 보존하고 합계는 NULL로 표현한다. 실제 일반 테이블 startdbpg.ms_mbr, startdbpg.tr_ord에서 검증했다.

BAD SQL — 두 기간을 회원마다 각각 스칼라 서브쿼리로 집계

SELECT m.mbr_id,
       (SELECT SUM(o.ord_amt)
        FROM   startdbpg.tr_ord o
        WHERE  o.mbr_id = m.mbr_id
          AND  o.ord_dtm >= TIMESTAMP '2022-12-01'
          AND  o.ord_dtm <  TIMESTAMP '2023-01-01') amt_2212,
       (SELECT SUM(o.ord_amt)
        FROM   startdbpg.tr_ord o
        WHERE  o.mbr_id = m.mbr_id
          AND  o.ord_dtm >= TIMESTAMP '2023-12-01'
          AND  o.ord_dtm <  TIMESTAMP '2024-01-01') amt_2312
FROM   startdbpg.ms_mbr m;

SQL

복사

실제 실행계획에서 SubPlan 1과 SubPlan 2가 각각 loops=9999로 반복됐다. 주문을 회원별로 재검색하면서 날짜 조건은 Filter로 적용했다.

GOOD SQL — 범용 CASE WHEN 조건부 집계로 통합 (주 예제)

SELECT m.mbr_id, a.amt_2212, a.amt_2312
FROM   startdbpg.ms_mbr m
LEFT JOIN (
    SELECT o.mbr_id,
           SUM(CASE
                 WHEN o.ord_dtm >= TIMESTAMP '2022-12-01'
                  AND o.ord_dtm <  TIMESTAMP '2023-01-01'
                 THEN o.ord_amt
               END) amt_2212,
           SUM(CASE
                 WHEN o.ord_dtm >= TIMESTAMP '2023-12-01'
                  AND o.ord_dtm <  TIMESTAMP '2024-01-01'
                 THEN o.ord_amt
               END) amt_2312
    FROM   startdbpg.tr_ord o
    WHERE  (o.ord_dtm >= TIMESTAMP '2022-12-01'
        AND o.ord_dtm <  TIMESTAMP '2023-01-01')
       OR  (o.ord_dtm >= TIMESTAMP '2023-12-01'
        AND o.ord_dtm <  TIMESTAMP '2024-01-01')
    GROUP BY o.mbr_id
) a
  ON   a.mbr_id = m.mbr_id;

SQL

복사

서로 다른 두 기간의 주문을 검색한 후 회원별 GROUP BY 한 번으로 두 금액을 계산한다. CASE에는 ELSE 0을 넣지 않는다. 그래야 해당 기간 주문이 없는 경우 기존 SUM의 NULL 의미가 유지된다. LEFT JOIN은 두 기간 모두 주문이 없는 회원을 보존한다.

보충 — PostgreSQL에서 사용할 수 있는 FILTER 표현

-- 위 GOOD의 인라인 뷰 SELECT 안에서 CASE 집계 두 개 대신 사용 가능
SELECT o.mbr_id,
       SUM(o.ord_amt) FILTER (
           WHERE o.ord_dtm >= TIMESTAMP '2022-12-01'
             AND o.ord_dtm <  TIMESTAMP '2023-01-01'
       ) amt_2212,
       SUM(o.ord_amt) FILTER (
           WHERE o.ord_dtm >= TIMESTAMP '2023-12-01'
             AND o.ord_dtm <  TIMESTAMP '2024-01-01'
       ) amt_2312
FROM   startdbpg.tr_ord o
WHERE  (o.ord_dtm >= TIMESTAMP '2022-12-01'
    AND o.ord_dtm <  TIMESTAMP '2023-01-01')
   OR  (o.ord_dtm >= TIMESTAMP '2023-12-01'
    AND o.ord_dtm <  TIMESTAMP '2024-01-01')
GROUP BY o.mbr_id;

SQL

복사

FILTER (WHERE ...)는 PostgreSQL 전용으로 만들어진 문법이 아니라 SQL 표준의 집계 조건 문법이지만 DBMS별 지원 차이가 있다. 따라서 범용 작성 규칙의 주 예제는 SUM(CASE WHEN ... THEN ... END)로 제시하고, PostgreSQL의 간결한 대안으로 FILTER를 소개한다. 위 보충 SQL은 GOOD 내부의 집계 부분을 대체하는 것으로, 전체 회원 조회 시에는 GOOD과 동일하게 회원 테이블에 LEFT JOIN해야 한다.

실제 데이터와 결과 동등성

•

전체 회원: 9,999명

•

2022년 12월 주문: 20,524건, 해당 월 주문 회원 9,709명

•

2023년 12월 주문: 20,359건, 해당 월 주문 회원 9,614명

•

2022년 12월 집계 NULL 회원: 290명, 2023년 12월 집계 NULL 회원: 385명

•

BAD, GOOD(CASE), FILTER 전체 조회: 각각 9,999행

•

EXCEPT ALL 양방향 비교: BADGOOD, GOODFILTER 모두 차이 0건 (FILTER는 동일 인라인 뷰와 LEFT JOIN 구조로 비교)

실제 실행계획 비교

항목	BAD: 스칼라 2개	GOOD: CASE WHEN	보충: FILTER
실행시간(각 1회)	8,702.181ms	78.400ms	75.095ms
Buffers: shared hit	1,335,948	642	642
주요 노드	SubPlan 2개 × loops=9999	BitmapOr → HashAggregate → Hash Left Join	BitmapOr → HashAggregate → Hash Left Join
결과 회원 수	9,999	9,999	9,999

BAD SQL — 실제 실행계획

Index Only Scan using ms_mbr_pk on ms_mbr m (actual time=939.274..8698.185 rows=9999 loops=1)
  Heap Fetches: 0
  Buffers: shared hit=1335948
  SubPlan 1
    ->  Aggregate (actual time=0.819..0.819 rows=1 loops=9999)
          Buffers: shared hit=667959
          ->  Index Scan using tr_ord_fk01 on tr_ord o (actual time=0.383..0.816 rows=2 loops=9999)
                Index Cond: ((mbr_id)::text = (m.mbr_id)::text)
                Filter: ((ord_dtm >= '2022-12-01 00:00:00'::timestamp without time zone) AND (ord_dtm < '2023-01-01 00:00:00'::timestamp without time zone))
                Rows Removed by Filter: 71
                Buffers: shared hit=667959
  SubPlan 2
    ->  Aggregate (actual time=0.047..0.047 rows=1 loops=9999)
          Buffers: shared hit=667959
          ->  Index Scan using tr_ord_fk01 on tr_ord o_1 (actual time=0.033..0.045 rows=2 loops=9999)
                Index Cond: ((mbr_id)::text = (m.mbr_id)::text)
                Filter: ((ord_dtm >= '2023-12-01 00:00:00'::timestamp without time zone) AND (ord_dtm < '2024-01-01 00:00:00'::timestamp without time zone))
                Rows Removed by Filter: 71
                Buffers: shared hit=667959
Planning:
  Buffers: shared hit=196
Planning Time: 7.202 ms
Execution Time: 8702.181 ms

Plain Text

복사

GOOD SQL — (CASE) 실제 실행계획

Hash Left Join (actual time=67.751..74.893 rows=9999 loops=1)
  Hash Cond: ((m.mbr_id)::text = (a.mbr_id)::text)
  Buffers: shared hit=642, temp read=76 written=146
  ->  Index Only Scan using ms_mbr_pk on ms_mbr m (actual time=0.114..2.486 rows=9999 loops=1)
        Heap Fetches: 0
        Buffers: shared hit=30
  ->  Hash (actual time=67.528..67.533 rows=9709 loops=1)
        Buckets: 16384  Batches: 1  Memory Usage: 605kB
        Buffers: shared hit=612, temp read=76 written=146
        ->  Subquery Scan on a (actual time=46.741..63.430 rows=9709 loops=1)
              Buffers: shared hit=612, temp read=76 written=146
              ->  HashAggregate (actual time=46.740..61.718 rows=9709 loops=1)
                    Group Key: o.mbr_id
                    Batches: 5  Memory Usage: 4721kB  Disk Usage: 728kB
                    Buffers: shared hit=612, temp read=76 written=146
                    ->  Bitmap Heap Scan on tr_ord o (actual time=3.730..11.788 rows=40883 loops=1)
                          Recheck Cond: (((ord_dtm >= '2022-12-01 00:00:00'::timestamp without time zone) AND (ord_dtm < '2023-01-01 00:00:00'::timestamp without time zone)) OR ((ord_dtm >= '2023-12-01 00:00:00'::timestamp without time zone) AND (ord_dtm < '2024-01-01 00:00:00'::timestamp without time zone)))
                          Heap Blocks: exact=508
                          Buffers: shared hit=612
                          ->  BitmapOr (actual time=3.642..3.644 rows=0 loops=1)
                                Buffers: shared hit=104
                                ->  Bitmap Index Scan on tr_ord_x51 (actual time=2.395..2.395 rows=20524 loops=1)
                                      Index Cond: ((ord_dtm >= '2022-12-01 00:00:00'::timestamp without time zone) AND (ord_dtm < '2023-01-01 00:00:00'::timestamp without time zone))
                                      Buffers: shared hit=52
                                ->  Bitmap Index Scan on tr_ord_x51 (actual time=1.246..1.246 rows=20359 loops=1)
                                      Index Cond: ((ord_dtm >= '2023-12-01 00:00:00'::timestamp without time zone) AND (ord_dtm < '2024-01-01 00:00:00'::timestamp without time zone))
                                      Buffers: shared hit=52
Planning:
  Buffers: shared hit=314
Planning Time: 5.598 ms
Execution Time: 78.400 ms

Plain Text

복사

FILTER 실행계획 보충: 동일한 BitmapOr → Bitmap Heap Scan → HashAggregate → Hash Left Join 형태로 실행했고, Execution Time: 75.095 ms, Buffers: shared hit=642였다. 단일 측정에서는 CASE 측 HashAggregate가 Batches: 5로 디스크 임시 블록을 사용했고, FILTER 측은 Batches: 1이었다. 이 차이나 약 3ms의 시간 차이만으로 두 문법의 성능 우위를 일반화해서는 안 된다.

핵심

•

동일 테이블의 여러 기간에 대해 반복 집계가 발생한다면, SUM(CASE WHEN ... THEN ... END)로 집계를 통합하는 방법을 고려하자. PostgreSQL에서는 FILTER로도 표현할 수 있다.

참고·예외

•

CASE와 FILTER 모두 SUM에 조건에 맞는 행이 없을 때 NULL을 반환하도록 작성했다. ELSE 0이나 COALESCE(...,0)는 결과 의미를 바꾸므로 업무 요구에 맞을 때만 사용한다. 외부 회원 수가 적고 회원별 인덱스가 효율적인 경우에는 개별 집계가 더 나을 수 있다. 실행시간은 각 1회 측정값이며 반복 측정을 통한 최종 성능 판단이 필요하다.