안녕하세요. SQLER의 코난 김대우입니다.
이번 강좌에서는, 2-8. GROUP BY, ROLLUP, CUBE를 진행 하겠습니다.
SQLER에서 진행되는, 챗GPT와 함께 배우는 SQL Server 강좌 목록
이번에 소개해 드릴 내용은 데이터를 그룹으로 묶어 요약 데이터를 작성하는 GROUP BY 강좌입니다.
TL;DR
GROUP BY 구문으로 데이터에서 그룹별 요약 정보를 생성 가능하며, 집계 함수와 함께 사용합니다. HAVING으로 요약 정보에 제약을 설정할 수 있습니다.
이전 강좌에서 배운 집계함수(Aggregate function)는 테이블 전체에 대해 집계된 요약 정보를 제공했습니다. 그렇다면, 테이블 내 데이터 그룹에 대해 집계된 요약 정보는 어떻게 출력할까요?
GROUP BY 구문 예제
-- 세일즈 테이블 생성 (테이블 생성 강좌에서 상세히 배웁니다. 지금은 실행만 하세요.) -- 만약 테이블이 존재하면 삭제 IF OBJECT_ID(N'dbo.sales', N'U') IS NOT NULL DROP TABLE dbo.sales; GO CREATE TABLE sales ( Country NVARCHAR(10), Region NVARCHAR(10), Sales INT ); GO --예제 데이터 삽입 INSERT INTO sales VALUES (N'한국', N'서울', 100); INSERT INTO sales VALUES (N'한국', N'서울', 200); INSERT INTO sales VALUES (N'한국', N'부산', 300); INSERT INTO sales VALUES (N'미국', N'뉴욕', 100); INSERT INTO sales VALUES (N'미국', N'LA', 50); GO --테스트 조회 SELECT * FROM sales; GO
이런 매출 데이터를 국가별로 정리해 보면, 국가별로 판매된 판매량을 알 수 있지 않을까요? 어떻게 그룹으로 만들 수 있을까요?
--국가별 매출 조회 SELECT Country, SUM(Sales) as SalesTotal FROM sales GROUP BY Country ORDER BY Country ASC; GO 결과 Country SalesTotal ---------- ----------- 미국 150 한국 600
국가별, 지역별로 그룹 된 판매량을 볼 수는 없을까요?
--국가별, 지역별 매출 조회 SELECT Country, Region, SUM(Sales) as SalesTotal FROM sales GROUP BY Country, Region ORDER BY Country ASC, Region ASC; GO 결과 Country Region SalesTotal ---------- ---------- ----------- 미국 LA 50 미국 뉴욕 100 한국 부산 300 한국 서울 300
가끔, GROUP BY 된 집계에 추가적으로 전체 총합 정보가 필요한 경우가 있습니다.(바로 이어지는 ROLLUP과 유사합니다.)
--GROUPING으로 총합 로우를 추가 SELECT Country, SUM(Sales) as SalesTotal FROM sales GROUP BY GROUPING SETS (Country, ()) ORDER BY Country ASC; GO 결과 Country SalesTotal ---------- ----------- NULL 750 <--총합 미국 150 한국 600
그렇다면 집계된 결과를 필터링할 수는 없을까요? 매출이 100 미만인 지역만 보고 싶다면 어떻게 하면 될까요?
--국가별, 지역별 매출 조회에서 합계 매출이 100 미만인 지역만 조회 SELECT Country, Region, SUM(Sales) as SalesTotal FROM sales GROUP BY Country, Region HAVING SUM(Sales) < 100 ORDER BY Country ASC, Region ASC; GO 결과 Country Region SalesTotal ---------- ---------- ----------- 미국 LA 50
WHERE절이 테이블의 로우를 필터링한다면, HAVING은 GROUP BY로 생성된 요약데이터에 대해 필터링을 합니다.
이런 그룹화된 요약 정보는 현업에서 아주 많이 쓰입니다. 데이터를 데이터베이스에 적재하는 이유가 이런 요약 정보를 얻어 비즈니스 인사이트를 얻는 것입니다. 요약 정보에 사용되는 GROUP BY는 매우 자주 사용되니 꼭 잘 공부해 두세요.
GROUP BY 구문정보
GROUP BY에 대해 조금 더 살펴봅니다.
SELECT select_list FROM table_name WHERE search_conditions GROUP BY { column-expression | ROLLUP ( <group_by_expression> [ ,...n ] ) | CUBE ( <group_by_expression> [ ,...n ] ) | GROUPING SETS ( <grouping_set> [ ,...n ] ) | () --calculates the grand total } [ ,...n ] |
집계 함수와 함께 각 그룹에 대해 하나의 행과 하나의 요약 정보를 생성한다.
GROUP BY
- 선택된 테이블의 각 그룹에 대한 요약 정보를 생성한다.
HAVING
- 결과 행에 대해 제약을 가한다.
- 조건에 맞지 않는 그룹을 제외한다.
ANSI 표준 SQL에서의 요구사항
- select_list에 있는 모든 컬럼들은 반드시 GROUP BY 절에 나타나야 한다.
- HAVING 절에 있는 컬럼은 반드시 하나의 값을 반환해야 한다.
- HAVING 절을 포함한 질의는 반드시 GROUP BY 절을 포함해야 한다.
T-SQL에서는 ANSI 표준 SQL보다 많은 옵션을 제공한다.
- GROUP BY 절은 수식을 포함할 수 있다.
- GROUP BY ALL은 WHERE절에서 제외된 그룹까지도 포함한다.
- ALL은 SELECT 문이 WHERE 절을 포함할 때만 의미가 있다.
AdventureWorks 데이터베이스를 사용하는 몇 개의 샘플을 더 살펴보겠습니다.
--AdventureWorks DB에서 Sales.SalesOrderDetail의 요약 정보 출력 SELECT SalesOrderID, SUM(LineTotal) AS SubTotal FROM Sales.SalesOrderDetail AS sod GROUP BY SalesOrderID ORDER BY SalesOrderID; GO
--DATEPART 함수를 이용해 연도별 판매량 집계 SELECT DATEPART(yyyy, OrderDate) AS N'Year' , SUM(TotalDue) AS N'Total Order Amount' FROM Sales.SalesOrderHeader GROUP BY DATEPART(yyyy, OrderDate) ORDER BY DATEPART(yyyy, OrderDate); GO
--HAVING 절을 이용해 연도가 2013년 이후인 판매량 집계 결과만 출력 SELECT DATEPART(yyyy, OrderDate) AS N'Year' , SUM(TotalDue) AS N'Total Order Amount' FROM Sales.SalesOrderHeader GROUP BY DATEPART(yyyy, OrderDate) HAVING DATEPART(yyyy, OrderDate) >= N'2013' ORDER BY DATEPART(yyyy, OrderDate); GO
ROLLUP과 CUBE
GROUP BY를 이용해 깔끔하게 집계된 요약 정보를 조회할 수 있습니다. 여기에 추가적인 정보를 출력해 그룹화된 소계와 합계 정보를 같이 볼 수 있을까요? 이럴 때 사용하는 것이 ROLLUP과 CUBE입니다.
--국가별 매출 조회. 소계와 합계 추가 SELECT Country, SUM(Sales) as SalesTotal FROM sales GROUP BY Country WITH ROLLUP; GO 결과 Country SalesTotal ---------- ----------- 미국 150 한국 600 NULL 750 <-- 합계
맨 마지막 줄을 보시면 이전 GROUP BY에 없던 NULL과 함께 합계 값이 추가됩니다.
그렇다면 국가와 지역별 ROLL UP은 어떨까요?
--국가별, 지역별 매출 조회. 소계와 합계 추가 SELECT Country, Region, SUM(Sales) as SalesTotal FROM sales GROUP BY Country, Region WITH ROLLUP ORDER BY Country ASC, Region ASC; GO 결과 Country Region SalesTotal ---------- ---------- ----------- NULL NULL 750 <--전체 국가 및 지역 합계 미국 NULL 150 <-- 미국 전체 지역 소계 미국 LA 50 미국 뉴욕 100 한국 NULL 600 <-- 한국 전체 지역 소계 한국 부산 300 한국 서울 300
그렇다면 CUBE는 무엇일까요?
CUBE는 ROLLUP에 추가적으로 가능한 모든 경우의 소계와 합계(집계)를 출력합니다.
--국가별, 지역별 매출 조회. 소계와 합계 모든 조합 추가 SELECT Country, Region, SUM(Sales) as SalesTotal FROM sales GROUP BY Country, Region WITH CUBE ORDER BY Country ASC, Region ASC; GO 결과 Country Region SalesTotal ---------- ---------- ----------- NULL NULL 750 <-- 모든 국가 합계 NULL LA 50 <--국가와 무관한 LA 지역 소계 NULL 뉴욕 100 <--국가와 무관한 뉴욕 지역 소계 NULL 부산 300 <--국가와 무관한 부산 지역 소계 NULL 서울 300 <--국가와 무관한 서울 지역 소계 미국 NULL 150 <--미국 국가 소계 미국 LA 50 미국 뉴욕 100 한국 NULL 600 <--한국 국가 소계 한국 부산 300 한국 서울 300
위의 데이터는 1차 그룹인 국가가 2차 그룹인 지역을 포괄해 지역별 정보가 단순하지만, 2차 그룹으로 의미 있는 정보를 출력하고 싶을 때 의미 있게 사용될 수 있습니다.
ROLLUP / CUBE 연산자
지정된 컬럼들에 대한 다양한 조합으로 추가 요약 데이터를 제공합니다. 집계함수와 함께 사용되어 결과에 추가적인 행을 생성합니다.
GROUP BY [ALL] aggregate_free_expression [, aggregate_free_expression…] [WITH{CUBE | ROLLUP}] |
ROLLUP 연산자
- GROUP BY 절에 있는 컬럼들의 오른쪽에서 왼쪽의 차례로 그룹들을 생성하고 각 그룹에 집계 함수를 적용한다.
- GROUP BY 절의 결과는 누적 집계 결과이다.
CUBE 연산자
- GROUP BY 절에 있는 모든 컬럼들에 대한 가능한 모든 조합을 그룹으로 생성한다.
ROLLUP이나 CUBE는 생각보다 사용할 일이 적습니다. 하지만, GROUP BY는 매우 빈번하게 사용됩니다. 위의 GROUP BY 예제를 차근차근 수행하시고 다른 테이블에서도 유용한 정보를 찾아 GROUP BY를 다양하게 시도해 보세요.
SQL 강좌 책 구매
강좌가 도움이 되셨다면, 책으로 구매 가능합니다. 책 판매 수익금은 전액 코딩 교육 사회공헌 활동에 기부되며, 아래 링크에서 구매하시면 더 많은 금액이 기부됩니다.