개요
2022.06.10 - [MySQL] MAX, MIN, COUNT, DISTINCT와 2023.05.14 - [MySQL] 윈도우 함수 - OVER 글에서 집계 함수와 윈도우 함수에 대해서 간단히 알아보았다.
이 글에서는 집계 함수와 OVER 절을 함께 사용하는 예시에 대해 알아보려고 한다. MySQL을 기준으로 확인해본다.
집계 함수
MySQL에서 제공하는 집계 함수는 다음과 같다. (참고 : 12.19.1 Aggregate Function Descriptions)
| 이름 | 설명 |
| AVG() | 인수의 평균값을 반환 |
| BIT_AND() | 반환 비트 AND |
| BIT_OR() | 반환 비트 OR |
| BIT_XOR() | 비트별 XOR 반환 |
| COUNT() | 반환된 행 수를 반환합니다. |
| COUNT(DISTINCT) | 다양한 값의 수를 반환합니다. |
| GROUP_CONCAT() | 연결된 문자열 반환 |
| JSON_ARRAYAGG() | 결과 집합을 단일 JSON 배열로 반환 |
| JSON_OBJECTAGG() | 단일 JSON 개체로 결과 집합 반환 |
| MAX() | 최대값 반환 |
| MIN() | 최소값 반환 |
| STD() | 모집단 표준편차 반환 |
| STDDEV() | 모집단 표준편차 반환 |
| STDDEV_POP() | 모집단 표준편차 반환 |
| STDDEV_SAMP() | 샘플 표준편차 반환 |
| SUM() | 합계 반환 |
| VAR_POP() | 모집단 표준 분산 반환 |
| VAR_SAMP() | 샘플 분산 반환 |
| VARIANCE() | 모집단 표준 분산 반환 |
이 중 OVER 절에서는 회색으로 표시한 함수를 제외하고 전부 사용할 수 있다.
다만 이 글에서는 많이 사용하는 AVG, COUNT, MAX, MIN, SUM 정도만 정리한다.
예시 테이블
아래의 사원 번호(empno), 사원 이름(ename), 직업(job), 급여(sal) 데이터로 구성된 테이블을 예시로 사용한다.
DDL
더보기
CREATE TABLE emp (
empno int,
ename varchar(30),
job varchar(30),
sal int
)
INSERT INTO emp
VALUES (7902, 'FORD', 'ANALYST', 3000),
(7788, 'SCOTT', 'ANALYST', 3000),
(7369, 'SMITH', 'CLERK', 800),
(7900, 'JAMES', 'CLERK', 950),
(7876, 'ADAMS', 'CLERK', 1100),
(7934, 'MILLER', 'CLERK', 1300),
(7782, 'CLARK', 'MANAGER', 2450),
(7698, 'BLAKE', 'MANAGER', 2850),
(7566, 'JONES', 'MANAGER', 2975),
(7839, 'KING', 'PRESIDENT', 5000),
(7654, 'MARTIN', 'SALESMAN', 1250),
(7521, 'WARD', 'SALESMAN', 1250),
(7844, 'TURNER', 'SALESMAN', 1500),
(7499, 'ALLEN', 'SALESMAN', 1600);| empno | ename | job | sal |
| ----- | ------ | --------- | ---- |
| 7902 | FORD | ANALYST | 3000 |
| 7788 | SCOTT | ANALYST | 3000 |
| 7369 | SMITH | CLERK | 800 |
| 7900 | JAMES | CLERK | 950 |
| 7876 | ADAMS | CLERK | 1100 |
| 7934 | MILLER | CLERK | 1300 |
| 7782 | CLARK | MANAGER | 2450 |
| 7698 | BLAKE | MANAGER | 2850 |
| 7566 | JONES | MANAGER | 2975 |
| 7839 | KING | PRESIDENT | 5000 |
| 7654 | MARTIN | SALESMAN | 1250 |
| 7521 | WARD | SALESMAN | 1250 |
| 7844 | TURNER | SALESMAN | 1500 |
| 7499 | ALLEN | SALESMAN | 1600 |
AVG
파티션 별 평균을 구한다.
예시로 job 별 평균 급여(sal)를 구한다. job 별로 집계 함수를 사용할 때는 OVER 절에 PARITION BY로 job을 지정한다.
SELECT
*,
AVG(sal) OVER (PARTITION BY job) AS avg_sal_by_job
FROM emp;
실행 결과
| empno | ename | job | sal | avg_sal_by_job |
| ----- | ------ | --------- | ---- | -------------- |
| 7902 | FORD | ANALYST | 3000 | 3000.0000 |
| 7788 | SCOTT | ANALYST | 3000 | 3000.0000 |
| 7369 | SMITH | CLERK | 800 | 1037.5000 |
| 7900 | JAMES | CLERK | 950 | 1037.5000 |
| 7876 | ADAMS | CLERK | 1100 | 1037.5000 |
| 7934 | MILLER | CLERK | 1300 | 1037.5000 |
| 7782 | CLARK | MANAGER | 2450 | 2758.3333 |
| 7698 | BLAKE | MANAGER | 2850 | 2758.3333 |
| 7566 | JONES | MANAGER | 2975 | 2758.3333 |
| 7839 | KING | PRESIDENT | 5000 | 5000.0000 |
| 7654 | MARTIN | SALESMAN | 1250 | 1400.0000 |
| 7521 | WARD | SALESMAN | 1250 | 1400.0000 |
| 7844 | TURNER | SALESMAN | 1500 | 1400.0000 |
| 7499 | ALLEN | SALESMAN | 1600 | 1400.0000 |
COUNT
파티션 별 행의 수를 구한다.
예시로 job 별 사원의 수를 구하고 사원 번호 순으로 나열한다.
SELECT
*,
COUNT(empno) OVER (PARTITION BY job) AS emp_cnt_by_job
FROM emp
ORDER BY empno
;
실행 결과
| empno | ename | job | sal | emp_cnt_by_job |
| ----- | ------ | --------- | ---- | -------------- |
| 7369 | SMITH | CLERK | 800 | 4 |
| 7499 | ALLEN | SALESMAN | 1600 | 4 |
| 7521 | WARD | SALESMAN | 1250 | 4 |
| 7566 | JONES | MANAGER | 2975 | 3 |
| 7654 | MARTIN | SALESMAN | 1250 | 4 |
| 7698 | BLAKE | MANAGER | 2850 | 3 |
| 7782 | CLARK | MANAGER | 2450 | 3 |
| 7788 | SCOTT | ANALYST | 3000 | 2 |
| 7839 | KING | PRESIDENT | 5000 | 1 |
| 7844 | TURNER | SALESMAN | 1500 | 4 |
| 7876 | ADAMS | CLERK | 1100 | 4 |
| 7900 | JAMES | CLERK | 950 | 4 |
| 7902 | FORD | ANALYST | 3000 | 2 |
| 7934 | MILLER | CLERK | 1300 | 4 |
MIN
파티션 별 최소값을 구한다.
예시로 모든 사원에 대한 최소 급여를 구한다.
SELECT
*,
MIN(sal) OVER () AS min_sal
FROM emp
;
실행 결과
| empno | ename | job | sal | min_sal |
| ----- | ------ | --------- | ---- | ------- |
| 7902 | FORD | ANALYST | 3000 | 800 |
| 7788 | SCOTT | ANALYST | 3000 | 800 |
| 7369 | SMITH | CLERK | 800 | 800 |
| 7900 | JAMES | CLERK | 950 | 800 |
| 7876 | ADAMS | CLERK | 1100 | 800 |
| 7934 | MILLER | CLERK | 1300 | 800 |
| 7782 | CLARK | MANAGER | 2450 | 800 |
| 7698 | BLAKE | MANAGER | 2850 | 800 |
| 7566 | JONES | MANAGER | 2975 | 800 |
| 7839 | KING | PRESIDENT | 5000 | 800 |
| 7654 | MARTIN | SALESMAN | 1250 | 800 |
| 7521 | WARD | SALESMAN | 1250 | 800 |
| 7844 | TURNER | SALESMAN | 1500 | 800 |
| 7499 | ALLEN | SALESMAN | 1600 | 800 |
MAX
파티션 별 최대값을 구한다.
예시로 job 별 최대 급여를 받는 사원의 정보를 구한다.
SELECT
*
FROM (SELECT
empno,
ename,
job,
sal,
MAX(sal) OVER (PARTITION BY job) AS max_job_sal
FROM emp) a
WHERE a.sal = a.max_job_sal
;
실행 결과
| empno | ename | job | sal | max_job_sal |
| ----- | ------ | --------- | ---- | ----------- |
| 7902 | FORD | ANALYST | 3000 | 3000 |
| 7788 | SCOTT | ANALYST | 3000 | 3000 |
| 7934 | MILLER | CLERK | 1300 | 1300 |
| 7566 | JONES | MANAGER | 2975 | 2975 |
| 7839 | KING | PRESIDENT | 5000 | 5000 |
| 7499 | ALLEN | SALESMAN | 1600 | 1600 |
SUM
파티션 별 합을 구한다.
예시로 job 별 급여의 합을 구한다.
SELECT
*,
SUM(sal) OVER(PARTITION BY job) AS sum_job_sal
FROM
emp
;
실행 결과
| empno | ename | job | sal | sum_job_sal |
| ----- | ------ | --------- | ---- | ----------- |
| 7902 | FORD | ANALYST | 3000 | 6000 |
| 7788 | SCOTT | ANALYST | 3000 | 6000 |
| 7369 | SMITH | CLERK | 800 | 4150 |
| 7900 | JAMES | CLERK | 950 | 4150 |
| 7876 | ADAMS | CLERK | 1100 | 4150 |
| 7934 | MILLER | CLERK | 1300 | 4150 |
| 7782 | CLARK | MANAGER | 2450 | 8275 |
| 7698 | BLAKE | MANAGER | 2850 | 8275 |
| 7566 | JONES | MANAGER | 2975 | 8275 |
| 7839 | KING | PRESIDENT | 5000 | 5000 |
| 7654 | MARTIN | SALESMAN | 1250 | 5600 |
| 7521 | WARD | SALESMAN | 1250 | 5600 |
| 7844 | TURNER | SALESMAN | 1500 | 5600 |
| 7499 | ALLEN | SALESMAN | 1600 | 5600 |
참고 문서
https://moonpiechoi.tistory.com/128
https://dev.mysql.com/doc/refman/8.0/en/window-functions-usage.html
https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html
728x90