데이터 그룹핑

GROUP BY 절은 데이터를 특정 기준에 따라 여러 그룹으로 나누고, 각 그룹에 대해 집계 함수를 적용하여 요약 통계를 생성할 때 사용한다. 이는 전체 데이터에 대한 통계가 아닌, 세분화된 기준(예: 카테고리별, 고객별)으로 데이터를 분석할 때 필수적인 기능이다

GROUP BY 기본 사용법

GROUP BY 절에는 그룹화의 기준이 될 컬럼을 지정한다. 지정된 컬럼의 값이 같은 행들이 하나의 그룹으로 묶이며, 이 그룹에 대한 COUNT(), SUM(), AVG(), MAX(), MIN() 등의 집계 함수를 적용할 수 있다. NULL 값 또한 하나의 독립된 그룹으로 취급되어 집계된다

카테고리별 주문 건수

SELECT
    category,
    COUNT(*) AS `카테고리별 주문 건수`
FROM
    order_stat
GROUP BY
    category;

고객별 주문 횟수

SELECT
    customer_name,
    COUNT(*) AS `주문 횟수`
FROM
    order_stat
GROUP BY
    customer_name;

GROUP BY 와 집계 함수를 이용한 심층 분석

GROUP BY는 다양한 집계 함수와 결합하여 더욱 풍부한 분석을 가능하게 한다

고객별 구매 활동 분석 (VIP 고객 찾기)

고객별 총 주문 횟수, 총 구매량, 총 구매 금액을 한 번에 조회하여 VIP 고객을 파악할 수 있다. ORDER BY 절을 사용하여 총 구매 금액이 높은 순으로 정렬하면 VIP 고객을 쉽게 식별할 수 있다

SELECT
    customer_name,
    COUNT(*) AS `총 주문 횟수`,
    SUM(quantity) AS `총 구매 수량`,
    SUM(price * quantity) AS `총 구매 금액`
FROM
    order_stat
GROUP BY
    customer_name
ORDER BY
    `총 구매 금액` DESC; -- 백틱(`)을 사용하여 별칭으로 정렬
  • ORDER BY에서는 SELECT에서 정의한 별칭을 사용할 경우 백틱(`)으로 감싸야 합니다. 작은따옴표(‘)나 큰따옴표(“)를 쓰면 문자열 상수로 인식되어 정렬이 제대로 되지 않을 수 있습니다.

여러 컬럼을 기준으로 그룹화

GROUP BY 절에 콤마(,)로 여러 컬럼을 나열하면 다중 기준으로 데이터를 세분화하여 그룹화할 수 있다

고객별 카테고리 구매 금액 분석

  • 고객별(customer_name)로 어떤 카테고리(category)의 상품을 주로 구매했는지 확인한다
SELECT
    customer_name,
    category,
    SUM(price * quantity) AS `카테고리별 구매 금액`
FROM
    order_stat
GROUP BY
    customer_name, category
ORDER BY
    customer_name, `카테고리별 구매 금액` DESC;
  • 세종대왕이 전자기기 카테고리에서 450,000원을, 가구에서 320,000원을 구매하는 등 고객별 카테고리 지출 내역을 구체적으로 보여준다. NULL 카테고리도 하나의 그룹으로 처리된다

GROUP BY 사용 시 주의사항 (핵심 규칙)

  • GROUP BY 절을 사용할 때 SELECT 절에는 GROUP BY 절에 사용된 컬럼 또는 집계 함수만 올 수 있다
  • GROUP BY는 여러 행의 데이터를 하나의 그룹으로 요약한다. 이 그룹 내의 개별 행 데이터를 SELECT 절에서 직접 선택하려 하면, 데이터베이스는 어떤 값을 보여줘야 할지 알 수 없기 때문이다. (예시: ‘전자기기’ 카테고리 그룹에 여러 상품명이 있는데, 어떤 상품명을 보여줄 것인가?) 데이터베이스는 이러한 모호한 요청을 허용하지 않는다
SELECT
    category,
    product_name, -- GROUP BY에 없으면서 집계 함수도 아님 (오류 발생 가능)
    COUNT(*)
FROM
    order_stat
GROUP BY
    category;
오류 메시지
Error Code: 1055. Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'my_shop.order_stat.product_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
  • 이 오류는 product_name 컬럼이 GROUP BY 절에 없으면서 집계 함수로도 사용되지 않았기 때문이다

SQL 모드 (ONLY_FULL_GROUP_BY) 와의 관계

MySQL이나 MariaDB 같은 데이터베이스에서는 sql_mode 설정에 따라 위와 같은 비표준 쿼리 (GROUP BY에 없는 컬럼을 SELECT 절에서 사용하는 경우) 결과가 나오는 것을 방지하여 데이터의 일관성을 유지한다

  • ONLY_FULL_GROUP_BY 모드 활성화 시 (권장): 표준 SQL 규칙을 엄격하게 적용하기 위해 위와 같은 쿼리는 오류를 발생시킨다. 이는 비결정적인(임의적인) 결과가 나오는 것을 방지하여 데이터의 일관성을 유지한다
  • ONLY_FULL_GROUP_BY 모드 비활성화 시: MySQL은 비표준 방식으로, GROUP BY 대상 외의 컬럼 중 첫 번쨰 행의 값을 임의로 반환할 수 있다. 이는 개발자의 의도와 다른 결과를 초래할 수 있으므로 권장되지 않는다

SQL 모드 확인 방법

SELECT @@sql_mode;

ONLY_FULL_GROUP_BY 모드 활성화 방법 (기존 모드 유지하면서 추가):

-- 현재 모드를 확인한 후
-- SET sql_mode = '기존_모드1,기존_모드2,ONLY_FULL_GROUP_BY';
SET sql_mode = 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY';
  • 주의사항: ONLY_FULL_GROUP_BY를 추가하려고 SET sql_mode = ‘ONLY_FULL_GROUP_BY’;이렇게 하면 나머지 모드는 전부 삭제되고 ONLY_FULL_GROUP_BY만 남는다.

출처 – 김영한 님의 강의 김영한의 실전 데이터베이스 입문 – 모든 IT인을 위한 SQL 첫걸음(SQL부터 차근차근)