GROUP BY vs. PARTITION BY: 유사점과 차이점

Suhyeon Lee·2024년 9월 12일

요약

GROUP BY와 PARTITION BY는 데이터를 “그룹화”한다는 점은 유사
- 유사점: GROUP BY와 PARTITION BY 모두 집계된 값을 반환할 때 사용
하지만 둘 사이에는 아주 큰 차이점이 존재함
- 차이점
1. GROUP BY를 사용하면 기존 행들이 합쳐짐 (집계된 값을 반환하면서 원래 행에 있었던 값을 함께 볼 수 없음)
2. PARTITION BY를 사용할 경우 집계된 값을 반환하면서 동시에 기존 행의 값들도 함께 볼 수 있음
3. PARTITION BY는 OVER()와 윈도우 함수와 함께 사용됨

GROUP BY

특정 기준으로 데이터를 정의하고자 할 때
- 특정 기준 == 분석을 할 때 분류 기준으로 삼는 것들
  - 임직원의 정보를 ‘연봉 인상률’을 기준으로 그룹화
  - 기차 정보를 기차의 ‘출발역’ 기준으로 그룹화
  - 매출 정보를 ‘연도’ 및 ‘월별’로 그룹화
기존 행에 있던 데이터는 우리가 제공한 새로운 기준에 의해 생성된 새로운 행에 입력됨
- 집계 함수를 사용하여 기존 행에 있던 값들을 계산한 후 새로운 행에 입력해 줄 수 도 있음
  - 집계함수: COUNT, SUM, AVG, MIN, MAX, …
    → 데이터를 하나로 합쳐줌
  - 합치는 과정에서 기존의 상세 데이터들을 잃게 됨

예시

train 테이블

id	model	max_speed	production_year	first_class_places	second_class_places
1	InterCity 100	160	2000	30	230
2	InterCity 100	160	2000	40	210
3	InterCity 125	200	2001	40	180
4	Pendolino 390	240	2012	45	150
5	Pendolino ETR310	240	2010	50	250
6	Pendolino 390	240	2010	60	250

journey 테이블

id	train_id	route_id	date
1	1	1	2016-01-03
2	1	2	2016-01-04
3	1	3	2016-01-05
4	1	4	2016-01-06
5	2	2	2016-01-03
6	2	3	2016-01-04
7	2	4	2016-01-05
8	2	5	2016-01-06
9	3	3	2016-01-03
10	3	5	2016-01-04
11	3	5	2016-01-05
12	3	6	2016-01-06
13	4	4	2016-01-04
14	4	5	2016-01-04
15	4	6	2016-01-05
16	4	7	2016-01-06
17	5	2	2016-01-03
18	5	1	2016-01-05
19	5	3	2016-01-05
20	5	1	2016-01-06
21	6	3	2016-01-03
22	6	3	2016-01-04
23	6	1	2016-01-05

route 테이블

id	name	from_city	to_city	distance
1	Manchester Express	Sheffield	Manchester	60
2	GoToLeads	Manchester	Leeds	70
3	StudentRoute	London	Oxford	90
4	MiddleEnglandWay	London	Leicester	160
5	BeatlesRoute	Liverpool	York	160
6	NewcastleDaily	York	Newcastle	135
7	ScotlandSpeed	Newcastle	Edinburgh	200

train 테이블과 journey 테이블을 활용해 기차와 해당 기차의 여정에 관한 정보 추출

기차의 고유 아이디를 기준으로 2개의 테이블 조인

SELECT
 t.id,
 t.model,
 j.*
FROM train AS t
INNER JOIN journey AS j
     ON t.id = j.train_id
ORDER BY t.id;

→ 실행 결과:

Id	model	id	train_id	route_id	date
1	InterCity 100	1	1	1	1/3/2016
1	InterCity 100	25	1	5	1/3/2016
1	InterCity 100	2	1	2	1/4/2016
1	InterCity 100	3	1	3	1/5/2016
1	InterCity 100	4	1	4	1/6/2016
2	InterCity 100	6	2	3	1/4/2016
2	InterCity 100	7	2	4	1/5/2016
2	InterCity 100	8	2	5	1/6/2016
2	InterCity 100	5	2	2	1/3/2016
3	InterCity 125	10	3	5	1/4/2016
3	InterCity 125	11	3	5	1/5/2016
3	InterCity 125	29	3	4	1/3/2016
3	InterCity 125	27	3	3	1/5/2016
3	InterCity 125	12	3	6	1/6/2016
3	InterCity 125	9	3	3	1/3/2016
4	Pendolino 390	16	4	7	1/6/2016
4	Pendolino 390	13	4	4	1/4/2016
4	Pendolino 390	14	4	5	1/4/2016
4	Pendolino 390	15	4	6	1/5/2016
4	Pendolino 390	28	4	6	1/6/2016

GROUP BY를 활용해 쿼리문 작성

SELECT
 t.id,
 t.model,
 COUNT(*) AS routes
FROM train
INNER JOIN journey AS j
     ON t.id = j.train_id
GROUP BY t.id, t.model
ORDER BY t.id

→ 실행 결과:

id	model	routes
1	InterCity 100	5
2	InterCity 100	4
3	InterCity 125	6
4	Pendolino 390	5

결과만 보면 무엇이 달라졌는지 확실히 알 수 있음
- 기차의 아이디와 모델명으로 데이터를 그룹지었음
- 이 과정에서 journey 테이블에 있던 행 단위의 세세한 경로 버림
집계 함수는 GROUP BY 없이도 사용할 수 있지만 대부분의 경우 집계 함수를 GROUP BY와 함께 사용함

💡 집계 함수 작동 원리
1. 동일한 값을 여러 개 갖고 있는 열의 이름을 GROUP BY 절에 적어줌으로써 데이터가 그룹 지어질 수 있는 기준으로 제공
2. 집계 함수가 동일한 값을 하나의 값으로 합치기 위해 그 행들의 값을 계산
3. 집계 함수를 통해 값을 합치는 과정에서 기존의 행들은 사라짐(집계 함수를 통해 구한 값들을 볼 수는 있어도 기존에 있던 정보를 함께 볼 수는 없음)

PARTITION BY

특정 기준에 한정하여 집계된 값을 계산
OVER절과 윈도우 함수와 함께 사용해 여러 행의 집계된 값을 구함
- 모든 집계 함수는 윈도우 함수로 사용할 수 있음
GROUP BY와는 달리 기존 행의 세세한 정보들은 사라지지 않고 그대로 유지
- 기존의 데이터와 집계된 값을 함께 나란히 볼 수 있다

SELECT
 t.id,
 t.model,
 r.name,
 r.from_city,
 r.to_city,
 COUNT(*) OVER(PARTITION BY t.id ORDER BY t.id) AS routes,
 COUNT(*) OVER() AS routes_total
FROM train AS t
INNER JOIN journey AS j
      ON t.id = j.train_id
INNER JOIN route AS r
      ON j.route_id = r.id;

→ 실행 결과:

Id	model	name	from_city	to_city	routes	routes_total
1	InterCity 100	Manchester Express	Sheffield	Manchester	5	30
1	InterCity 100	BeatlesRoute	Liverpool	York	5	30
1	InterCity 100	GoToLeads	Manchester	Leeds	5	30
1	InterCity 100	StudentRoute	London	Oxford	5	30
1	InterCity 100	MiddleEnglandWay	London	Leicester	5	30
2	InterCity 100	StudentRoute	London	Oxford	4	30
2	InterCity 100	MiddleEnglandWay	London	Leicester	4	30
2	InterCity 100	BeatlesRoute	Liverpool	York	4	30
2	InterCity 100	GoToLeads	Manchester	Leeds	4	30
3	InterCity 125	BeatlesRoute	Liverpool	York	6	30
3	InterCity 125	BeatlesRoute	Liverpool	York	6	30
3	InterCity 125	MiddleEnglandWay	London	Leicester	6	30
3	InterCity 125	StudentRoute	London	Oxford	6	30
3	InterCity 125	NewcastleDaily	York	Newcastle	6	30
3	InterCity 125	StudentRoute	London	Oxford	6	30
4	Pendolino 390	ScotlandSpeed	Newcastle	Edinburgh	5	30
4	Pendolino 390	MiddleEnglandWay	London	Leicester	5	30
4	Pendolino 390	BeatlesRoute	Liverpool	York	5	30
4	Pendolino 390	NewcastleDaily	York	Newcastle	5	30
4	Pendolino 390	NewcastleDaily	York	Newcastle	5	30
5	Pendolino ETR310	StudentRoute	London	Oxford	5	30

PARTITION BY 핵심 포인트

GROUP BY를 사용하지 않았지만 여전히 집계된 값을 구할 수 있음 (routes 열과 routes_total 열)
PARTITION BY를 통해 얻어낸 결과에는 기존 데이터들이 그대로 있고 중복되는 데이터를 지우지 않았음. 그리고 집계 함수를 통해 구한 값은 모든 행마다 부여되어 있음
- GROUP BY 를 사용한 쿼리문에서도 기차의 아이디와 모델명을 추출해 달라고 SELECT 문에 적었지만 GROUP BY는 기차의 아이디와 모델명을 기준으로 데이터를 합치느라 중복되는 기존 데이터는 다 지우고 기준이 될 수 있도록 한 개씩만 남겨놓았음
COUNT(*) OVER() AS routes_total은 집계되어야 할 행들끼리 구분 짓지 않았기 때문에 (PARTITION BY를 적지 않음) 모든 행이 집계 함수의 대상이 되어 30이라는 숫자가 모든 행마다 부여된 점
COUNT(*) OVER(PARTITION BY t.id) ORDER BY t.id)) AS routes 부분
- PARTITION BY를 통해 각 기차 아이디를 기준으로 행을 집계해달라고 요청해 routes 열을 보면 각 아이디마다 서로 다른 집계값을 가지고 있는 것을 확인할 수 있음

우리가 알고 있는 일반적인 집계 함수를 OVER 와 함께 윈도우 함수로 사용하면 기존 데이터를 그대로 유지한 채 새로운 집계 값을 구할 수 있음

윈도우 함수

집계 함수 이외에도, 상당히 유용한 윈도우 함수들이 존재함
윈도우 함수는 중첩해서 사용할 수 없음
- 하지만 서브쿼리는 사용 가능함

ROW_NUMBER 함수 : 각 행에 연속적인 숫자를 부여(괄호 안에 열 이름을 적을 수 없음)
RANK 함수 : ROW_NUMBER 함수와 비슷하나, 해당 함수는 괄호 안에 열 이름을 적을 수 있음
- 순위는 괄호 안에 적는 열의 값을 기준으로 부여
  - 만약 동일한 값을 가지고 있는 행이 여러 개 존재한다면, 그 행들은 모두 똑같은 순위를 부여받음
  - 그다음으로 부여되는 순위는 똑같은 순위를 부여받은 행의 개수만큼 뛰어넘음
  - 예를 들어 10위가 총 2개의 행에게 부여되었다면 그 다음 순위는 11이 아닌 12
DENSE_RANK 함수 : RANK 함수와 거의 유사하지만 이 함수는 숫자를 뛰어넘어 순위를 부여하지 않음(10위가 총 2개의 행에게 부여되었다 하더라도 그 다음 순위로 11을 부여)
NTILE 함수 : 4분위, 십분위, 백분위 등을 계산할 때 사용
LAG와 LEAD 함수 : 특정 행 이전 혹은 이후의 행을 반환

Suhyeon Lee

2 B R 0 2 B

이전 포스트

COALESCE와 IFNULL

다음 포스트

GROUP BY vs. PARTITION BY: 유사점과 차이점

요약

GROUP BY

예시

PARTITION BY

PARTITION BY 핵심 포인트

윈도우 함수

COALESCE와 IFNULL

Python 기본 문법 (1)

0개의 댓글