[SQL 입문] 집계 함수

05 - 3 집계 함수

집계 함수는 데이터를 그룹화하고 요약할 떄 사용합니다.

 

조건에 맞는 데이터 개수 세기 : COUNT, COUNT_BIG 함수

조건에 맞는 데이터 개수를 세고 싶다면 COUNT 함수와 COUNT_BIG 함수를 사용합니다. COUNT 함수가 반환하는 값은 INT 범위이고 COUNT_BIG 함수가 반환하는 값은 BIGINT 범위이므로, 만약 데이터 개수가 21억 개를 초과할 것이로 예상되면 COUNT_BIG 함수를 사용합니다.

-- 조건에 맞는 데이터 개수 세기 : nasdaq_company 테이블의 전체 행 수
SELECT COUNT(*) FROM nasdaq_company

-- 조건에 맞는 데이터 개수 세기 : nasdaq_company 테이블의 그룹별 행 수 1
SELECT ipo_year, COUNT(*) FROM nasdaq_company
GROUP BY ipo_year
ORDER BY ipo_year

-- 조건에 맞는 데이터 개수 세기 : nasdaq_company 테이블의 그룹별 행 수 2
SELECT ipo_year, sector, COUNT(*) FROM nasdaq_company
GROUP BY ipo_year, sector
ORDER BY ipo_year desc, sector

COUNT 함수를 사용할 때 주의할 점은 COUNT 함수에 전체 열이 아닌 특정 열만 지정하면 해당 열의 NULL값은 제외한다는 것입니다. 그래서 전체 데이터 개수와 COUNT 함수로 얻은 데이터 개수가 다를 수 있습니다.

-- 조건에 맞는 데이터 개수 세기 : nasdaq_company 테이블의 그룹별 행 수 3
SELECT COUNT(*) AS all_row, COUNT(sector) AS sector_row FROM nsadaq_company

COUNT 함수를 사용할 때 DISTINCT 문을 조합하면 NULL값이 아닌 고윳값의 데이터 개수를 얻을 수도 있습니다.

-- 조건에 맞는 데이터 개수 세기 : nasdaq_company 테이블의 그룹별 행 수 4
SELECT
COUNT(*) AS all_row,
COUNT(sector) AS sector_row,
COUNT(DISTINCT sector) AS sector_distinct
FROM nasdaq_company

 

데이터 합 구하기 : SUM 함수

숫자나 돈 관련 값을 합할 때는 SUM 함수를 사용합니다. SUM 함수는 모든 행의 값을 합하ㅣ만 DISTINCT 문을 조합해 중복값을 무시하고 고윳값에만 SUM 함수를 적용할 수도 있습니다.

-- 데이터 합 구하기 : DISTINCT 문으로 고윳값만 계산
SELECT SUM(close_price) FROM nasdaq_company

SELECT SUM(DISTINCT close_price) FROM nasdaq_company

-- 데이터 합 구하기 : GROUP BY 문으로 그룹별 합산
SELECT sector, SUM(close_price) FROM nasdaq_company
GROUP BY sector

입력 자료형	반환 자료형
tinyint	int
smallint	int
int	int
bigint	bigint
decimal(p,s)	decimal(38,s)
money, smallmoney	money
float, real	float

SUM 함수의 입력 자료형과 반환 자료형

만약 SUM 함수의 반환값이 반환 자료형을 초과하면 오류가 발생합니다.

-- 데이터 합 구하기 : 반환값이 반환 자료형을 초과해 오류 발생
WITH cte_sum (price)
AS (
SELECT 1000000000 AS price UNION ALL
SELECT 1000000000 AS price UNION ALL
SELECT 1000000000 AS price
)
SELECT SUM(price) FROM cte_sum

-- 메시지 8115, 수준 16, 상태 2, 줄 1
-- expression을(를) 데이터 형식 int(으)로 변환하는 중 산술 오버플로 오류가 발생했습니다.

-- 데이터 합 구하기 : 자료형을 변경해 오류 해결
WITH cte_sum (price)
AS (
SELECT 1000000000 AS price UNION ALL
SELECT 1000000000 AS price UNION ALL
SELECT 1000000000 AS price
)
SELECT SUM(CAST(price AS bigint)) FROM cte_sum

-- 3000000000

 

데이터 평균 구하기 : AVG 함수

AVG 함수는 평군을 구할 때 사용합니다. AVG 함수의 특징은 NULL값은 무시한다는 것입니다. 그리고 앞에서 다른 함수에 DISTINCT 문을 조합했던 것처럼 DISTINCT 문을 조합해 중복값을 무시하고 고윳값에만 AVG 함수를 적용할 수 있습니다.

-- 데이터 평균 구하기 : DISTINCT 문으로 고윳값만 계산
SELECT AVG(close_price) FROM nasdaq_company

SELECT AVG(DISTINCT close_price) FROM nasdaq_company

-- 데이터 평균 구하기 : GROUP BY 문으로 그룹별 평균 계산
SELECT sector, AVG(close_price) FROM nasdaq_company
GROUP BY sector

평균을 구할 때는 자료형에 유의해야 합니다. 예를 들어 int형이나 bigint형과 같은 정수끼리 계산한 결과인 10/3은 3.33333···이 나와야 하지만 정수끼리 계산헀으므로 실제로는 3을 얻습니다. 또는 실제 예상하지 못한 값을 얻을 수도 있습니다. 이러한 상황을 해결하려면 평균을 구하려는 열의 형식을 float형이나 decimal형과 같은 실수형으로 변환합니다.

-- 데이터 평균 구하기 : 대상 열 형식을 실수형으로 변환
SELECT 10/3

-- 3

SELECT 10/CONVERT(FLOAT, 3)

-- 3.33333333333333

입력 자료형	반환 자료형
tinyint	int
smallint	int
int	int
bigint	bigint
decimal(p,s)	decimal(38, max(s, 6))
money, smallmoney	money
float, real	float

AVG 함수의 입력 자료형과 반환 자료형

 

최솟값, 최대값 구하기 : MIN, MAX 함수

최솟값과 최댓값을 구할 때는 MIN 함수와 MAX 함수를 사용합니다. 이들 두 함수 역시 DISTINCT 문을 조합할 수 있습니다.

-- 최솟값, 최댓값 구하기 : 특정 열 대상
SELECT MIN(close_price), MAX(close_price) FROM nasdaq_company
WHERE close_price > 0

-- 최솟값, 최댓값 구하기 : 그룹별 대상
SELECT sector, MIN(close_price), MAX(close_price) FROM nasdaq_company
WHERE close_price > 0
GROUP BY sector

MIN 함수와 MAX 함수가 반환하는 값의 자료형은 연산 대상 열의 자료형과 같으며 NULL값은 무시합니다. 문자열의 최솟값과 최댓값을 구할 때는 데이터베이스 속성에 정의된 데이터 정렬 순서에 따라 다르게 출력됩니다.

 

부분합, 총합 구하기 : ROLLUP, CUBE 함수

부분합과 총합을 구하려면 GROUP BY 문을 ROLLUP 함수와 CUBE 함수에 조합합니다. ROLLUP 함수는 GROUP BY ROLLUP(···)에 입력한 열 기준으로 오른쪽에서 왼쪽으로 열을 이동하며 부분합과 총합을 구합니다.

-- 부분합과 총합 구하기 : GROUP BY 문을 ROLLUP 함수에 조합 1
SELECT sector, industry, SUM(close_price)
FROM nasdaq_company
GROUP BY ROLLUP (sector, industry)

-- 부분합과 총합 구하기 : GROUP BY 문을 ROLLUP 함수에 조합 2
SELECT sector, SUM(close_price)
FROM nasdaq_company
GROUP BY ROLLUP (sector)

CUBE 함수 역시 GROUP BY 문과 조합해 사용하며 모든 열 조합의 집계 그룹을 만듭니다. 예를 들어 GROUP BY CUBE(a, b)의 경우 (a, b), (NULL, b), (a, NULL), (NULL, NULL)의 고윳값을 집계하는 그룹을 만듭니다.

-- 부분합과 총합 구하기 : GROUP BY 문을 CUBE 함수에 조합
SELECT sector, industry, SUM(close_price)
FROM nasdaq_company
GROUP BY CUBE(sector, industry)

 

모든 값의 표준편차 구하기 : STDEV, STDEVP 함수

표준편차를 구하려면 STDEV, STDEVP 함수를 사용합니다. STDEV 함수는 모든 값은 편차를, STDEVP 함수는 모집단의 표준편차를 구합니다. DISTINCT 문을 조합하면 고윳값의 표준편차를 검색할 수 있습니다.

-- 표준편차 구하기 : STDEV, STDEVP 함수
SELECT STDEV(close_price), STDEVP(close_price)
FROM nasdaq_company

SELECT STDEV(distinct close_price), STDEVP(distinct close_price)
FROM nasdaq_company

 

실전 SQL

퀴즈 3. stock 테이블에서 symbol이 MSFT인 데이터의 2021년 1월 1일 ~ 2021년 1월 31일까지의 주식 종가(close) 중에 최솟값과 최댓값, 평균값이 얼마인지 구하세요.

SELECT
MIN([close]) AS Low_Price,
MAX([close]) AS Max_Price,
AVG([close]) AS Avg_Price
FROM stock
WHERE symbol = 'MSFT'
AND date >= '2021-01-01'
AND date < '2021-02-01'