집계 함수 사용

  • 3분

T-SQL은 SUM, MAX 및 AVG와 같은 집계 함수를 제공하여 여러 값을 사용해 단일 결과를 반환하는 계산을 수행합니다.

집계 함수 사용

앞서 살펴본 대부분의 쿼리는 WHERE 절로 행을 필터링하여 한 번에 하나의 행에서 작동합니다. 반환된 각 행은 원래 데이터 세트의 한 행에 해당합니다.

많은 집계 함수가 SQL Server에서 제공됩니다. 이 섹션에서는 SUM, MIN, MAX, AVG, COUNT와 같은 가장 일반적인 함수를 살펴보겠습니다.

집계 함수를 사용할 때는 다음 사항을 고려해야 합니다.

  • 집계 함수는 단일(스칼라) 값을 반환하고 단일 값을 사용할 수 있는 SELECT 문의 거의 모든 위치에서 사용할 수 있습니다. 예를 들어 집계 함수는 SELECT, HAVING, ORDER BY 절에서 사용할 수 있지만, WHERE 절에서는 사용할 수 없습니다.
  • COUNT(*)를 사용하는 경우를 제외하고 집계 함수는 NULL을 무시합니다.
  • AS를 사용하여 별칭을 제공하지 않는 한 SELECT 목록의 집계 함수에는 열 헤더가 없습니다.
  • SELECT 목록에 있는 집계 함수는 SELECT 작업에 전달된 모든 행에서 작동합니다. GROUP BY 절이 없으면 WHERE 절에서 필터 조건을 하나라도 충족하는 모든 행이 요약됩니다. 다음 항목에서 GROUP BY에 대해 자세히 알아봅니다.
  • GROUP BY를 사용하지 않는 한 집계 함수를 동일한 SELECT 목록의 함수에 포함되지 않은 열과 결합하면 안 됩니다.

기본 제공 함수 외에 다양한 함수를 제공하기 위해 SQL Server는 .NET CLR(공용 언어 런타임)을 통해 사용자 정의 집계 함수의 메커니즘을 제공합니다. 해당 항목은 이 모듈에서 다루지 않습니다.

기본 제공 집계 함수

앞서 언급했듯이 Transact-SQL은 많은 기본 제공 집계 함수를 제공합니다. 일반적으로 사용되는 함수는 다음과 같습니다.

함수 이름

구문

설명

합계

SUM(expression)

열에서 NULL이 아닌 모든 숫자 값의 합계를 계산합니다.

평균

AVG(expression)

열에서 NULL이 아닌 모든 숫자 값의 평균을 계산합니다(합계/개수).

최소

MIN(expression)

(데이터 정렬 규칙에 따라) 가장 작은 숫자, 가장 이른 날짜/시간 또는 첫 번째 문자열을 반환합니다.

최대

MAX(expression)

(데이터 정렬 규칙에 따라) 가장 큰 숫자, 가장 늦은 날짜/시간 또는 마지막 문자열을 반환합니다.

COUNT or COUNT_BIG

COUNT(*) 또는 COUNT(expression)

(*)가 있으면 NULL 값을 가진 행을 포함하여 모든 행을 계산합니다. 열을 expression으로 지정하면 해당 열에 대해 NULL이 아닌 행의 수를 반환합니다. COUNT는 int를 반환하고, COUNT_BIG은big_int를 반환합니다.

SELECT 절에서 기본 제공 집계를 사용하려면 MyStore 샘플 데이터베이스에서 다음 예제를 참조하세요.

SELECT AVG(ListPrice) AS AveragePrice,
       MIN(ListPrice) AS MinimumPrice,
       MAX(ListPrice) AS MaximumPrice
FROM Production.Product;

이 쿼리의 결과는 다음과 같습니다.

AveragePrice

MinimumPrice

MaximumPrice

744.5952

2.2900

3578.2700

위의 예제는 Production.Product 테이블의 모든 행을 요약한다는 점에 유의하세요. 다음과 같이 WHERE 절을 추가하여 특정 범주의 제품에 대한 평균, 최소, 최대 가격을 반환하도록 쿼리를 쉽게 수정할 수 있습니다.

SELECT AVG(ListPrice) AS AveragePrice,
       MIN(ListPrice) AS MinimumPrice,
       MAX(ListPrice) AS MaximumPrice
FROM Production.Product
WHERE ProductCategoryID = 15;

SELECT 절에서 집계를 사용하는 경우 SELECT 목록에서 참조되는 모든 열을 집계 함수에 대한 입력으로 사용하거나 GROUP BY 절에서 참조해야 합니다.

집계된 결과에 ProductCategoryID 필드를 포함하려는 다음 쿼리를 살펴봅니다.

SELECT ProductCategoryID, AVG(ListPrice) AS AveragePrice,
MIN(ListPrice) AS MinimumPrice,
MAX(ListPrice) AS MaximumPrice
FROM Production.Product;

쿼리를 실행하면 다음 오류가 발생합니다.

메시지 8120, 수준 16, 상태 1, 줄 1

‘Production.ProductCategoryID’ 열이 집계 함수나 GROUP BY 절에 없으므로 선택 목록에서 유효하지 않습니다.

쿼리는 모든 행을 단일 집계 그룹으로 처리합니다. 따라서 모든 열을 집계 함수의 입력으로 사용해야 합니다.

이전 예제에서는 가격 및 수량과 같은 숫자 데이터를 집계했습니다. 일부 집계 함수를 사용하여 날짜, 시간, 문자 데이터를 요약할 수도 있습니다. 다음 예제에서는 날짜 및 문자에 집계를 사용하는 방법을 보여 줍니다.

이 쿼리는 MIN과 MAX를 사용하여 이름순으로 첫 번째 회사와 마지막 회사를 반환합니다.

SELECT MIN(CompanyName) AS MinCustomer, 
       MAX(CompanyName) AS MaxCustomer
FROM SalesLT.Customer;

이 쿼리는 데이터베이스의 데이터 정렬 시퀀스에서 CompanyName의 첫 번째 값과 마지막 값을 반환합니다. 이 경우 해당 시퀀스는 사전순입니다.

MinCustomer

MaxCustomer

자전거 매장

Yellow Bicycle Company

다른 함수는 집계 함수와 중첩될 수 있습니다.

예를 들어 다음 예제에서는 YEAR 스칼라 함수를 사용하여 MIN과 MAX가 계산되기 전에 주문 날짜의 연도 부분만 반환합니다.

SELECT MIN(YEAR(OrderDate)) AS Earliest,
       MAX(YEAR(OrderDate)) AS Latest
FROM Sales.SalesOrderHeader;

가장 이른 날짜

가장 늦은 날짜

2008

2021

MIN과 MAX 함수를 날짜 데이터와 함께 사용하여 가장 이른 시간 및 가장 늦은 시간 값을 반환할 수도 있습니다. 그러나 AVG와 SUM은 정수, 통화, 부동 소수점, 10진 데이터 형식을 포함하는 숫자 데이터에만 사용할 수 있습니다.

집계 함수와 함께 DISTINCT 사용

중복 행을 제거하려면 SELECT 절에서 DISTINCT의 사용을 알아야 합니다. 집계 함수와 함께 사용할 경우 DISTINCT는 요약 값을 계산하기 전에 입력 열에서 중복 값을 제거합니다. DISTINCT는 주문 테이블의 고객과 같은 고유한 값의 발생을 요약하는 경우에 유용합니다.

다음 예에서는 주문 건수와 관계없이 주문을 한 고객의 수를 반환합니다.

SELECT COUNT(DISTINCT CustomerID) AS UniqueCustomers
FROM Sales.SalesOrderHeader;

COUNT(<some_column>)는 열에서 특정 값이 있는 행의 수만 계산합니다. NULL 값이 없는 경우 COUNT(<some_column>)는 COUNT(*)와 동일합니다. COUNT(DISTINCT <some_column>)는 열에 있는 서로 다른 값의 개수를 계산합니다.

NULL이 있는 집계 함수 사용

데이터에 NULL이 존재할 수 있다는 것과 NULL이 집계 함수를 포함한 T-SQL 쿼리 구성 요소와 상호 작용하는 방식을 알아야 합니다. 다음 사항을 고려해야 합니다.

  • (*) 옵션과 함께 사용된 COUNT를 제외하고 T-SQL 집계 함수는 NULL을 무시합니다. 예를 들어 SUM 함수는 NULL이 아닌 값만 합산합니다. NULL은 0으로 계산되지 않습니다. COUNT(*)는 모든 열에서 값의 여부와 관계없이 모든 행을 계산합니다.
  • 열에 NULL이 있으면 AVG 계산 결과가 정확하지 않을 수 있으며 채워진 행만 합산되고 NULL이 아닌 행의 수로 합계가 나뉩니다. AVG(<column>)와 (SUM(<column>)/COUNT(*))의 결과는 다를 수 있습니다.

예를 들어 t1이라는 다음 테이블을 살펴봅시다.

C1

C2

1

NULL

2

10

3

20

4

30

5

40

6

50

이 쿼리는 AVG가 NULL을 처리하는 방법과 SUM/COUNT(*)로 계산된 열을 통해 평균을 계산하는 방법의 차이를 보여 줍니다.

SELECT SUM(c2) AS sum_nonnulls, 
    COUNT(*) AS count_all_rows, 
    COUNT(c2) AS count_nonnulls, 
    AVG(c2) AS average, 
    (SUM(c2)/COUNT(*)) AS arith_average
FROM t1;

결과는 다음과 같습니다.

sum_nonnulls

count_all_rows

count_nonnulls

average

arith_average

150

6

5

30

25

이 결과 세트에서 average라는 열은 내부적으로 150의 합계를 가져오고 c2 열의 null이 아닌 값의 개수로 나누는 집계입니다. 계산은 150/5 또는 30입니다. arith_average라는 열은 합계를 모든 행의 수로 명시적으로 나눕니다. 따라서 계산은 150/6 또는 25입니다.

NULL 여부와 관계없이 모든 행을 요약해야 하는 경우 NULL을 집계 함수에서 무시되지 않는 다른 값으로 바꾸는 것이 좋습니다. COALESCE 함수를 이용하여 이 작업을 수행할 수 있습니다.