이번 장에서는 데이터의 집계에 대해서 알아보도록 하겠습니다. 집계의 사전적 의미를 보면 "모아 셈함", "한데 모은 계산" 이라는 뜻인데, 이를 통해 이번 장에서 무엇을 할지 감을 잡을 수 있을 것입니다. 바로 데이터를 한데 모아서 어떤 연산을 하는 방법을 설명하는데, 가령 회원 정보를 담고 있는 테이블에서 회원 들의 나이의 평균을 구하고 싶을때 AVG 라는 집계 함수를 이용할 수 있는 것입니다
집계 함수를 통해 우리는 데이터의 전체 평균이나 합계같은 연산을 할 수 있고, 남여별 평균 같은 데이터의 그룹핑(Grouping)도 가능합니다. 앞으로의 강좌를 통해 알아볼 집계 함수는 다음과 같습니다.
| AVG | MIN |
| CHECKSUM | SUM |
| CHECKSUM_AGG | STDEV |
| COUNT | STDEVP |
| COUNT_BIG | VAR |
| GROUPING | VARP |
| MAX |
테이블에 데이터는 얼마나 있을까? - COUNT, COUNT_BIG
아주 간단한 질문 입니다. 테이블에 데이터가 몇건이나 들어 있을까? 충분히 가져볼만한 질문입니다. 특히, 웹에서 쓰이는 데이터베이스의 경우 회원 정보 테이블은 수시로 체크가 될것입니다. 왜냐면, 몇명의 회원이 가입을 했는지 알아보기 위해서는 회원 정보 테이블에 몇건의 데이터가 있는지 셈하면 될테니깐요. COUNT 함수는 구문 설명보다는 바로 예제로 이해하는 것이 빠릅니다.
GO
-- 테이블에 존재하는 모든 행(Row)의 수를 구한다
SELECT COUNT(*) FROM Person.Contact;
-- 결과(19972 개의 행이 있음을 알 수 있다) ----------- 19972 (1개 행 적용됨)
SELECT COUNT(FirstName) AS CntOfFirst FROM Person.Contact;
-- 칼럼을 주었을 경우, 해당 칼럼의 값이 NULL인 경우 카운트하지 않는다
SELECT COUNT(MiddleName) AS CntOfMiddle FROM Person.Contact;
-- 결과(MiddleName의 경우 NULL이 상당 수 있어, 결과가 다르다) CntOfFirst ----------- 19972 (1개 행 적용됨) CntOfMiddle ----------- 11473 경고: 집계 또는 다른 SET 연산에 의해 Null 값이 제거되었습니다. (1개 행 적용됨)
SELECT COUNT(FirstName) AS CntOfAll FROM Person.Contact;
SELECT COUNT(DISTINCT FirstName) AS CntOfDistinct FROM Person.Contact;
-- 결과(MiddleName의 경우 NULL 값이 상당 수 있어, 결과가 다르다) CntOfFirst ----------- 19972 (1개 행 적용됨) CntOfDistinct ----------- 1018 (1개 행 적용됨)
COUNT 함수와 똑같은 COUNT_BIG 함수라는 것이 있습니다. COUNT 함수의 경우 셀 수 있는 행의 수가 231-1 입니다. 바로, SQL Server의 INT 형식에 해당하는 값입니다. 이 범위를 넘어가는 행의 수를 카운트 하려고 하면 에러를 유발하게 됩니다. 이럴때는 COUNT_BIG함수를 이용할 수 있습니다. COUNT_BIG 함수는 BIGINT 범위의 행을 수를 카운트 할 수 있습니다.
최대/최소 값을 구해보자 - MAX, MIN
MAX, MIN 함수는 상식적으로 받아들일 수 있는 함수라고 생각합니다. 각각 최대 값과 최소 값을 구하는 합니다. 회원 정보 테이블을 가지고 예를 들면, 나이가 가장 작은 사람의 나이와, 가장 많은 사람의 나이를 구할 수 있을 것입니다. MAX, MIN 함수에서도 DISTINCT 키워드를 이용해 고유 값을 지정할 수 있으나 전혀 의미가 없습니다(2,3,3 에서 최대값은 3이다. 중복값을 제거하여 고유 값을 만들어 2,3 에서 최대값을 찾아도 3이니 말이다). 그래도 DISTINCT 키워드를 사용할 수 있는데, SQL-92 호환성을 위해서만 존재합니다. 그럼 MAX, MIN 함수의 예제를 살펴 보겠습니다.
GO
-- 최저 세율을 구한다
SELECT MIN(TaxRate) AS Minval FROM Sales.SalesTaxRate;
-- 최고 세율을 구한다
SELECT MAX(TaxRate) AS Maxval FROM Sales.SalesTaxRate;
-- 결과(19972 개의 행이 있음을 알 수 있다) Minval --------------------- 5.00 (1개 행 적용됨) Maxval --------------------- 19.60 (1개 행 적용됨)
SELECT MIN(FirstName)AS Minval, MAX(FirstName)AS Maxval
FROM Person.Contact;
-- 결과 Minval Maxval ---------------------------------------------- ---------------------------------------------- A. Zoe (1개 행 적용됨)
MIN/MAX 함수의 집계에서는 NULL 값이 제외됩니다. 따라서 NULL 값은 아무런 영향을 미치지 않습니다. 그리고 문사열에 적용한 MIN/MAX 함수는 문자열을 A~Z 순으로 정렬하였을 때, 가장 앞에 나타나는 값이 MIN 함수의 결과로, 가장 뒤에 나타나는 값이 MAX 함수의 결과값으로 나타나게 됩니다. 길이가 가장 짧고 길다는 것을 뜻하는 것이 아니니 주의바랍니다(참고 : 길이가 가장 긴 문자열은 MAX(LEN(칼럼명)) 을 이용하면 찾아낼 수 있습니다)
합계와 평균을 구해보자 - SUM, AVG
SUM, AVG 함수 또한 이해하기 어려운 것이 아닙니다. 각각 합계와 평균을 구하는 함수임을 이름을 통해 다 알고 있으리라 믿습니다. SUM이나 AVG 함수에 칼럼명을 지정하면 해당 칼럼의 합계나 평균을 구해주는데, NULL 값은 무시됩니다. SUM 함수의 경우 NULL 값을 무시하든 안 하든 결과에 전혀 영향을 미치지 못하나, AVG 함수는 NULL 값을 무시하지 않을 경우 결과가 달라질 수 있습니다. 가령, (1, NULL, 2)에서 NULL 값을 무시했을 때의 평균은 1.5 이지만 - (1+2)/2 = 1.5 - NULL 값을 0으로 간주했을 경우 결과는 1이 됩니다 - (1+0+2) / 3 = 1
NULL 값이란 Unknown 값으로 알 수 없는 값을 이야기 합니다. 비어있다는 Empty나 0 과는 다른 값을 말 하는 것입니다. 그래서 NULL 값에 어떤 수를 더하든 빼든 그 결과는 NULL이 되고 마는 것입니다. 알 수 없는 값에 어떤 수를 더한든 그 결과는 마찬가지로 알 수 없으니깐요. 쉽게 이야기하면 테이블 칼럼에 사용자가 어떤 값을 넣지 않아 아직 어떤 값인지 모른다는 이야기로 요약할 수 있습니다. 값이 없다면, 명시적으로 Empty나 0 과 같은 값을 넣어줘야 하구요. 하지만 대부분의 경우 값이 존재하지 않을 경우 NULL 인 상태로 그대로 두는 것이 관례입니다.
SUM, AVG 함수 또한 예제를 한번 보시면 금방 이해할 수 있을 것입니다. 백문이 불여일타! 꼭 한번 입력하고 실행해 보도록 합시다!!
SELECT SUM(TaxRate) AS Total, AVG(TaxRate) AS Average FROM Sales.SalesTaxRate;
-- 결과 Total Average --------------------- --------------------- 263.65 9.0913 (1개 행 적용됨)
-- TaxType이 3인 데이터의 세율의 합계와 평균을 보자
SELECT SUM(TaxRate) AS Total, AVG(TaxRate) AS Average
FROM Sales.SalesTaxRate
WHERE TaxType = 3
-- 결과 Total Average --------------------- --------------------- 126.10 9.70 (1개 행 적용됨)
표준 편차를 구해보자 - STDEV, STDEVP
표준 편차는 다들 고등학교 수학시간에 한번쯤 들어보셨을 것입니다. 아니면 잘 알고 업무에 잘 활용하고 계실꺼라 믿습니다. 표준편차(Standard Deviation)가 클수록 관측값 중에는 평균에서 떨어진 값이 많이 존재하여 편차가 크다고 말할 수 있습니다. 보통 통계정보를 제공할 때 합계와 평균, 그리고 표준 편차 정도를 제공해 주어야할 것입니다.
SQL Server에서 표준편차를 구하는 집계 함수는 2개가 제공되고 있습니다. 각각 STDEV, STDEVP 인데, 여기서 SQL Server 2005 BOL의 해석상의 문제를 조심스럽게 지적하고자 합니다. 영문 버전의 내용을 확인하지 못해 확실하지 않지만, SQL Server 2005 BOL의 설명을 빌리자면, STDEV는 지정한 식의 모든 값에 대한 통계적 표준 편차를 반환하고, STDEVP는 지정한 식에 있는 모든 값의 모집단에 대한 통계적 표준 편차를 반환한다고 설명하고 있습니다. 딴지를 걸고자 하는 것은, 식의 모든 값이 모집단이 아니냐는 것입니다. 모집단 자체가 집계를 하기 위한 전체 데이터를 뜻하는데, 그렇다면 STDEV와 STDEVP의 차이는 없어야 하지요. 모집단은 영어로 Population이라 앞의 글자 'P'를 따서 모집단의 표준편차를 나타내는 함수는 STDEVP가 맞습니다. 확인결과 STDEV는 표본의 표준편차로 추측됩니다. 제가 Excel 2003(11.8012)을 이용해 SQL Server와 같은 데이터를 입력하여 표준 편차를 구해본 결과, STDEV는 표본의 표준편차를 구하고 있었습니다. 이렇게 한번 딴지 걸면,, 저 또한 엄청난 딴지를 당하지 않을까 두렵습니다..
이제 STDEV, STDEVP 함수의 차이를 알았으니, 또 예제를 통해서 살펴보도록 하겠습니다. 어려운 것은 없고, 단지 STDEV 혹은 STDEVP 함수 다음에 칼럼명을 명시해 주시만 하면 됩니다. 다른 집계 함수와 마찬가지로 NULL 값을 무시합니다. 다음의 쿼리를 참고해 주십시오.
GO
-- 모집단과 표본에서 모든 보너스 값의 표준편차를 구해보자
SELECT STDEV(Bonus) AS '표본', STDEVP(Bonus) AS '모집단' FROM Sales.SalesPerson;
-- DISTINCT 키워드를 이용해 중복값을 제거할 수도 있다.
SELECT STDEV(DISTINCT Bonus) AS '중복제거표본', STDEVP(DISTINCT Bonus) AS '중복제거모집단'
FROM Sales.SalesPerson;
-- 결과 표본 모집단 ---------------------- ---------------------- 2273.31454210651 2205.43905349595 (1개 행 적용됨) 중복제거표본 중복제거모집단 ---------------------- ---------------------- 2163.29876310729 2084.60684885321 (1개 행 적용됨)
분산을 구해보자 - VAR, VARP
분산은 표준편차와 마찬가지로 표본과 모집단에 대한 분산을 구할 수 있습니다. 별로 긴 설명이 필요없을 것 같아, 표준편차와 마찬가지로 보너스 값에 대한 분산을 구해보도록 하겠습니다.
GO
-- 모집단과 표본에서 모든 보너스 값의 분상을 구해보자
SELECT VAR(Bonus) AS '표본', VARP(Bonus) AS '모집단' FROM Sales.SalesPerson;
-- DISTINCT 키워드를 이용해 중복값을 제거할 수도 있다.
SELECT VAR(DISTINCT Bonus) AS '중복제거표본', VARP(DISTINCT Bonus) AS '중복제거모집단'
FROM Sales.SalesPerson;
-- 결과 표본 모집단 ---------------------- ---------------------- 5167959.00735294 4863961.41868512 (1개 행 적용됨) 중복제거표본 중복제거모집단 ---------------------- ---------------------- 4679861.53846154 4345585.71428571 (1개 행 적용됨)
체크섬을 구해보자 - CHECKSUM, CHECKSUM_AGG
CHECKSUM 함수를 설명한 책이 잘 없습니다. 잘 쓰이질 않으니 그런 것이라 생각하고 있습니다. CHECKSUM 함수를 이용하면, 어떤 문자열을 정수의 값으로 표현할 수가 있습니다. 이를 이용하면 인덱스를 구축할 때 이득을 볼 수 있는데, 아직 인덱스를 다루지 않은 관계로 자세히 이야기할 수가 없네요. 이것또한 긴 설명보단 예제가 가장 이해하기 쉬울 것입니다. 아니면 제가 예전에 썼던 "CHECKSUM 함수의 활용한 인덱스 구축 - http://www.sqlmania.net/lecture/view.asp?lectid=5&page=1"을 참고해 주십시오.
SELECT
CHECKSUM('English') AS r1,
CHECKSUM('english') AS r2,
CHECKSUM('한글') AS r2,
CHECKSUM('漢字') AS r3,
CHECKSUM(REPLICATE('A', 7999)) AS r4,
CHECKSUM(REPLICATE('A', 8000)) AS r5,
CHECKSUM(1234) AS r6,
CHECKSUM(2147483648) AS r7
-- 결과 r1 r2 r2 r3 r4 r5 r6 r7 ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- 1650434961 1650434961 118519096 2116711736 335548418 0 1234 -519048026 (1개 행 적용됨)
[리스트 6]를 보시면 다양한 문자열이 정수 형태로 바뀜을 알 수 있습니다. 이거 어디다 쓰냐... 물어 보실 수 있습니다. 당연히 그런 질문 가지실 것입니다. 위에서는 문자열의 길이가 짧아서 별로 티가 안 나는데, 길이가 아주 긴 문자열 같은 경우에도 CHECKSUM 함수를 통하면 다른 문자열과 구별되는 유일한 정수를 반환해 줍니다. 문자열이 100글자 이면 100 바이트를 차지하겠지만, CHECKSUM 함수의 결과는 항상 4 바이트 입니다. 이것으로 인덱스를 구축한다면, 비교도 안될 정도로 작은 크기로 인덱스를 구성할 수 있기 때문에 제가 관심을 가졌던 것입니다. 하지만 제약이 너무 많아 실제 쓰기엔 많은 한계가 있습니다. 그리고 SQL Server 2000에서 사용 가능했던 BINARY_CHECKSUM 함수 또한 SQL Server 2005에서 그대로 사용 가능합니다.
CHECKSUM_AGG 함수 또한 칼럼 이름이나 상수를 입력 받아 정수 형식으로 값을 변환해 주는 점은 CHECKSUM 함수와 같지만, CHECKSUM는 한 행의 결과를 따로따로 보여주는데 반해, CHECKSUM_AGG 함수는 해당 칼럼 전체의 값을 하나로 보아 처리한 후, 하나의 결과로 보여주게 됩니다. CHECKSUM_AGG 함수를 이용하면, 테이블에서 한 컬럼의 값이 변경 되었는지 알 수 있습니다. 값이 변경되면 CHECKSUM_AGG 함수의 결과가 달라지기 때문에, 값의 변화를 보고 이를 인지할 수 있는 것입니다. 예제를 살펴보겠습니다.
-- CHECKSUM_AGG 함수가 INT 형식의 값만을 받아들여, Bonus 칼럼의 값을 INT 형식으로 바꾸기 위한 CAST 함수를 사용하였다.
SELECT CHECKSUM_AGG(CAST(Bonus AS INT)) AS BEFORE
FROM Sales.SalesPerson;
-- SalesPersonID가 268번인 사람의 보너스 값을 100으로 바꾸자.
UPDATE Sales.SalesPerson SET Bonus = 100.0 WHERE SalesPersonID = 268;
-- 보너스 값을 바꾼후의 CHECKSUM_AGG 값
SELECT CHECKSUM_AGG(CAST(Bonus AS INT)) AS AFTER
FROM Sales.SalesPerson;
-- 결과(값을 바꾸기 전과 바꾸고 난 후의 결과값이 다르다) BEFORE ----------- 2840 AFTER ----------- 2940
[리스트 7]의 결과를 살펴보면 테이블에서 특정 칼럼의 값을 바꿨을 경우, CHECKSUM_AGG 함수의 결과도 바뀜을 알 수 있습니다. 이를 이용하면 어떤 칼럼의 값이 바꿨는지, 안 바꿨는지 알아내는데 유용하게 이용할 수 있습니다. 다만, INT 형식의 데이터 형식만 지원하는 것이 조금 아쉽게 느껴집니다.
데이터를 그룹화(Grouping) 해보자 - GROUP BY
이번 강좌에서 가장 중요한 그룹화에 관한 내용입니다. 회원 정보중에서 "남여별 평균 연령을 구하라" 라는 문제가 있다고 생각해 봅시다. 평균 나이는 앞에서 배웠던 AVG 함수를 이용하면 될 것 같은데, 남여를 따로 구하라니... SELECT 문장의 WHERE 절을 이용해 남자의 평균 연령을 한번 구하고 여자의 평균 연령을 구하면 되기는 합니다. 하지만 남여로 그룹화 하는것 처럼 2개의 그룹이 아닌, 테이블에 존재하는 모든 동일 데이터를 찾아 모두 그룹화하라고 하면 WHERE 절만을 이용해서 해결이 불가능 합니다. 이럴때 쓰라고 있는 것이 GROUP BY 구문입니다. GROUP BY 구문을 이용해 데이터를 그룹화할 수 있습니다.
GROUP BY 구문이 들어가면서부터 SELECT 문장의 순서에 혼돈을 가져올 수 있습니다. 너도 처음엔 SELECT 문장에서 GROUP BY 구문 어디 들어가야 하는지 한참 생각하곤 했답니다. GROUP BY 구문으로 인한 SELECT 문장의 변화를 살펴보겠습니다.
FROM 테이블 이름
WHERE 조건
GROUP BY 컬럼1, .., 컬럼n
HAVING 조건
위에서 부터 하나씩 살펴 보겠습니다. 먼저 SELECT 문장 다음에 열람을 원하는 컬럼을 표시하는 곳에 집계함수가 들어가 있는 것을 알 수 있습니다. 회원정보에서 남여를 따로 묶어 놨으니, 그룹화된 데이터를 가지고 어떻게 처리해를 해야되지 않겠습니까? 단순히 묶는 것으로는 아무런 의미를 부여할 수 없기 때문에 더하든, 빼든 그룹화된 데이터를 집계하는 과정이 꼭 필요합니다.
그 다음 볼 수 있는 것이 GROUP BY 구문 입니다. 여기서 그룹화 하기를 원하는 컬럼을 적어주는 것입니다. 회원정보 테이블이라면 이곳에 성별을 나타내는 칼럼 이름을 적어주면 되겠죠? 단, GROUP BY 절에 나타난 컬럼은 모드 SELECT 절 다음에 나타날 필요가 없지만 SELECT 절 다음에 나타난 칼럼들은 꼭 GROUP BY 절에 포함이 되어 있어야 합니다.
마지막으로는 보이는 것이 HAVING 절 입니다. WHERE 절에서 기술한 조건과 무엇이 다른지 이해를 해야 합니다. WHERE 절에서는 원본 테이블에서 데이터를 가져오기 전에 어떤 데이터만 가져오라고 말해주는 곳입니다. 그래서 WHERE 절에서 명시된 조건을 만족하는 모든 데이터를 불러와서 GROUP BY 절에 명시된 칼럼을 기준으로 그룹화를 하게 됩니다. 그룹화를 하는 과정에서 HAVING 절에서 명시된 조건이 있으면, 그 조건을 만족하는 결과만 우리에게 보이는 것입니다. 가령, HAVING COUNT(*) > 1 이라고 적어주면 그룹화된 데이터가 1건를 초과할 경우만 보이라는 이야기가 됩니다.
| 아이디(UserID) | 이름(UserName) | 성별(Gender) | 나이(Age) | 시/도(Province) |
| sqlmania | 권태돈 | 남 | 27 | 경북 |
| cik0225 | 조일국 | 남 | 26 | 서울 |
| ryung | 이미령 | 여 | 25 | 서울 |
| dalgi | 김수진 | 여 | 26 | 서울 |
[표 1]과 같은 회원정보 테이블에 데이터가 들어 있다고 가정해 보겠습니다. 테이블 이름은 UserInfo 입니다. 이제 위에서 계속 예로든 회원의 남여별 평균 연령을 구해보는 쿼리를 작성해 보도록 하겠습니다.
SELECT Gender, AVG(Age) AS AverageAge
FROM UserInfo
GROUP BY Gender
-- 결과
Gender AverageAge ------ ----------- 남 26 여 25 (2개 행 적용됨)
결과를 해석해 보도록 하겠습니다. GROUP BY 절에 의해 Gender 칼럼이 그룹화 하도록 선언이 되었습니다. 테이블에는 성별을 나타내는 값이 '남' / '여' 둘 뿐이라 그룹은 많아야 2개가 될것으로 예상이 가능합니다. 먼저 '남'으로 묶어 보겠습니다. [표 1]에서 녹색 부분이 묶이게 될 것 입니다. 데이터를 묶고 나서 어떤 집계를 원하는지 살펴보니 AVG(Age)를 수행하라고 합니다. 성별이 '남'인 행들의 나이의 평균은 (27 + 26) / 2 = 26 입니다. 26.5 아니냐고요? 이게 컴퓨터의 한계입니다. "정수 + 정수 / 정수" 의 결과는 정수로 나오게 되어 있습니다. 정수로 표기하기 위해선 반올림이고 뭐고 간에 상관하지 않고 그냥 소숫점은 버려버립니다. 그래서 26이 나온 것입니다. 성별이 '여' 데이터도 마찬가지로 계산을 하면 25라는 값을 반환함을 알 수 있습니다.
이번에는 그룹화의 기준으로 성별과 시/도를 지정해 보도록 하겠습니다. 마찬가지로 GROUP BY 절 다음에 성별과 시/도를 나타내는 칼럼명이 나올 것으로 예상할 수 있습니다.
SELECT Gender, Province, AVG(Age) AS AverageAge, COUNT(*) AS Cnt
FROM UserInfo
GROUP BY Gender, Province
-- SELECT 절에서 Province 칼럼을 제거해 보자(동작한다)
SELECT Gender, AVG(Age) AS AverageAge, COUNT(*) AS Cnt
FROM UserInfo
GROUP BY Gender, Province
-- GROUP BY 절에서 Province 칼럼을 제거해 보자(동작 안한다)
SELECT Gender, Province, AVG(Age) AS AverageAge, COUNT(*) AS Cnt
FROM UserInfo
GROUP BY Gender
-- 결과1 Gender Province AverageAge Cnt ------ ---------- ----------- ----------- 남 경북 27 1 남 서울 26 1 여 서울 25 2 (3개 행 적용됨) -- 결과2 Gender AverageAge Cnt ------ ----------- ----------- 남 27 1 남 26 1 여 25 2 (3개 행 적용됨) -- 결과3 메시지 8120, 수준 16, 상태 1, 줄 1 열 'UserInfo.Province'이(가) 집계 함수나 GROUP BY 절에 없으므로 SELECT 목록에서 사용할 수 없습니다.
[리스트10]에서 결과1을 해석해 보겠습니다. 남여/시도별로 그룹화를 하였기 때문에, 성별이 '여' 인 데이터의 경우 성별이 '여' 인 데이터로 묶이고 그 다음 이들의 시/도 정보를 보니 같기 때문에(여자들은 모두 서울에 산다) 전체가 하나로 묶이게 됩니다. 하지만 성별이 '남'인 데이터의 경우 시/도 정보가 다르기 때문에 묶이질 못하고 [리스트10]의 결과1 처럼 결과가 따로 나오는 것입니다. COUNT(*) 함수로 그룹화된 데이터의 개수를 보면 성별이 '남'인 데이터의 경우 1임을 알 수 있습니다.
다음으로 HAVING절을 이용해 그룹화된 결과가 하나 이상의 행을 그룹화한 것만 출력하도록 쿼리를 작성해 보겠습니다.
SELECT Gender, AVG(Age) AS AverageAge, COUNT(*) AS Cnt
FROM UserInfo
GROUP BY Gender, Province
HAVING COUNT(*) > 1
-- 결과 Gender AverageAge Cnt ------ ----------- ----------- 여 25 2 (1개 행 적용됨)
text, ntext 및 image 형식의 칼럼은 GROUP BY 절에 명시되어 그룹화의 기준 칼럼으로 사용될 수 없으며, xml 형식 또한 직접적인 사용이 불가능합니다.
마지막으로 CUBE와 ROLLUP 구문을 통해 추가적인 통계 정보를 얻는 방법을 알아보겠습니다. CUBE와 ROLLUP 구문을 통해 여러 수준의 요약 정보를 더 추가시킬 수 있는데, 설명보단 예제를 통해 이해하는 것이 빠를 것 같습니다. [리스트 10]에 사용한 쿼리에 CUBE와 ROLLUP 구문을 추가해 보도록 하겠습니다.
SELECT Gender, Province, AVG(Age) AS AverageAge, COUNT(*) AS Cnt
FROM UserInfo
GROUP BY Gender, Province WITH ROLLUP
-- WITH CUBE
SELECT Gender, Province, AVG(Age) AS AverageAge, COUNT(*) AS Cnt
FROM UserInfo
GROUP BY Gender, Province WITH CUBE
-- WITH ROLLUP 결과 Gender Province AverageAge Cnt ------ ---------- ----------- ----------- 남 경북 27 1 남 서울 26 1 남 NULL 26 2 여 서울 25 2 여 NULL 25 2 NULL NULL 26 4 (6개 행 적용됨) -- WITH CUBE 결과 Gender Province AverageAge Cnt ------ ---------- ----------- ----------- 남 경북 27 1 남 서울 26 1 남 NULL 26 2 여 서울 25 2 여 NULL 25 2 NULL NULL 26 4 NULL 경북 27 1 NULL 서울 25 3 (8개 행 적용됨)
ROLLUP의 경우 계층적인 합계를 함께 표시해 줍니다. [리스트 12]을 보면 시/도 정보는 무관한 성별별 집계자료와, 성별과 시/도 정보 모두 무관한(NULL 값으로 표시) 집계 자료를 함께 볼 수 있습니다. 즉, 남여별 평균 나이와 성별과는 무관한 전체의 평균 나이를 함께 표시하고 있는 것입니다.
CUBE의 경우 ROLLUP의 결과에 성별은 무관한 가운데 시/도별 평균 연령까지 더 보여주고 있습니다. 이처럼 CUBE는 조합 가능한 모든 경우의 수를 다 보여주기 때문에, 많은 정보를 얻을 수 있어 다양한 관점에서 데이터를 관찰할 수 있으나 어떤 정보를 보여주질 예측하기가 어려운 단점이 있습니다.
일반 SELECT결과에 집계함수의 결과만 더하자 - COMPUTE, COMPUTE BY
COMPUTE 구문을 이용하면 일반 SELECT결과와 함께 집계함수의 결과를 따로 표시할 수 있습니다. 프로그램을 해보시면 알겠지만, 집계정보를 다른 레코드셋에서 불러올 수 있어매우 편리한것을 알 수 있습니다. COMPUTE 구문과 함께 쓰일 수 있는 집계 함수로는 AVG, COUNT, MAX, MIN, STDEV, STDEVP, VAR, VARP, SUM 이 있습니다. 더 이상 긴 말이 필요없이 바로 결과를 보여 드리겠습니다. 데이터는 [표-1]의 것을 그대로 이용하겠습니다.
SELECT UserID, UserName, Gender, Age, Province
FROM UserInfo
COMPUTE SUM(Age), AVG(Age)
-- 결과(하단에 보면 집계 정보가 따로 표시된다) UserID UserName Gender Age Province ------------ ---------- ------ ---- ---------- cik0225 조일국 남 26 서울 dalgi 김수진 여 26 서울 ryung 이미령 여 25 서울 sqlmania 권태돈 남 27 경북 sum avg ----------- ----------- 104 26 (5개 행 적용됨)
COMPUTE BY 구문을 이용하면 COMPUTE 구문와 GROUP BY 구문을 썩어놓은 듯한 결과를 볼 수 있습니다. COMPUTE BY 구문 다음에 나타난 칼럼을 기준으로 데이터를 그룹화하여 그룹에 속한 상세 데이터와 그 집계 정보를 함께 보여주게 됩니다. 한가지 제약조건이 있다면, COMPUTE BY 구문에 나타난 컬럼은 꼭 ORDER BY 구문을 이용해 정렬을 해주어야 한다는 것입니다. 그럼, 예제를 살펴보겠습니다.
SELECT UserID, UserName, Gender, Age, Province
FROM UserInfo
ORDER BY Gender
COMPUTE SUM(Age), AVG(Age) BY Gender
-- 결과(하단에 보면 집계 정보가 따로 표시된다) UserID UserName Gender Age Province ------------ ---------- ------ ---- ---------- cik0225 조일국 남 26 서울 sqlmania 권태돈 남 27 경북 -- 성별이 '남' 인 데이터의 집계정보 sum avg ----------- ----------- 53 26 UserID UserName Gender Age Province ------------ ---------- ------ ---- ---------- dalgi 김수진 여 26 서울 ryung 이미령 여 25 서울 -- 성별이 '여' 인 데이터의 집계정보 sum avg ----------- ----------- 51 25 (6개 행 적용됨)
마치며...
이상으로 SQL Server 2005에서 제공하는 모든 집계 함수를 알아보았습니다. 그룹화 함수를 제외하면 크게 어려운점은 없으리라 생각합니다. GROUP BY 구문의 경우 실무에서도 매우 빈번히 사용됨으로 확실한 이해가 필요한 부분입니다. CUBE, ROLLUP, COMPUTE 과 같은 부가적인 통계정보를 보여주는 구문들은 개발단계에서 사용하시는 어플리케이션에서 충분히 대체가 가능한 것이지만, 뭐든 잘 쓰면 뽀대가 나는 법입니다. SQL Server 여러 사람이서 매우 효율적으로 돌아가도록 만들어 졌기때문에, 저 같은 경우 제가 직접 처리하는 것보다 SQL Server를 믿는 경향이 있습니다. 저보다 똑똑한 사람이 만들었거라는 막연한 기대이죠.
이번 강좌는 쓰다가 사람을 지치게 만드는 군요. 다 쓰고 쭉 스크롤을 해보니 색깔도 알록다록하고 이쁘네요. 다음 강좌는 UNION, EXCEPT, INTERSECT에 관한 것입니다. 중/고등학교 수학 시간에 배운 합집합, 교집합, 차집합 따위를 SQL Server 2005에 구현하는 방법을 배우는 것입니다. 중/고등학교때의 악몽을 기억한다면 별로 즐거운 시간은 될것 같지는 않지만, 그래도 늘 기대하는 마음으로 기다려주시면 큰 힘이 될 것 같습니다. 또 뵙겠습니다.^^
댓글 없음:
댓글 쓰기