[SQL] GROUP BY

정의

GROUP BY 는 데이터를 키별로 그룹으로 나누고 각 그룹에 집계 함수 (aggregate) 를 적용해 그룹당 한 행으로 줄이는 연산. SQL 의 split-apply-combine.

Pandas groupby 와 1:1 대응. 시각화도 동일하게 사용한다.

시각화

기본 골격

SELECT 그룹키, 집계함수(컬럼)
FROM 테이블
[WHERE 조건]
GROUP BY 그룹키
[HAVING 그룹조건]
[ORDER BY ...]

실행 순서는 SQL 의 “쿼리 실행의 논리적 순서” 참고. WHERE → GROUP BY → HAVING → SELECT 임을 기억.

기본 예제

SELECT city, AVG(salary) AS avg_salary, COUNT(*) AS cnt
FROM users
GROUP BY city;

  city   | avg_salary | cnt
---------+------------+-----
Seoul   |    3833.33 |   3
Busan   |    4250.00 |   2

city 가 같은 행끼리 모이고 각 그룹에서 AVG(salary), COUNT(*) 가 계산된다.

집계 함수 (Aggregate Functions)

표준 SQL 의 핵심 집계.

NULL 의 묵시적 무시 함정

-- score 컬럼에 NULL 이 섞여 있을 때
SELECT
  COUNT(*)        AS total,      -- NULL 포함
  COUNT(score)    AS scored,     -- NULL 제외
  AVG(score)      AS avg_score,  -- NULL 제외하고 평균
  SUM(score) / COUNT(*) AS my_avg  -- NULL 도 0 으로 친 평균 (보통 의도와 다름)
FROM exams;

HAVING, 집계 조건

WHERE 는 집계 전, HAVING 은 집계 후.

-- ❌ WHERE 에 집계 함수 (에러)
SELECT city, COUNT(*) FROM users
WHERE COUNT(*) > 2
GROUP BY city;
​
-- ✓ HAVING 이 정답
SELECT city, COUNT(*) AS cnt
FROM users
GROUP BY city
HAVING COUNT(*) > 2;

-- 첫 번째: ERROR: aggregate functions are not allowed in WHERE
​
-- 두 번째:
city   | cnt
---------+-----
Seoul   |   3

-- WHERE + HAVING 같이 쓸 수도
SELECT city, AVG(salary)
FROM users
WHERE active = TRUE          -- 그룹 전, active 사용자만
GROUP BY city
HAVING AVG(salary) > 3000;    -- 그룹 후, 평균이 큰 city

여러 컬럼 그룹

SELECT city, dept, AVG(salary) AS avg_sal
FROM users
GROUP BY city, dept;

(city, dept) 의 모든 고유 조합당 한 행.

ROLLUP / CUBE / GROUPING SETS

여러 수준의 소계/총계를 한 쿼리로.

SELECT city, dept, SUM(salary)
FROM users
GROUP BY ROLLUP (city, dept);

PG/MySQL 8 모두 지원. 분석/OLAP 보고서에 빠르고 깔끔.

GROUP BY 의 SELECT 제약

표준 SQL: SELECT 절에 나오는 비집계 컬럼은 모두 GROUP BY 에 있어야 한다.

-- ❌ 표준 위반: name 이 GROUP BY 에 없음
SELECT city, name, AVG(salary)
FROM users
GROUP BY city;

dialect 차이:

• PostgreSQL: 엄격, 에러.
• MySQL: 기본은 허용 (조용히 임의의 값). sql_mode=ONLY_FULL_GROUP_BY 면 엄격. MySQL 5.7+ 부터 ONLY_FULL_GROUP_BY 가 기본.

해법: 명시적 집계 또는 GROUP BY 에 포함.

SELECT city, MAX(name), AVG(salary) FROM users GROUP BY city;     -- 명시적 집계
SELECT city, name, AVG(salary)      FROM users GROUP BY city, name;  -- 그룹키 추가

DISTINCT vs GROUP BY

같은 결과지만 의미가 다르다.

SELECT DISTINCT city, dept FROM users;
SELECT city, dept           FROM users GROUP BY city, dept;

둘 다 같은 결과셋. 옵티마이저는 보통 같은 plan 으로 처리. GROUP BY 는 집계가 따라올 때, DISTINCT 는 단순 중복 제거 의도일 때.

윈도 함수 (Window Functions)

그룹별 집계를 행 단위로 보존하며 계산. GROUP BY 가 그룹당 한 행, 윈도는 모든 행 유지.

SELECT
name,
city,
salary,
AVG(salary)  OVER (PARTITION BY city)            AS city_avg,
RANK()       OVER (PARTITION BY city ORDER BY salary DESC) AS city_rank,
ROW_NUMBER() OVER (ORDER BY salary DESC)         AS overall_rank
FROM users;

  name   | city  | salary | city_avg | city_rank | overall_rank
---------+-------+--------+----------+-----------+--------------
Alice   | Seoul |   3000 |  3833.33 |         3 |            5
Bob     | Busan |   4000 |  4250.00 |         2 |            2
Charlie | Seoul |   3500 |  3833.33 |         2 |            3

윈도 함수 종류

윈도 프레임 (Frame)

OVER (PARTITION BY ... ORDER BY ... ROWS/RANGE BETWEEN ... AND ...).

-- 7 일 이동평균
SELECT
  date,
  amount,
  AVG(amount) OVER (
    ORDER BY date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS ma7
FROM daily_sales;

-- 누적합
SELECT
  date,
  amount,
  SUM(amount) OVER (ORDER BY date) AS cumsum
FROM daily_sales;

Pandas rolling / Pandas expanding 와 같은 개념.

pandas 와 GROUP BY

기본 대응

예시 비교

SELECT
city,
COUNT(*) AS cnt,
AVG(salary)       AS avg_sal,
COUNT(DISTINCT dept) AS dept_cnt
FROM users
WHERE active = TRUE
GROUP BY city
HAVING COUNT(*) >= 2
ORDER BY avg_sal DESC;

result = (
  df[df['active']]
  .groupby('city')
  .agg(
      cnt=('city', 'size'),
      avg_sal=('salary', 'mean'),
      dept_cnt=('dept', 'nunique'),
  )
  .query('cnt >= 2')
  .sort_values('avg_sal', ascending=False)
)

-- 양쪽 모두 동일한 의미와 결과
-- city 별로 active 사용자만 집계, 2명 이상인 도시만, 평균 급여 내림차순

함정

1. WHERE 와 HAVING 헷갈림

-- WHERE 는 집계 전, HAVING 은 집계 후
WHERE  salary > 3000       -- 개별 행 필터
HAVING AVG(salary) > 3000  -- 그룹 필터

2. COUNT(*) vs COUNT(col)

-- 행 수
SELECT COUNT(*) FROM t;

-- non-NULL 행 수, 종종 의도와 다름
SELECT COUNT(active) FROM t;     -- active 가 NULL 인 행은 안 셈

3. COUNT 와 LEFT JOIN

-- 주문 없는 사용자 cnt 가 0 이 아니라 1 이 나오는 함정
SELECT u.name, COUNT(*) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.name;
-- COUNT(*) 는 LEFT JOIN 의 NULL 행도 셈 (1)

-- ✓ COUNT(o.id) 가 정답 (NULL 무시)
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.name;

4. GROUP BY 후 정렬 컬럼

-- SELECT 의 별칭을 ORDER BY 에 쓸 수 있는지는 dialect 마다 다름
SELECT city, AVG(salary) AS avg_sal
FROM users GROUP BY city
ORDER BY avg_sal DESC;     -- PG/MySQL 모두 OK

5. 큰 카디널리티 GROUP BY

수억 행 × 그룹키가 수백만 → hash table 폭발. EXPLAIN ANALYZE 로 work_mem (PG) 또는 tmp_table_size (MySQL) 확인. External Merge Sort / Grace Hash 처럼 디스크 spill 발생.

관련 위키

• SQL
• SQL DML
• SQL JOIN
• SQL Data Types
• PostgreSQL
• MySQL
• Pandas groupby
• Pandas agg
• Pandas pivot_table
• Pandas shift
• Pandas rolling
• External Merge Sort