GROUP BY vs. PARTITION BY: 유사점과 차이점
윈도우 함수는 SQL의 유용한 기능 중 하나입니다. 윈도우 함수를 잘 사용할 줄 안다면 여러분의 인생은 보다 편안해질 것입니다. 이 글에서는 GROUP BY와 PARTITION BY의 차이점을 살펴보고자 합니다.
PARTITION BY 대 GROUP BY
PARTITION BY와 GROUP BY절은 복잡한 분석을 할 때 꽤나 자주 쓰게 될 SQL 문법입니다. 물론 데이터를 단순히 반환하는 것만으로도 유용할 때가 있겠지만, 우리가 마주하는 상황은 종종 더 복잡한 계산을 하는 상황이기 때문입니다. 한 단계 발전된 분석을 위해 우리는 GROUP BY와 PARTITION BY를 사용합니다. 이 둘은 데이터를 "그룹화"한다는 점에서 유사하지만, 둘 사이에는 아주 큰 차이점이 존재합니다. 그 차이점을 지금부터 살펴보도록 하겠습니다.
GROUP BY
GROUP BY 절은 특정 기준으로 데이터를 정의하고자 할 때 사용합니다. 보통 이런 기준은 우리가 분석을 할 때 분류 기준으로 삼는 것들입니다. 예를 들자면, 임직원의 정보를 그들의 '연봉 인상률'을 기준으로 그룹화할 수 있고, 기차 정보를 기차의 '출발역' 기준으로 그룹화할 수 있을 것이며, 매출 정보를 '연도' 및 '월별'로 그룹화하여 살펴볼 수 있습니다.
GROUP BY 절을 사용하면 기존 행에 있던 데이터는 우리가 제공한 새로운 기준에 의해 생성된 새로운 행에 입력됩니다. 그뿐만이 아닙니다. 집계 함수를 사용하여 기존 행에 있던 값들을 계산한 후 새로운 행에 입력해 줄 수도 있습니다. 집계 함수는 데이터를 하나로 합쳐줍니다. 합치는 과정에서, 우리는 기존의 상세 데이터들을 잃게 됩니다. 집계 함수의 종류는 다양하지만, 그중 가장 많이 사용되는 함수는 COUNT, SUM, AVG, MIN, MAX 함수가 있습니다.
지금까지의 이야기가 무엇을 이야기하는지 예시를 통해 살펴보도록 하겠습니다. 우리는 총 3개의 테이블을 가지고 이야기할 것입니다. 기차의 정보가 저장된 train 테이블이 있고, 기차의 여정이 저장된 journey 테이블이 있고, 해당 여정들이 어떤 경로를 통해 가는지에 관한 정보가 저장된 route 테이블이 있습니다.
첫 번째, 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 |
결과를 보면, 아이디가 1인 기차의 행이 총 5개가 존재합니다. 아이디가 2인 기차는 행이 총 4개가 있네요.
그럼, 이번에는 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 |
결과만 보면 무엇이 달라졌는지 확연히 알아챌 수 있습니다. 바로 기차의 아이디와 모델명으로 데이터를 그룹 지었습니다. COUNT 함수를 사용해 각 기차마다 몇 개의 경로를 갖고 있는지 알아냈습니다. 이 과정에서, 우리는 journey 테이블에 있던 행 단위의 세세한 정보는 버렸습니다. 이중 집계 함수를 사용해 여러 행의 값을 합친 route 열의 값들을 바로 직전 테이블과 비교해보면, 각 기차와 모델명마다 경로의 개수가 일치하는 것을 확인할 수 있습니다.
물론 집계 함수는 GROUP BY 없이도 사용할 수 있습니다. 하지만 대부분의 경우 집계 함수는 GROUP BY와 함께 사용합니다. 집계 함수가 작동하는 원리는 다음과 같습니다.
첫째, 동일한 값을 여러 개 갖고 있는 열의 이름을 GROUP BY 절에 적어줌으로써 데이터가 그룹 지어질 수 있는 기준으로 제공합니다. 둘째, 집계 함수가 동일한 값을 하나의 값으로 합치기 위해 그 행들의 값을 계산합니다. 셋째, 집계 함수를 통해 값을 합치는 과정에서 기존의 행들은 사라지게 됩니다. 집계 함수를 통해 구한 값들을 볼 수는 있어도 기존에 있던 정보를 함께 볼 수는 없습니다.
기존 행에 있던 세세한 정보를 더 이상 보지 못하는 것은 대부분의 경우는 괜찮습니다. 우리는 집계 함수를 통해 보다 유의미한 분석 포인트를 발견하고 싶었던 것이니까요. 하지만, 때론, 집계 함수로 새로 구한 값과 원래 기존의 세세한 행들을 같이 보면서 분석을 해야 할 때가 생깁니다. 이는 서브 쿼리를 활용해 해결할 수 있지만, 서브 쿼리로 해결하다 보면 쿼리문도 길어질 수도 있습니다. 무엇보다도, 이런 문제를 해결해 줄 수 있는 다른 SQL 문법이 있는데 배워봐야죠.
PARTITION BY
여러분이 어떤 분석을 하냐에 따라 다르겠지만, PARTITION BY를 통해 특정 기준에 한정하여 집계된 값을 계산해 줄 수 있습니다. 여러 행의 집계된 값을 구하고자 PARTITION BY는 OVER절과 윈도우 함수와 함께 사용됩니다. 이는 GROUP BY와 집계 함수가 하는 역할과 거의 유사하지만, 차이점이 1가지 존재합니다. 여러분이 PARTITION BY를 사용하면, GROUP BY와는 달리 기존 행의 세세한 정보들은 사라지지 않고 그대로 유지됩니다. 즉, 기존의 데이터와 집계된 값을 함께 나란히 볼 수 있다는 이야기입니다. GROUP BY는 이전의 기존 데이터가 하나로 합쳐지는 바람에 같이 볼 수 없었던 점, 기억하시죠? 이것이 GROUP BY와 PARTITION BY의 차이점입니다. 모든 집계 함수는 윈도우 함수로 사용할 수 있습니다.
아래 쿼리를 살펴보도록 할게요. 이번에는 3개의 테이블을 전부 다 사용하려고 합니다.
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 |
해당 결과를 통해 이번 글에서 알아보려 했던 몇 가지 중요한 점을 짚고 넘어가겠습니다.
첫째, GROUP BY를 사용하지 않았지만 여전히 집계된 값을 구할 수 있었습니다 (routes 열과 routes_total 열을 보세요).
둘째, GROUP BY 를 사용한 쿼리문에서도 우리는 기차의 아이디와 모델명을 추출해 달라고 SELECT 문에 적었습니다. 하지만 GROUP BY는 기차의 아이디와 모델명을 기준으로 데이터를 합치느라 중복되는 기존 데이터는 다 지우고 기준이 될 수 있도록 한 개씩만 남겨주었습니다. 하지만 이번 PARTITION 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 와 함께 윈도우 함수로 사용하면 기존 데이터를 그대로 유지한 채 새로운 집계 값을 구할 수 있습니다. 만약 지금까지의 내용이 어려우셨다면, 집계 함수와 윈도우 함수: 유사점과 차이점을 읽어보시길 추천드립니다.
윈도우 함수
집계 함수 이외에도, 상당히 유용한 윈도우 함수들이 존재합니다. 대표적인 윈도우 함수에 관해 간략히 설명하도록 하겠습니다. 윈도우 함수에 관해 더 자세히 알고 싶으신 분들은 대표적인 윈도우 함수 6가지 알아보기를 읽어보시길 추천드립니다.
ROW_NUMBER 함수 : 각 행에 연속적인 숫자를 부여합니다. 괄호 안에 열 이름을 적을 수 없습니다.
RANK 함수 : ROW_NUMBER 함수와는 비슷하나, 해당 함수는 괄호 안에 열 이름을 적을 수 있습니다. 순위는 괄호 안에 적는 열의 값을 기준으로 부여됩니다. 만약 동일한 값을 가지고 있는 행이 여러 개 존재한다면, 그 행들은 모두 똑같은 순위를 부여받습니다. 그다음으로 부여되는 순위는 똑같은 순위를 부여받은 행의 개수만큼 뛰어넘습니다. 예를 들어, 10위가 총 2개의 행에게 부여되었다면, 그다음은 순위는 11이 아닌 12 입니다.
DENSE_RANK 함수 : RANK 함수와 거의 유사합니다. 다만, 이 함수는 숫자를 뛰어넘어 순위를 부여하지 않습니다. 예를 들어, 10위가 총 2개의 행에게 부여되었다 하더라도, 그다음 순위로는 11이 부여됩니다.
NTILE 함수 : 4분위, 십분위, 백분위 등을 계산할 때 사용합니다.
LAG와 LEAD 함수 : 특정 행 이전 혹은 이후의 행을 반환해 줍니다.
무조건 윈도우 함수를 써야 하는 경우가 정해진 것은 아니지만, 알고 있으면 상당히 유용한 개념이라 살펴보았습니다.
PARTITION BY와 GROUP BY: 유사점과 차이점
비로 우리는 GROUP BY를 더 자주 사용하지만, PARTITION BY가 더 유용할 때도 있습니다. 어떤 경우는 GROUP BY와 서브 쿼리를 함께 사용하여 PARTITION BY를 대체할 수는 있으나, 그렇게 되면 꽤나 복잡한 쿼리문을 작성하게 될 수도 있습니다. 그럼 PARTITION BY와 GROUP BY의 가장 중요한 유사점과 차이점을 살펴보면서 이 글을 마치도록 하죠.
1. 유사점: PARTITION BY와 GROUP BY 모두 집계된 값을 반환할 때 사용합니다.
2. 차이점
(1) GROUP BY를 사용하면 기존 행들이 합쳐집니다. 집계된 값을 반환하면서 원래 행에 있었던 값을 함께 볼 수 없습니다.
(2) 반면, PARTITION BY를 사용할 경우 집계된 값을 반환하면서 동시에 기존 행의 값들도 함께 볼 수 있습니다.
(3) 또한, PARTITION BY는 OVER()와 윈도우 함수와 함께 사용됩니다.
원문: What Is the Difference Between a GROUP BY and a PARTITION BY?