Analysis function

MySQL 분석함수

분석함수는 순위를 매길 때 쓸 수 있다. 써보는 게 참.. 중요한 사고 포인트.

순위함수 : rank, dense_rank, row_number, ntitle

집계함수(그룹함수) : sum, min, max, avg, count (sum과 avg는 숫자에만 적용된다.)

기타함수 : lead, lag, first_value, last_value, ratio_to_report(mariadb 지원안됨)

SELECT EMPNO, ENAME, DEPTNO, SAL, RANK() OVER(ORDER BY SAL DESC) 급여순위
FROM EMP; # 중복된 RANK가 있으면 다름 순위는 중복된 개수만큼 떨어짐.

SELECT EMPNO, ENAME, DEPTNO, SAL, DENSE_RANK() OVER(ORDER BY SAL DESC) 급여순위
FROM EMP;  #중복된 RANK가 있어도 순위가 더 떨어지지 않는다.


SELECT EMPNO, ENAME, DEPTNO, SAL, NTILE(5) OVER(ORDER BY SAL DESC) FROM EMP;

SELECT EMPNO, ENAME, HIREDATE, ROW_NUMBER() OVER() FROM EMP;
SELECT EMPNO, ENAME, HIREDATE, ROW_NUMBER() OVER() NUMBERING FROM EMP;
SELECT EMPNO, ENAME, HIREDATE, ROW_NUMBER() OVER() NUMBERING FROM EMP LIMIT 2;

SELECT EMPNO, ENAME, HIREDATE, ROW_NUMBER() OVER(ORDER BY HIREDATE ASC) NUMBERING FROM EMP;

SELECT EMPNO, ENAME, SAL, HIREDATE, DEPTNO, SUM(SAL) SUMSAL FROM EMP;
SELECT EMPNO, ENAME, SAL, HIREDATE, DEPTNO, SUM(SAL) OVER() SUMSAL FROM EMP;
SELECT EMPNO, ENAME, SAL, HIREDATE, DEPTNO, SUM(SAL) OVER() 총급여, SUM(SAL) OVER(PARTITION BY DEPTNO) 부서별총급여
FROM EMP
ORDER BY 부서별총급여;

SELECT EMPNO, ENAME, SAL, AVG(SAL) 
OVER(PARTITION BY JOB) 업무별평균급여, 
MAX(SAL) OVER(PARTITION BY JOB) 업무별최대급여
FROM EMP;