[MySQL] 데이터 집계

집계 함수

• 합(SUM), 평균값(AVG), 최댓값(MAX), 최솟값(MIN), 개수(COUNT)를 구함
• 합과 평균값은 숫자에 대해서만 구할 수 있음
• 최댓값, 최솟값, 개수는 숫자는 물론 문자와 날짜에 대해서도 사용 가능

      - 날짜의 최솟값 -> 가장 빠른(오래된) 날짜

      - 날짜의 최댓값 -> 가장 최근 날짜

• 행 수를 구할 때는 COUNT(*)를 사용함

SELECT COUNT(*) AS emp_count
	FROM employee WHERE reture)date IS NOT NULL;

 

전체 집계

• WHERE 절의 조건을 만족하는 모든 행(조건이 없으면 전체 행)에 대한 집계 수행

      - 예 : 전체 판매 수량, 오늘 하루 전체 판매 금액, 서울 지역 전체 고객 수 집계 등

• 전체에 대한 집계이므로 출력되는 값이 한 행이 됨
• 합계, 평균, 최솟값, 최댓값, 개수 등을 개별적으로 또는 함께 집계할 수 있음
• 시스템부서 직원들의 급여 합 집계 예

SELECT SUM(salary) AS tot_salary
	FROM employ
    WHERE dept_id = 'SYS';

• 전 직원 급여 합, 급여 평균 집계 예

SELECT SUM(salary) AS tot_salary, AVG(salary) AS avg_salary
	FROM employee
    WHERE dept_id = 'SYS';

 

집계 함수와 NULL값

• 집계 함수는 NULL값을 무시함(없는 값으로 간주) -> 의도하지 않은 결과를 얻을 수 있음

      -> SUM() : NULL 값을 제외한 나머지 값의 합을 구함

      -> AVG() : NULL 값을 제외한 나머지 값의 합을 NULL 값을 제외한 값의 개수로 나눔 - 주의

      -> MIN(), MAX() : NULL 값을 제외한 값 중에서 가장 작은/큰 값

      -> COUNT() : NULL 값을 제외한 값의 개수

• COUNT(*)만이 특정 열을 기준으로 하지 않으므로 NULL 값이 무시되지 않음
• NULL 값이 있는 열에 대한 집계 시, 특히 AVG() 함수 사용 시 주의 필요

 

• NULL 값을 무시하고 집계가 수행되기를 원하는 경우도 있음
• 그렇지 않은 경우는 NULL 값을 0으로 대체해 집계를 수행해야 함
• 다음 구문은 salary 열에 NULL 값이 있으면 평균을 집계할 때 무시

SELECT AVG(salary) AS avg_salary
	FROM employee WHERE retire_date IS NULL;

• 방법1 : COUNT(*)을 사용해 얻은 행 수를 분모로 사용

SELECT SUM(salary) / COUNT(*) AS avg_salary
	FROM employee WHERE retire_date IS NULL;

• 방법2 : NULL 값을 0으로 대체해서 평균을 집계

SELECT AVG(IFNULL(salary, 0)) AS avg_salary
	FROM employee WHERE retire_date IS NULL;

 

그룹별 집계

SELECT 집계 기준 열, 집계함수1, 집계함수2, ...
       FROM 테이블
       WHERE 조건
       GROUP BY 집계 기준 열
       ORDER BY 정렬 기준 열 ASC | DESC;

• GROUP BY를 사용해 그룹별 집계를 수행

      - 예 : 지역별 구매 수량 합계, 남녀별 점수 평균, 영업점별 판매 수량과 금액 집계 등

• 집계 결과에 대한 정렬이 필요한 경우 ORDER BY 절을 사용함
• 기본적으로 집계 기준 열을 기준으로 오름차순 정렬이 됨
• 집계 예

SELECT dept_id, COUNT(*) AS emp_count
	FROM employee
    WHERE retire_date IS NULL
    GROUP BY dept_id;

 

그룹별 집계와 조건

SELECT 집계 기준 열, 집계함수1, 집계함수2, ...
       FROM 테이블
       WHERE 조건
       GROUP BY 집계 기준 열
       HAVING 조건
       ORDER BY 정렬 기준 열 ASC | DESC;

• WHERE 절 조건 -> GROUP BY 하기 전 조건 -> 집계 대상 행 필터링
• HAVING 절 조건 -> GROUP BY 한 후 조건 -> 집계 후 표시할 행 필터링
• HAVING 없는 GROUP BY는 존재하지만, GROUP BY 없는 HAVING은 존재하지 않음
• 집계 예

SELECT dept_id, COUNT(*) AS cnt
	FROM employee
    WHERE retire_date IN NULL
    GROUP BY dept_id
    HAVING cnt >= 3
    ORDER BY emp_count DESC;

 

순위 구하기

• RANK() : 값이 같을 경우 같은 순위를 가지며 다음 순위는 건너뜀(1, 2, 2, 4, ...)
• DENSE_RANK() : 값이 같을 경우 같은 순위를 가지며 다음 순위를 이어감(1, 2, 2, 3 ...)
• ROW_NUMBER() : 행 번호를 매김(1, 2, 3, 4 ...)
• nTILE(3) : 1, 1, 1, 2, 2, 2, 3, 3, 3