[MSSQL] 집계 함수와 SELECT ... GROUP BY, 그리고 HAVING

집계 함수와 SELECT ... GROUP BY, 그리고 HAVING

 

1. 집계 함수와 GROUP BY

SQL 집계 함수(aggregate function)는 하나의 칼럼의 여러 값을 읽어 하나의 값을 반환한다.

자주 사용되는 집계 함수로는 다음과 같은 것들이 있다.

• COUNT : 행의 개수를 센다.
• AVG : 행들의 값들을 평균낸다.
• SUM : 행들의 값을 모두 더한다.
• MAX : 최대값을 구한다.
• MIN : 최소값을 구한다.
• STDEV : 표준편차를 구한다.

집계 함수는 주로 GROUP BY 절과 함께 사용된다.

GROUP BY 절은 말 그대로 특정 칼럼을 기준으로 그룹으로 묶어 주는 역할을 한다.

2. SUM과 GROUPBY를 통한 기초 실습

아래와 같은 구매 내역 테이블이 있다.

위 테이블에서 ID별로 구매한 물품의 개수를 보려면 아래와 같이 작성할 수 있다.

1. SELECT ID, Amount FROM BuyTable
2. ORDER BY ID

위와 같이 작성하고 수행해보면, 물건을 여러번 구매한 사용자의 경우 각각의 행이 별도로 출력되기에,

다시 결과를 가지고 계산기를 두드려가며 한 사용자가 총 몇 개를 구매했는지 계산해야 한다.

이럴 때 아래와 같이 집계 함수 SUM과 GROUP BY를 적절히 사용하면, 이쁘게 결과를 얻어낼 수 있다.

1. SELECT ID, SUM(Amount) FROM BuyTable
2. GROUP BY ID

위 결과는 아래와 같다.

엇...원하는 대로 Amount가 정확히 집계되었지만, 열 이름이 없다.

열 이름을 임의로 지정하고 싶다면, 다음과 같이 AS 구문을 사용한다.

1. SELECT ID, SUM(Amount) AS AmountSum FROM BuyTable
2. GROUP BY ID

그러면 다음과 같이 SUM(Amount)에 대해 AmountSum이라는 열 이름이 붙는다.

위 과정을 조금 더 설명하면,

ID 별로 그룹핑하여 이 그룹에 속한 행들의 값을 모두 더하여 결과로 표현한 것이다.

응용을 위해 아래와 같이 구매 총액을 구해 보자.

1. SELECT ID, SUM(Amount * Price) AS TotalCost FROM BuyTable
2. GROUP BY ID

구매액은 수량 * 가격이고, 총 구매액이므로 SUM 함수를 사용하였다.

지금까지 GROUP BY 절과 SUM 함수에 대해 알아보았다.

이제 다른 집계 함수도 사용해 보면서 GROUP BY에 대해 조금 더 자세히 알아보자.

3. AVG와 GROUP BY

전체 구매자가 구매한 물품 개수의 평균을 구해보자.

AVG 함수가 평균을 구하는 함수라고 했으니 바로 사용해 보자.

SELECT AVG(Amount) AS AmountAvg FROM BuyTable

음... 실제 평균은 2.72 정도가 나와야 하는데, 결과가 달랑 2 로만 나온다.

이것은 Amount 칼럼의 타입이 정수형이기 때문이다.

칼럼의 타입 형식을 맞추기 위해서 결과값 역시 정수형으로 나온 것이다.

타입 형식을 변경하기 위해 다음과 같은 방법들을 사용할 수 있다.

• 명시적 형 변환 : CAST() / CONVERT()
• 암시적 형 변환 : ex) 곱하기 1.0

CAST / CONVERT 함수는 추후 자세히 살펴보기로 하고, 우선 간단하게 1.0을 곱해보자.

SELECT AVG(Amount * 1.0) AS AmountAvg FROM BuyTable

제대로 AmountAvg가 2.727272로 표시되는 것을 확인할 수 있다.

이제, 전체 사용자가 아닌 개별 사용자별로 평균 몇개씩 구매했는지를 살펴보자.

이를 위해 AVG와 GROUP BY를 아래와 같이 사용한다.

1. SELECT ID, AVG(Amount * 1.0) AS AmountAvg FROM BuyTable
2. GROUP BY ID

4. COUNT / COUNT_BIG

COUNT() 함수는 () 안의 조건에 맞는 행의 개수를 INT 형식으로 반환한다.

테이블의 전체 행 개수를 얻어오고 싶을 땐 아래와 같이 사용한다.

1. -- BuyTable의 전체 행 개수를 반환한다
2. SELECT COUNT(*) FROM BuyTable

이번에는 전체 구매자가 구매한 GoodName이 몇가지 종류나 되는지 살펴보자.

1. -- DISTINCT 구문을 이용해 GoodName의 중복을 없앴고
2. -- 이 중복이 없어진 것을 카운트하면 GoodName의 총 가지수가 나온다
3. SELECT COUNT(DISTINCT GoodName) FROM BuyTable

COUNT()가 INT 형식을 반환하기에 만약 2^31 - 1을 넘는 수를 다루어야 한다면,

BIGINT 형식을 반환하는 COUNT_BIG() 함수를 사용하기 바란다.

5. 집계 함수와 성능

대략 2만건의 데이터가 들어 있는 테이블 BigTable에서

1. SELECT * FROM BuyTable
2. GO
3. SELECT COUNT(*) FROM BuyTable
4. GO

SELECT COUNT(*)가 SELECT * 에 비해 8배 이상 빠르다.

140건의 행이 존재하는 테이블의 특정 칼럼 값을 

• 집계 함수 SUM을 썼을 때
• 값을 하나씩 읽어 직접 더했을 때

SUM 함수가 17배 정도 빠르다.

이와 같이 집계 함수는 데이터를 한 건씩 처리하는 것에 비해 월등히 좋은 성능을 보인다.

6. HAVING 절

앞의 총구매액을 구하던 예제를 다시 살펴보자.

1. SELECT ID, SUM(Amount * Price) AS TotalCost FROM BuyTable
2. GROUP BY ID

이 중에서 총 구매액이 1,000 이상인 사용자만 출력하고 싶을 때,

얼핏 생각하면 아래와 같이 WHERE 구문을 생각하기 싶다.

1. SELECT ID, SUM(Amount * Price) AS TotalCost FROM BuyTable
2. WHERE SUM(Amount * Price) >= 1000
3. GROUP BY ID

하지만, 실행 결과는 아래와 같다.

메시지 147, 수준 15, 상태 1, 줄 4

집계가 HAVING 절이나 SELECT 목록에 포함된 하위 쿼리 내에 없으면 WHERE 절에 나타날 수 없습니다. 

또한 집계 중인 열은 외부 참조입니다.

오류 메시지를 보면, 집계함수는 WHERE 절에 나타날 수 없다는 이야기이다.

이럴 때 사용하는 것이 HAVING 절이다.

HAVING은 WHERE와 비슷한 개념으로 조건을 제한하되 집계함수에 대해서 제한하는 것이라고 생각하면 된다.

또한, HAVING 절은 반드시 GROUP BY 절 다음에 나와야 한다.

이제 HAVING 절을 이용하여, 총 구매액이 1,000이상이고 구매액이 높은 순서로 정렬까지 해보자.

1. SELECT ID, SUM(Amount * Price) AS TotalCost FROM BuyTable
2. GROUP BY ID
3. HAVING SUM(Amount * Price) >= 1000
4. ORDER BY SUM(Amount * Price)

참고로, 위 쿼리문에서 SUM을 AVG로 바꾸면, 성능 차이가 3배 정도 발생한다.

WHERE와 HAVING에 관한 다음 MSDN 페이지를 읽어보는 것도 좋다.