그룹별로 집계하고 결과 걸러내기

상품 전체의 평균 가격을 구하는 것에서 한 걸음 더 나아가, 분류별 평균을 비교해 보겠습니다. 가격이 1,000 이상인 상품만 집계하고, 그중 상품이 두 개 이상인 분류를 찾는 것이 목표입니다. 실습 환경은 SQLite입니다.

행을 묶고 그룹을 고르는 방법

GROUP BY category는 category 값이 같은 행을 하나의 그룹으로 묶습니다. 집계 함수는 각 그룹에 따로 적용되므로 COUNT(*)는 분류별 상품 수를, AVG(price)는 분류별 평균 가격을 구합니다.

조건을 적용하는 위치도 중요합니다. WHERE는 그룹을 만들기 전에 개별 행을 걸러내고, HAVING은 그룹을 만든 뒤 그룹별 집계 결과를 기준으로 걸러냅니다. 예를 들어 가격 조건은 WHERE에, 상품 수 조건은 HAVING에 넣습니다.

예제: 상품이 두 개 이상인 분류

다음 입력에는 분류와 가격으로 구성된 상품 여섯 개가 있습니다. WITH ... AS (VALUES ...)는 이 한 문장에서 사용할 이름 붙은 임시 결과를 만드는 구문입니다. VALUES 안의 각 괄호가 상품 한 행이며, products(category, price)는 두 열에 이름을 붙입니다. 별도의 테이블을 만들 필요는 없습니다.

WITH products(category, price) AS (
  VALUES
    ('book', 1000),
    ('book', 3000),
    ('book', 500),
    ('stationery', 2000),
    ('stationery', 4000),
    ('food', 5000)
)
SELECT category,
       COUNT(*) AS product_count,
       AVG(price) AS average_price
FROM products
WHERE price >= 1000
GROUP BY category
HAVING COUNT(*) >= 2
ORDER BY category;

예상 결과는 다음과 같습니다. 열 구분 방식과 숫자 표시 형식은 사용하는 SQLite 실행 도구에 따라 다를 수 있습니다.

category    product_count  average_price
book        2              2000.0
stationery  2              3000.0

먼저 WHERE price >= 1000이 가격이 500인 book 행을 제외합니다. 남은 book 두 행의 평균은 (1000 + 3000) / 2인 2000이고, stationery 두 행의 평균은 3000입니다. food는 남은 상품이 한 개이므로 HAVING COUNT(*) >= 2를 통과하지 못합니다. ORDER BY category는 결과를 분류 이름순으로 정렬합니다.

실수와 경계 조건

• WHERE COUNT(*) >= 2처럼 집계 함수로 행을 거르려고 하지 마세요. 그룹별 개수 조건은 HAVING에 작성합니다.
• WHERE에서 제외된 행은 개수와 평균 어느 쪽에도 포함되지 않습니다. 따라서 조건을 추가하거나 바꾸면 같은 분류의 집계값도 달라질 수 있습니다.
• COUNT(*)는 가격이 NULL인 행도 셉니다. 반면 AVG(price)는 NULL 가격을 제외하며, 그룹의 가격이 모두 NULL이면 평균도 NULL입니다. 분류가 NULL인 행들은 하나의 그룹으로 묶입니다.
• WHERE를 통과한 행이 없거나 모든 그룹이 HAVING 조건에 미달하면 결과 행이 없습니다. 그룹화되지 않은 열을 집계 함수 없이 SELECT에 추가하면 SQLite에서 그 값은 그룹의 어느 행에서 선택될지 예측하기 어려우므로 주의하세요.

연습과 풀이

위 예제에서 WHERE price >= 1000을 제거하고 HAVING COUNT(*) >= 3으로 바꾸면 어떤 결과가 나올까요?

book 한 그룹만 남습니다. 가격이 500인 행도 집계에 포함되어 상품 수는 3이고, 평균 가격은 (1000 + 3000 + 500) / 3인 1500입니다. stationery는 두 개, food는 한 개이므로 조건에 미달합니다.

출처

• SQLite SELECT 공식 문서: WHERE, GROUP BY, HAVING과 집계 결과 처리
• SQLite WITH 공식 문서: 문장 안에서 사용하는 이름 붙은 임시 결과
• SQLite 집계 함수 공식 문서: COUNT와 AVG의 동작