SECTION 07. GROUP BY와 집계 작성 규칙

상세 페이지

SECTION 07에서는 GROUP BY의 결과 단위를 먼저 정의한다. 이어서 불필요한 JOIN·DISTINCT를 제거하고 FILTER·UNION ALL로 중복 작업을 줄인다. 이 기본기를 익힌 후 SECTION 06에서 반복 서브쿼리를 최적화한다.

난이도: 초급 후반~중급 초반 · 설명 방식: BAD SQL → GOOD SQL → 핵심 규칙. 실행계획 전문 용어는 후반 SECTION 11에서 다룬다.

상세 소계·총계 ROLLUP은 필요할 때 원문 백업에서 참고한다. 예외와 상세 예상 계획은 SECTION 10 원문 백업에 보존돼 있다.

REMINDER 07-01. GROUP BY에 불필요한 컬럼을 추가하지 말자

원번호 09-01에서 이동. 실제 SQL 및 실행계획·검증 결과는 유지함.

상황: 2023년 한 해의 매장별 주문 건수와 총주문금액을 집계한다. 업무에서 필요한 그룹은 매장(shop_id)별이다. 실제 일반 테이블 startdbpg.tr_ord의 209,370건을 대상으로 SQL과 실행계획을 검증했다.

BAD — 조회 기간이 이미 2023년으로 고정됐는데 연도까지 GROUP BY

SELECT o.shop_id,
       COUNT(*) AS order_cnt,
       SUM(o.ord_amt) AS total_amt
FROM   startdbpg.tr_ord o
WHERE  o.ord_dtm >= TIMESTAMP '2023-01-01'
  AND  o.ord_dtm <  TIMESTAMP '2024-01-01'
GROUP BY o.shop_id, DATE_TRUNC('year', o.ord_dtm)
ORDER BY o.shop_id;

SQL

복사

WHERE에서 2023년 주문만 선택했으므로 DATE_TRUNC('year', o.ord_dtm)은 모든 대상 행에서 2023-01-01 00:00:00으로 동일하다. 따라서 연도는 그룹을 추가로 구분하지 않는다. 하지만 PostgreSQL은 이 표현식을 실제로 계산하고 집계 키로 사용했다.

GOOD — 업무에서 필요한 매장 ID만 GROUP BY

SELECT o.shop_id,
       COUNT(*) AS order_cnt,
       SUM(o.ord_amt) AS total_amt
FROM   startdbpg.tr_ord o
WHERE  o.ord_dtm >= TIMESTAMP '2023-01-01'
  AND  o.ord_dtm <  TIMESTAMP '2024-01-01'
GROUP BY o.shop_id
ORDER BY o.shop_id;

SQL

복사

매장별 집계 결과는 그대로 유지하면서 불필요한 연도 집계 키를 제거한다.

실제 데이터 및 결과 동등성

•

2023년 주문: 209,370건

•

BAD, GOOD 모두 매장별 289행 반환

•

양쪽 COUNT(*), SUM(ord_amt)를 포함한 전체 결과를 EXCEPT ALL로 양방향 비교: 차이 0건

실제 실행계획 비교 — EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)

항목	BAD: 매장 + 불필요한 연도	GOOD: 매장
집계 입력 행 수	209,370	209,370
최종 반환 그룹 수	289	289
집계 키	shop_id, date_trunc('year', ord_dtm)	shop_id
집계 노드	HashAggregate	Partial HashAggregate • Finalize GroupAggregate
집계 메모리	529kB (단일 집계 노드)	157kB (Partial HashAggregate, 각 작업자)
Shared Buffer Hit	3,089	3,475
실행시간 (워밍 후 각 1회 실측)	212.931ms	118.419ms

두 SQL의 실행계획은 동일하지 않다. GOOD에는 병렬 집계(작업자 1명 추가)가 선택됐다. 실행시간 차이는 불필요한 키 계산뿐 아니라 병렬 실행 선택에도 영향을 받으므로, 약 1.8배의 차이를 모든 환경에 일반화하지 않는다. GOOD의 메모리 수치는 각 Partial HashAggregate 노드 기준이므로 BAD와 전체 메모리를 단순 비교하지 않는다.

BAD 실제 실행계획 (핵심 발췌)

Sort (actual time=212.288..212.312 rows=289 loops=1)
  Sort Key: shop_id
  Buffers: shared hit=3089
  ->  HashAggregate (actual time=211.716..211.921 rows=289 loops=1)
        Group Key: shop_id, date_trunc('year'::text, ord_dtm)
        Batches: 1  Memory Usage: 529kB
        ->  Index Scan using tr_ord_x51 on tr_ord o
              (actual time=0.045..114.715 rows=209370 loops=1)
              Index Cond: ((ord_dtm >= '2023-01-01 00:00:00'::timestamp without time zone)
                       AND (ord_dtm < '2024-01-01 00:00:00'::timestamp without time zone))
Execution Time: 212.931 ms

Plain Text

복사

GOOD 실제 실행계획 (핵심 발췌)

Finalize GroupAggregate (actual time=110.060..118.036 rows=289 loops=1)
  Group Key: shop_id
  Buffers: shared hit=3475
  ->  Gather Merge (actual time=110.039..117.242 rows=578 loops=1)
        Workers Planned: 1
        Workers Launched: 1
        ->  Sort (actual time=104.634..104.672 rows=289 loops=2)
              Sort Key: shop_id
              ->  Partial HashAggregate (actual time=103.948..104.147 rows=289 loops=2)
                    Group Key: shop_id
                    Batches: 1  Memory Usage: 157kB
                    ->  Parallel Index Scan using tr_ord_x51 on tr_ord o
                          (actual time=0.043..37.527 rows=104685 loops=2)
Execution Time: 118.419 ms

Plain Text

복사

실행계획 해석: BAD에서는 Group Key에 업무상 필요하지 않은 date_trunc('year', ord_dtm)이 포함됐다. GOOD에서는 shop_id만으로 집계했으며, 이번 측정에서 병렬 집계까지 선택돼 실행시간이 짧았다.

핵심: GROUP BY에는 업무 결과를 구분하는 데 필요한 컬럼만 사용하자. 이미 WHERE에서 한 가지 값으로 확정된 컬럼·표현식을 불필요하게 그룹 키로 추가하면 계산과 집계 부담을 늘릴 수 있다.

주의할 예외

•

날짜 범위가 여러 연도로 확장되고 연도별 매장 매출이 필요하다면 GROUP BY shop_id, DATE_TRUNC('year', ord_dtm)은 정당한 설계다.

•

추가한 그룹 키가 실제 그룹을 세분화하면 BAD/GOOD의 결과가 달라질 수 있다. 무조건 GROUP BY 컬럼을 제거하지 말고 업무의 결과 단위부터 확인한다.

•

PostgreSQL이 불필요한 키를 최적화하는 경우도 있고, 데이터 분포·인덱스·병렬 실행 여부에 따라 성능 차이는 달라진다. 여기의 시간은 실측 1회 값이며 고정된 성능 개선 비율이 아니다.

REMINDER 07-02. DISTINCT로 중복을 제거하기 전에 불필요한 JOIN부터 확인하자

상황: 2023년 12월 주문의 주문번호, 주문일시, 매장명, 주문금액을 조회한다. tr_ord는 주문 1건당 1행이지만, tr_ord_det는 주문 하나에 상세가 여러 건인 1:N 관계다. 상세 컬럼이나 상세 조건이 필요하지 않은데 상세 테이블을 JOIN하면 행이 늘어나고, 이를 DISTINCT로 다시 줄이는 불필요한 작업이 발생한다.

실습 테이블: startdbpg.tr_ord(주문), startdbpg.ms_shop(매장), startdbpg.tr_ord_det(주문상세). 실제 일반 테이블에서 실행해 검증했다.

BAD — 불필요한 1:N JOIN으로 만든 중복을 DISTINCT로 제거

SELECT DISTINCT
       o.ord_no,
       o.ord_dtm,
       s.shop_nm,
       o.ord_amt
FROM   startdbpg.tr_ord o
JOIN   startdbpg.ms_shop s
  ON   s.shop_id = o.shop_id
LEFT JOIN startdbpg.tr_ord_det d
  ON   d.ord_no = o.ord_no
WHERE  o.ord_dtm >= TIMESTAMP '2023-12-01'
  AND  o.ord_dtm <  TIMESTAMP '2024-01-01';

SQL

복사

주문상세의 컬럼을 조회하거나 조건으로 사용하지 않는다. 그러나 상세 테이블과 JOIN하면서 주문 20,359건이 40,864행으로 증가하고, 이 중복을 DISTINCT의 HashAggregate로 다시 20,359행으로 줄인다.

GOOD — 사용하지 않는 JOIN과 DISTINCT를 함께 제거

SELECT o.ord_no,
       o.ord_dtm,
       s.shop_nm,
       o.ord_amt
FROM   startdbpg.tr_ord o
JOIN   startdbpg.ms_shop s
  ON   s.shop_id = o.shop_id
WHERE  o.ord_dtm >= TIMESTAMP '2023-12-01'
  AND  o.ord_dtm <  TIMESTAMP '2024-01-01';

SQL

복사

매장명 shop_nm을 얻기 위한 ms_shop JOIN만 유지한다. tr_ord.ord_no는 PK이고, ms_shop.shop_id도 PK이므로 이 JOIN은 주문 한 행을 여러 행으로 늘리지 않는다. 따라서 DISTINCT가 필요 없다.

실제 데이터·결과 검증

•

2023년 12월 주문: 20,359건

•

주문상세 JOIN 후: 40,864행

•

상세 2건 이상인 주문: 14,697건

•

BAD 결과 / GOOD 결과: 모두 20,359행

•

EXCEPT ALL 양방향 비교: 차이 0건

실제 실행계획 비교 (EXPLAIN (ANALYZE, BUFFERS, COSTS OFF))

비교 항목	BAD	GOOD
최종 반환 행 수	20,359	20,359
중간 JOIN 행 수	40,864	20,359
실행시간(각 1회)	490.411ms	16.710ms
Buffers: shared hit	61,291 (추가 read=96)	309
주요 연산	Nested Loop Left Join → HashAggregate	Hash Join

BAD 실제 실행계획

HashAggregate (actual time=479.942..485.734 rows=20359 loops=1)
  Group Key: o.ord_no, o.ord_dtm, s.shop_nm, o.ord_amt
  Batches: 1  Memory Usage: 2321kB
  Buffers: shared hit=61291 read=96
  ->  Nested Loop Left Join (actual time=6.443..445.767 rows=40864 loops=1)
        Buffers: shared hit=61291 read=96
        ->  Hash Join (actual time=2.397..271.250 rows=20359 loops=1)
              Hash Cond: ((o.shop_id)::text = (s.shop_id)::text)
              Buffers: shared hit=309
              ->  Index Scan using tr_ord_x51 on tr_ord o (actual time=0.797..256.183 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=305
              ->  Hash (actual time=1.568..1.569 rows=300 loops=1)
                    Buckets: 1024  Batches: 1  Memory Usage: 24kB
                    Buffers: shared hit=4
                    ->  Seq Scan on ms_shop s (actual time=0.058..0.814 rows=300 loops=1)
                          Buffers: shared hit=4
        ->  Index Only Scan using tr_ord_det_fk01 on tr_ord_det d (actual time=0.007..0.008 rows=2 loops=20359)
              Index Cond: (ord_no = o.ord_no)
              Heap Fetches: 0
              Buffers: shared hit=60982 read=96
Planning:
  Buffers: shared hit=387
Planning Time: 56.707 ms
Execution Time: 490.411 ms

Plain Text

복사

GOOD 실제 실행계획

Hash Join (actual time=0.231..15.261 rows=20359 loops=1)
  Hash Cond: ((o.shop_id)::text = (s.shop_id)::text)
  Buffers: shared hit=309
  ->  Index Scan using tr_ord_x51 on tr_ord o (actual time=0.044..5.737 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=305
  ->  Hash (actual time=0.158..0.160 rows=300 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 24kB
        Buffers: shared hit=4
        ->  Seq Scan on ms_shop s (actual time=0.017..0.073 rows=300 loops=1)
              Buffers: shared hit=4
Planning:
  Buffers: shared hit=326
Planning Time: 1.589 ms
Execution Time: 16.710 ms

Plain Text

복사

실행계획 읽기: BAD에서는 tr_ord_det_fk01에 대한 Index Only Scan이 20,359회(loops) 수행되고, 결과가 40,864행으로 증가한다. 이후 HashAggregate가 중복을 제거한다. GOOD에서는 불필요한 주문상세 탐색과 HashAggregate가 모두 사라진다.

핵심: DISTINCT를 제거할 수 있는지 판단하기 전에, 중복을 발생시키는 JOIN이 꼭 필요한지 먼저 확인하자. 조회나 필터에 사용하지 않는 1:N JOIN을 제거하면, 결과의 중복과 그 중복을 제거하는 비용을 함께 없앨 수 있다.

주의할 예외: 주문상세의 컬럼을 출력하거나 주문상세 조건으로 주문을 선택해야 하는 경우에는 해당 JOIN을 단순 삭제하면 안 된다. 상세 존재 여부만 확인하는 목적이라면 EXISTS도 비교할 수 있다(SECTION 05). 여러 결과 행 자체가 업무적으로 필요하거나 중복 제거가 요구사항이라면 DISTINCT가 필요할 수 있다. 위 성능 수치는 각각 1회 실측 결과이므로 시간 차이의 절대적 배수는 일반화하지 않는다.

REMINDER 07-03. 동일한 데이터에서 여러 값을 집계할 때는 FILTER를 활용하자

상황: 한 매장의 2023년 3월 전체 주문 수와 픽업 완료 주문 수를 함께 출력한다.

BAD — 기존 또는 비교 대상

SELECT
 (SELECT COUNT(*)
  FROM tr_ord_big AS o
  WHERE o.shop_id = 'S033'
    AND o.ord_dtm >= TIMESTAMP '2023-03-01'
    AND o.ord_dtm < TIMESTAMP '2023-04-01') AS all_cnt,
 (SELECT COUNT(*)
  FROM tr_ord_big AS o
  WHERE o.shop_id = 'S033'
    AND o.ord_dtm >= TIMESTAMP '2023-03-01'
    AND o.ord_dtm < TIMESTAMP '2023-04-01'
    AND o.ord_st = 'PKUP') AS pkup_cnt;

SQL

복사

GOOD — 권장 또는 대안

SELECT COUNT(*) AS all_cnt,
       COUNT(*) FILTER (WHERE o.ord_st = 'PKUP') AS pkup_cnt
FROM tr_ord_big AS o
WHERE o.shop_id = 'S033'
  AND o.ord_dtm >= TIMESTAMP '2023-03-01'
  AND o.ord_dtm < TIMESTAMP '2023-04-01';

SQL

복사

핵심: 집계 대상이 같고 조건만 다르면 PostgreSQL의 FILTER (WHERE ...)로 여러 집계를 한 번에 표현할 수 있다.

주의할 예외: COUNT(CASE WHEN 조건 THEN 1 ELSE 0 END)는 ELSE 0이 NULL이 아니므로 전체 행을 세는 잘못된 작성법이다. 비교할 때는 COUNT(*) FILTER (...) 또는 NULL을 반환하는 CASE를 사용한다. 두 독립 인덱스 조회가 매우 선택적이면 BAD가 더 유리할 수도 있다.

REMINDER 07-04. 중복 제거가 불필요하면 UNION ALL을 검토하자

상황: 2023년 3월의 카드(CARD)·현금(CASH) 결제 주문번호와 매장 ID를 하나로 조회한다. 실제 일반 테이블 startdbpg.tr_ord에서 검증했다. 이 테이블의 ord_no는 PK이며, pay_tp는 주문별 단일 결제유형이다.

BAD — 중복 제거가 필요하지 않은데 UNION 사용

SELECT o.ord_no, o.shop_id
FROM   startdbpg.tr_ord o
WHERE  o.ord_dtm >= TIMESTAMP '2023-03-01'
  AND  o.ord_dtm <  TIMESTAMP '2023-04-01'
  AND  o.pay_tp = 'CARD'

UNION

SELECT o.ord_no, o.shop_id
FROM   startdbpg.tr_ord o
WHERE  o.ord_dtm >= TIMESTAMP '2023-03-01'
  AND  o.ord_dtm <  TIMESTAMP '2023-04-01'
  AND  o.pay_tp = 'CASH';

SQL

복사

UNION은 두 SELECT의 결과를 합치면서 전체 출력 컬럼 기준 중복 행을 제거한다. 그러나 주문 한 건이 카드 결제이면서 동시에 현금 결제일 수 없고, 출력한 ord_no도 PK이므로 여기서는 중복 제거가 불필요하다.

GOOD 1 — 중복 제거가 필요 없다면 UNION ALL

SELECT o.ord_no, o.shop_id
FROM   startdbpg.tr_ord o
WHERE  o.ord_dtm >= TIMESTAMP '2023-03-01'
  AND  o.ord_dtm <  TIMESTAMP '2023-04-01'
  AND  o.pay_tp = 'CARD'

UNION ALL

SELECT o.ord_no, o.shop_id
FROM   startdbpg.tr_ord o
WHERE  o.ord_dtm >= TIMESTAMP '2023-03-01'
  AND  o.ord_dtm <  TIMESTAMP '2023-04-01'
  AND  o.pay_tp = 'CASH';

SQL

복사

UNION ALL은 중복 제거를 수행하지 않는다. 다만 동일 테이블을 카드·현금 조건으로 두 번 검색하는 구조는 남아 있다.

GOOD 2 — 이 사례의 최종 권장안: WHERE IN으로 통합

SELECT o.ord_no, o.shop_id
FROM   startdbpg.tr_ord o
WHERE  o.ord_dtm >= TIMESTAMP '2023-03-01'
  AND  o.ord_dtm <  TIMESTAMP '2023-04-01'
  AND  o.pay_tp IN ('CARD', 'CASH');

SQL

복사

하나의 주문 테이블에서 결제유형 조건만 다르게 조회하므로, WHERE IN으로 통합하면 두 번 검색하지 않고 한 번만 검색할 수 있다. 서로 다른 테이블 또는 독립적인 결과 집합을 결합해야 한다면 이 방식으로 대체할 수 없고 UNION ALL 등을 검토해야 한다.

실제 데이터 확인

•

2023년 3월 CARD 주문: 16,878건

•

2023년 3월 CASH 주문: 704건

•

전체 조회 대상: 17,582건

•

tr_ord.ord_no는 실제 PRIMARY KEY

•

UNION, UNION ALL, WHERE IN 모두 17,582행 반환

•

세 결과의 EXCEPT ALL 양방향 검증: 차이 0건

실제 실행계획 비교 (EXPLAIN (ANALYZE, BUFFERS, COSTS OFF))

비교 항목	BAD: UNION	GOOD 1: UNION ALL	GOOD 2: WHERE IN
반환 행 수	17,582	17,582	17,582
실행시간 (각 1회)	29.931ms	14.934ms	8.819ms
Shared Buffer Hit	524	524	262
주문 테이블 검색	2회	2회	1회
주요 실행 노드	Append → HashAggregate	Append	Index Scan

BAD: UNION 실제 실행계획

HashAggregate (actual time=24.320..28.198 rows=17582 loops=1)
  Group Key: o.ord_no, o.shop_id
  Batches: 1  Memory Usage: 1553kB
  Buffers: shared hit=524
  ->  Append (actual time=0.097..15.269 rows=17582 loops=1)
        Buffers: shared hit=524
        ->  Index Scan using tr_ord_x51 on tr_ord o (actual time=0.096..8.022 rows=16878 loops=1)
              Index Cond: ((ord_dtm >= '2023-03-01 00:00:00'::timestamp without time zone) AND (ord_dtm < '2023-04-01 00:00:00'::timestamp without time zone))
              Filter: ((pay_tp)::text = 'CARD'::text)
              Rows Removed by Filter: 704
              Buffers: shared hit=262
        ->  Index Scan using tr_ord_x51 on tr_ord o_1 (actual time=0.035..5.158 rows=704 loops=1)
              Index Cond: ((ord_dtm >= '2023-03-01 00:00:00'::timestamp without time zone) AND (ord_dtm < '2023-04-01 00:00:00'::timestamp without time zone))
              Filter: ((pay_tp)::text = 'CASH'::text)
              Rows Removed by Filter: 16878
              Buffers: shared hit=262
Planning:
  Buffers: shared hit=173
Planning Time: 0.963 ms
Execution Time: 29.931 ms

Plain Text

복사

GOOD 1: UNION ALL 실제 실행계획

Append (actual time=0.057..13.668 rows=17582 loops=1)
  Buffers: shared hit=524
  ->  Index Scan using tr_ord_x51 on tr_ord o (actual time=0.057..6.741 rows=16878 loops=1)
        Index Cond: ((ord_dtm >= '2023-03-01 00:00:00'::timestamp without time zone) AND (ord_dtm < '2023-04-01 00:00:00'::timestamp without time zone))
        Filter: ((pay_tp)::text = 'CARD'::text)
        Rows Removed by Filter: 704
        Buffers: shared hit=262
  ->  Index Scan using tr_ord_x51 on tr_ord o_1 (actual time=0.034..4.964 rows=704 loops=1)
        Index Cond: ((ord_dtm >= '2023-03-01 00:00:00'::timestamp without time zone) AND (ord_dtm < '2023-04-01 00:00:00'::timestamp without time zone))
        Filter: ((pay_tp)::text = 'CASH'::text)
        Rows Removed by Filter: 16878
        Buffers: shared hit=262
Planning:
  Buffers: shared hit=150
Planning Time: 0.817 ms
Execution Time: 14.934 ms

Plain Text

복사

GOOD 2: WHERE IN 실제 실행계획

Index Scan using tr_ord_x51 on tr_ord o (actual time=0.049..7.651 rows=17582 loops=1)
  Index Cond: ((ord_dtm >= '2023-03-01 00:00:00'::timestamp without time zone) AND (ord_dtm < '2023-04-01 00:00:00'::timestamp without time zone))
  Filter: ((pay_tp)::text = ANY ('{CARD,CASH}'::text[]))
  Buffers: shared hit=262
Planning:
  Buffers: shared hit=202
Planning Time: 0.930 ms
Execution Time: 8.819 ms

Plain Text

복사

실행계획 읽기: UNION은 Append로 두 집합을 결합한 다음 HashAggregate로 중복을 제거한다. UNION ALL은 중복 제거용 HashAggregate가 없지만, 두 브랜치에서 tr_ord_x51을 각각 사용하여 해당 월의 주문을 반복 검색한다. WHERE IN은 한 번의 Index Scan으로 조회한다. 이 실습에서는 실행시간뿐 아니라 버퍼 접근량도 524 → 262로 줄었다.

핵심: 중복 제거가 필요 없는 집합 결합은 UNION ALL을 우선 검토하자. 더 나아가 동일 테이블의 OR 조건으로 표현할 수 있다면 WHERE IN 등으로 한 번의 조회로 통합하는 편이 단순할 수 있다.

예외·주의:

•

서로 다른 브랜치에 같은 출력 행이 생길 수 있으면 UNION과 UNION ALL의 결과는 달라진다. UNION은 출력된 컬럼의 조합으로 중복을 제거하며, 원본의 PK가 출력되지 않으면 서로 다른 원본 행도 중복으로 판단될 수 있다.

•

중복을 보존해야 하는 업무에서는 UNION ALL을 사용해야 한다.

•

서로 다른 테이블의 결과를 결합하는 경우 WHERE IN은 일반적인 대체안이 아니다.

•

이번 수치는 각각 1회 측정값이다. UNION ALL 또는 WHERE IN이 항상 같은 비율로 빠르다는 뜻은 아니므로 실제 실행계획을 비교해야 한다.

SQL 작성 후 체크리스트

GROUP BY와 출력 컬럼의 단위가 맞는가?

JOIN 때문에 집계 대상 행이 증폭되지 않았는가?

COUNT·SUM의 NULL·중복 처리 정책이 정확한가?

중복 제거가 불필요한 UNION은 UNION ALL로 바꿀 수 있는가?

한 줄 요약: 집계는 SQL이 빠른지보다 어떤 단위로 정확하게 세고 합산하는지가 먼저다. 한 번 읽어 함께 집계할 수 있는 데이터는 되풀이해서 읽지 말자.