GROUP BY
Gravity DBA 이승연
Group by 는 특정열이나 특정 열을 연산한 결과를 집계 키로 정의 하여 그 집계 키의 Unique 값에 따라 그룹을 짓는 연산자라고 정의 할 수가 있다. 사용방법은 select 절의 가장 마지막 라인에 Group by 라는 절을 쓰고 컬럼 및 연산식을 지정하면 된다. 하나 이상이면 ,(콤마)를 이용하여 구분 할수도 있다. 앞서 이야기 했듯이 집계 키로 정의 된 특정열이나 특정 열을 연산한 결과 값이나 동일한 집계 키 즉, 동일한 값이면 오직 한번(Unique)만 나타난다.
Group by와 동일한 기능을 가진 연산자는 Distinct라는 연산자가 있다. Group by와 Distinct연산이 동일하다고 생각하는 사람이 있을 듯 하다. 하지만, 그것은 Distinct와 Group by를 잘못 이해를 하고 있는 것이다. Distinct와 Group는 다른 연산이다. Distinct는 단순히 unique값만을 추출하기 위해 사용하는 것이고, Group by는 집계 키 기준으로 집합 연산을 위해 사용하는 것이다. 집합 연산을 간략하게 짚고 넘어가면 우리가 흔히 잘 알고 잘 사용하고 있는 집합 연산자는 Count(*), SUM(), AVG(), MAX(), MIN()등을 말하는 것이다.
그럼 예제로 Distinct와 Group by를 비교하여 보자.
|
USE Northwind GO SELECT DISTINCT customerID, employeeid FROM Northwind..orders SELECT DISTINCT customerID, employeeid, count(*) FROM Northwind..orders SELECT customerID, count(*) FROM Northwind..orders GROUP BY customerID |
첫번째 T-SQL 문장에서는 customerID와 employeeid가 중복제거가 되여 출력이 된다. 즉, group by customerId, employeeid 한것과 동일한 결과값을 같는다. 여기에서 employeeid 빼고 T-SQL을 돌리면 customerID만 중복제거 되어 출력이 된다.
두번째 T-SQL 문장은 error가 출력이 된다.
메시지8120, 수준16, 상태1, 줄1
열'Northwind..orders.CustomerID'이(가) 집계함수나GROUP BY 절에없으므로SELECT 목록에서사용할수없습니다.
앞서 이야기 했듯이 Distinct와 Group by의 가장 큰 차이점은 집계 함수를 쓸수 있느냐? 없느냐? 이다. 집계 함수의 count(*)를 쓰려면 마지막 세번째 T-SQL문처럼 Group by를 써야 한다.
이번에는 Group by를 사용할 때 정렬은 어떻게 이루어지는지 알아보자. 오라클에서 Group by를 사용하면 Group by절에 명시된 컬럼의 기준으로 정렬이 이루어진다고 한다. SQL 7.0 이하 버전에서도 오라클과 같이 Group by의 명시된 컬럼의 순서대로 정렬이 되었다고 한다. 하지만 SQL 7.0이후 부터는 Group by를 할 때 비용을 따져서 정렬하여 그룹핑을 하는 경우도 있고, Hash Match를 통해서 정렬이 이루어지지 않을 수도 있다. 따라서 Group by를 할 때 반드시 Order by는 되지 않으므로 명시적으로 정렬을 원한다면 Order by를 사용하여야 하고 Order by를 사용 하므로써 추가 비용이 발생을 하게 된다.
SELECT를 할 때 특정 값을 얻기 위해서는 WHERE절을 쓸 것이다. Group by에서도 집계된 값에서 특정 값을 얻기 위해서 HAVING절이라는 것을 사용할수가있다. SELECT에서 WHERE절은 길을 찾는 Path의 역할을 수행한다고 하면, Group by에서 Having절은 전체의 큰 덩어리를 들고서 마음에 드는 것만을 골라내는 것과 같다고 볼수 있다. 따라서 WHERE절에서 우리가 고민하였던 Index니 Query성능을 좋게 하기위해서 고민했던것들을 Having절에서는 의미가 없을수도 있다. 아니 의미가 없다. Having절 사용법은 아래와 같다.
|
SELECT country, sum(money) FROM #orders GROUP BY country HAVING sum(money) > 2000 |
임시테이블 #orders 의 컬럼 country를 집계 키로 하여 Group by를 한 후 money컬럼의 합계가 2000이상인 Row만 추출하는 T-SQL문이다. 여기서 출력되는 총합을 내림차순으로 정렬을 하고 싶으면 맨 마지막절에 Order by sum(money) DESC를 하면 출력되는 값이 내림차순으로 정렬 되여 질 것이다. 참고로 Order by를 사용할 때 DESC(내림차순)또는 ASC(오름차순)을 명시적으로 지정을 하지 않았을 경우에는 ASC(오름차순)으로 정렬이 되어진다.
CUBE
Data cube라는 말은 OLTP(Online transaction processing)환경보다는 OLAP(Online analytical processing)환경에서 많이 쓰이는 말이다. OLAP에서 cube의 정의는 다음과 같다. “CUBE는 N개의 축으로 만들어지는 다면체이다.” 그럼, OLTP에서의 cube는 어떤 정의 일까? OLTP의 cube의 정의는 다음과 같이 말할수 있다. “Group by 집계 키 값에 대한 모든 가능한 조합을 row로 정리하는 연산이다.” 그리고, cube 연산을 하다 보면 NULL값을 보게 되는데, 이는 SELECT절에 포함되여진 NULL일수도 있고, cube연산중에 생긴 NULL일수도 있다.
잘 이해가 되지 않을 것 이다. 예를들어 집합 {(a,b),(a,c),(a,d),(b,d),(b,a),(c,b)}가 있다고 가정을 하고 a와 b를 집계 키로 하고 cube연산을 하면 (a,NULL) = {(a,b),(a,c),(a,d)}이고 (NULL,b) = {(a.b),(c,b)} 이다. 그럼 (NULL,NULL)은 무엇이냐? (NULL,NULL)은 전체 집합이다. 아래의 예제를 보면서 이야기를 하여 보자.
|
CREATE TABLE #나라( 나라 varchar(10) , 도시 varchar(10) , 인구수 smallint ); INSERT INTO #나라 VALUES(N'한국', N'서울', 100) INSERT INTO #나라 VALUES(N'한국', N'대전', 120) INSERT INTO #나라 VALUES(N'한국', N'대구', 130) INSERT INTO #나라 VALUES(N'미국', N'오스틴', 1000) INSERT INTO #나라 VALUES(N'미국', N'워싱턴', 1240) INSERT INTO #나라 VALUES(N'미국', N'찰스턴', 740) INSERT INTO #나라 VALUES(N'인도', N'뭄바이', 440) INSERT INTO #나라 VALUES(N'인도', N'코친', 230) INSERT INTO #나라 VALUES(N'인도', N'방갈로르', 440) select 나라,도시,sum(인구수) from #나라 group by 나라,도시 with cube |
위와 같은 예제를 실행을 시키면, 아래와 같이 출력이 될 것 이다.
나라 도시 (열 이름 없음)
------------------------------------------
한국 대구 130
NULL 대구 130
한국 대전 120
NULL 대전 120
인도 뭄바이 440
NULL 뭄바이 440
인도 방갈로르 440
NULL 방갈로르 440
한국 서울 100
NULL 서울 100
미국 오스틴 1000
NULL 오스틴 1000
미국 워싱턴 1240
NULL 워싱턴 1240
미국 찰스턴 740
NULL 찰스턴 740
인도 코친 230
NULL 코친 230
NULL NULL 4440
미국 NULL 2980
인도 NULL 1110
한국 NULL 350
이제 cube가 어떻게 동작을 하는지 조금 감이 잡혔으리라 생각한다. 그럼 이제 우리는 여기서 Grouping함수를 사용하여 앞서 말한 저 NULL값을 컨트롤을 할 수가 있다. Grouping함수를 사용하여 값이 1이면 cube나 rollup연산중에 생긴 NULL값이고, 0이면 원래NULL값인 것이다. 자 이것도 잘 이해가 되지 않을것같다. 아래 예제를 통하여 이해해 보자.
|
select (case when grouping(나라) = 1 then N'합계' else 나라 end) as '나라' , (case when grouping(도시) = 1 then N'합계' else 도시 end) as '도시' , sum(인구수) as '인구수' from #나라 group by 나라,도시 with cube |
위와 같이 case문을 사용하여 grouping한 결과가 1이면 cube나 rollup연산중에 발생한 값이니 ‘합계’를 찍어라. 라는 의미이다. 실제 결과 값을 보면 아래와 같다.
나라 도시 인구수
----------------------------------
한국 대구 130
합계 대구 130
한국 대전 120
합계 대전 120
인도 뭄바이 440
합계 뭄바이 440
인도 방갈로르 440
합계 방갈로르 440
한국 서울 100
합계 서울 100
미국 오스틴 1000
합계 오스틴 1000
미국 워싱턴 1240
합계 워싱턴 1240
미국 찰스턴 740
합계 찰스턴 740
인도 코친 230
합계 코친 230
합계 합계 4440
미국 합계 2980
인도 합계 1110
한국 합계 350
ROLLUP
Rollup을 살펴보자. Cube은 group by의 집계 키를 구성하는 모든 값의 조합이였으면, Rollup은 group by의 앞의 값에 따른 하위 값을 기준으로 값을 조합하는 것이다. 이것도 이렇게 이론적으로 이야기 하면 우리는 잘 모르겠다. 아래 예제를 보면서 이야기 해보자. 우리는 아까 cube를 하면서 생성한 #나라를 계속 사용하는 것이다. 혹시나 지웠다면 다시 생성하기 바란다.
|
select 나라,도시,sum(인구수) from #나라 group by 나라,도시 with rollup |
Cube와 사용법은 별로 다른 것이 없어 보인다. 하지만 결과 값을 보면 cube와 rollup의 차이점을 알수가 있다.
결과값은 아래와 같다.
나라 도시 (열 이름 없음)
-------------------------------------------
미국 오스틴 1000
미국 워싱턴 1240
미국 찰스턴 740
미국 NULL 2980
인도 뭄바이 440
인도 방갈로르 440
인도 코친 230
인도 NULL 1110
한국 대구 130
한국 대전 120
한국 서울 100
한국 NULL 350
NULL NULL 4440
Cube와 다르다. 무언가가 다르다. 무엇이 다른것일까? 앞에 이야기 했듯이 group by의 앞에 값에 따른 하위값을 조합한다고 하였다. 여기서 group by의 앞의 값이라는 것은 나라column이다. 나라column으로 도시를 조합을 한것이다. 실행 계획을 보아도 cube와 rollup의 차이점은 확연히 틀리다. Cube는 모든 조합에 대하여 연산을 하기 때문에 집계 키 즉, 차원이 2개이면 실행 계획은 차원은 2개로 나뉘고, 차원이 3개이면 실행 계획의 차원도 3개로 나뉜다. 이 부분은 직접 확인 해 보기 바란다.
또한, cube에서 나왔던 grouping함수를 사용하여 연산도중에 발생한 NULL값을 rollup에서도 cube와 동일하게 처리를 할 수가 있다. 다시 한번 이야기 하면 grouping함수를 사용하여 그 값이 1이면 cube나 rollup의 연산과정에서 발생한 NULL이고, 0이면 원래 NULL값인것이다. 그럼 grouping 함수를 사용하여 NULL을 ‘합계’로 바꾸어 보자.
|
select (case when grouping(나라) = 1 then N'합계' else 나라 end) as '나라' , (case when grouping(도시) = 1 then N'합계' else 도시 end) as '도시' , sum(인구수) from #나라 group by 나라,도시 with rollup |
이것도 cube와 다를것이 없다. 결과값은 아래와 같다.
나라 도시 (열 이름 없음)
--------------------------------------------
미국 오스틴 1000
미국 워싱턴 1240
미국 찰스턴 740
미국 합계 2980
인도 뭄바이 440
인도 방갈로르 440
인도 코친 230
인도 합계 1110
한국 대구 130
한국 대전 120
한국 서울 100
한국 합계 350
합계 합계 4440
마지막으로 rollup에서 보면 집계 키별로 중간합계가 나오고 전체에 대한 합계도 나오고 있다. 여기에서 중간합계를 뺀 전체 합계만을 표현하려면 어떻게 해야 할까? 결과값은 아래와 같이 추출되어야 한다.
나라 도시 (열 이름 없음)
--------------------------------------
미국 오스틴 1000
미국 워싱턴 1240
미국 찰스턴 740
인도 뭄바이 440
인도 방갈로르 440
인도 코친 230
한국 대구 130
한국 대전 120
한국 서울 100
합계 합계 4440
DBA 이승연
GRAVITY co., Ltd
Global Business Dept / Tech Support
http://www.gravity.co.kr
http://www.ddoung2.com
----------------------------------------------------------
'MSSQL > T-SQL' 카테고리의 다른 글
| union시 문자셋 에러 해결에 대하여 (3) | 2009.05.08 |
|---|---|
| UNION에 대하여 (0) | 2009.04.28 |
| SELECT절 집계 함수 (0) | 2009.04.28 |
| SQL에서의 관계대수 (0) | 2009.04.28 |
| 데이터형식 우선 순위 (0) | 2009.03.27 |