정리햇던거를 다시 정리함
분석함수란?
- 테이블에 있는 데이터를 특정 용도로 분석하여 결과를 반환하는 함수
- 복잡한 계산을 단순하게 처리해주는 함수
- 쿼리 결과 Set을 대상으로 계산을 수행하는 함수
- SELECT 절에서 수행
- FROM, WHERE, GROUP BY 절에서 사용불가
- ORDER BY 구문에서는 사용가능
집계함수 vs 분석함수
집계함수
여러 행 또는 테이블 전체행으로 부터 그룹별로 집계하여 결과를 반환함
SELECT detp
, SUM(sal) s_sal
FROM emp
GROUP BY deptno;
분석함수
집계 결과를 각 행 마다 보여준다
SELECT deptno,
empno,
sal,
SUM(sal) OVER (PARTITION BY deptno) s_sal
FROM emp;
집계함수vs 분석함수
집계함수는 그룹별 최대,최소,합계,평균,건수 등을 구할때 사용됨 그룹별 1개의 행을 반환한다는게 특징
분석함수는 그룹단위로 값을 계산한다는 점에서 집계함수와 유사, but 그룹마다가 아니라 결과Set의 각 행마다 집계결과를 보여준다는 점에서 집계함수와 상당한 차이가 있다.
분석함수는 쉽게 생각해서, 그룹별 계산결과를 각 행마다 보여주는것이다.
Syntax
SELECT ANALYTIC_FUNCTION (arguments)
OVER ( [PARTITION BY 컬럼List ]
[ORDER BY 컬럼 List]
[ WINDOWING 절 (Rows|Range Between)]
)
FROM 테이블 명;- ANALYTIC_FUNCTION: 분석 함수명(입력인자)
- OVER:분석함수임을 나타내는 키워드
- PARTITION BY 계산 대상 그룹을 정함
- ORDER BY: 대상 그룹에 대한 정렬을 수행
- WINDOWING 절 : 분석함수의 계산 대상 범위를 지정
- ORDER BY절에 종석적임
- 기본 생략 구문:정렬된 결과의 처음부터 현재형까지 [RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW]
분석함수의 종류
- 순위함수: RANK, DENSE_RANK, ROW_NUMBER, NTILE
- 집계함수: SUM, MIN, MAX, AVG, COUNT
- 기타함수: LEAD, LAG, FIRST_VALUE, LAST_VALUE, RATIO_TO_REPORT
- 분석함수(?) : KEEP,LISTAGG
순위함수
- RANK함수 : 순위를 부여하는 함수로 동일순위 처리 가능(중복 순위 다음 순서 건너뜀 ex) 1,2,2,4)
- DENSE_RANK 함수: RANK함수와 같은 역할 but 동일 등수가 순위에 영향 X(중복순위 다음 순위 연속 ex)1,2,2,3
- ROW_NUMBER 함수는 특정 순위로 일련번호를 제공하는 함수, 동일순위처리가 불가능
(중복순위 없이 유일값-1,2,3,4) - 순위 함수 사용시 ORDER BY절은 필수로 입력해야 함
순위함수 -RANK
급여가 높은 순서대로 순위를 구하는 예제
SELECT deptno, empno, sal
,RANK() OVER(ORDER BY sal DESC) rk
FROM emp;
DEPTNO EMPNO SAL RK
-------- ---------- ---------- ----------
10 7839 5000 1
20 7788 3000 2
20 7902 3000 2
20 7566 2975 4
30 7698 2850 5
10 7782 2450 6
30 7499 1600 7
30 7844 1500 8
10 7934 1300 9
30 7521 1250 10
30 7654 1250 10
20 7876 1100 12
30 7900 950 13
20 7369 800 14급여가 같은경우 순위가 동일순위로 처리되는 것을 확인할 수잇다.
아래는 부서별(PARTITION BY deptno)로 급여가 높은 순서대로(ORDER BY sal DESC)순위를 구하는 예제
SELECT deptno, empno, sal
,RANK() OVER (PARTITION BY detpno
ORDER BY sal DESC) rk
FROM emp;
DEPTNO EMPNO SAL RK
-------- ---------- ---------- ----------
10 7839 5000 1
10 7782 2450 2
10 7934 1300 3
20 7788 3000 1
20 7902 3000 1
20 7566 2975 3
20 7876 1100 4
20 7369 800 5
30 7698 2850 1
30 7499 1600 2
30 7844 1500 3
30 7654 1250 4
30 7521 1250 4
30 7900 950 6순위함수의 특징은 ORDER BY절은 생략할수없고 WINDOWING절은 사용할 수 없다
순위함수 비교
RANK,DENSE_RANK,ROW_NUMBER의 차이점을 이해해보는 예제
SELECT detpno,empno,sal
, RANK() OVER(ORDER BY sal DESC) rk
, DENSE_RANK() OVER(ORDER BY sal DESC) dr
, ROW_NUMBER() OVER(ORDER BY sal DESC) rn
FROM emp;
DEPTNO EMPNO SAL RK DR RN
------- ---------- ---------- ---------- ---------- ----------
10 7839 5000 1 1 1
20 7788 3000 2 2 2
20 7902 3000 2 2 3
20 7566 2975 4 3 4
30 7698 2850 5 4 5
10 7782 2450 6 5 6
30 7499 1600 7 6 7
30 7844 1500 8 7 8
10 7934 1300 9 8 9
30 7521 1250 10 9 10
30 7654 1250 10 9 11
20 7876 1100 12 10 12
30 7900 950 13 11 13
20 7369 800 14 12 순위함수- NTILE(분류)
NTILE함수는 쿼리의 결과를 n개의 그룹으로 분류하는 기능을 제공
아래 예제에서 GRP2는 두 개의 그룹으로, GRP3는 세 개의 그룹으로, GRP5는 다섯개의 그룹으로 분류하는 것을 알 수 있다.
즉 NTILE : 지정한 숫자만큼의 그룹으로 분류이다.
SELECT empno
, NTILE(2) OVER(ORDER BY empno) grp2
, NTILE(3) OVER(ORDER BY empno) grp3
, NTILE(5) OVER(ORDER BY empno) grp5
FROM emp;
EMPNO GRP2 GRP3 GRP5
-------- ---------- ---------- ----------
7369 1 1 1
7499 1 1 1
7521 1 1 1
7566 1 1 2
7654 1 1 2
7698 1 2 2
7782 1 2 3
7788 2 2 3
7839 2 2 3
7844 2 2 4
7876 2 3 4
7900 2 3 4
7902 2 3 5
7934 2 3 5
집계함수
SUM,MIN,MAX,AVG,COUNT등의 집계 함수도 분석함수로 사용할 수 있다.
SUM(누적합계)
---아래는 사원의 전체급여 합계(all_sum), 부서별 급여합계(dept_sum), 부서별로 누적합계(nujuk_sum)를 조회하는예
SELECT empno, ename,deptno,
SUM(sal) OVER () all_sum,
SUM(sal) OVER(PARTITION BY deptno) dept_sum,
SUM(sal) OVER(PARTITION BY deptno ORDER BY empno) nujuk_sum
FROM emp;
EMPNO ENAME DEPTNO ALL_SUM DEPT_SUM NUJUK_SUM
------ ------- ---------- ---------- ---------- ----------
7782 CLARK 10 29025 8750 2450
7839 KING 10 29025 8750 7450
7934 MILLER 10 29025 8750 8750
7369 SMITH 20 29025 10875 800
7566 JONES 20 29025 10875 3775
7788 SCOTT 20 29025 10875 6775
7876 ADAMS 20 29025 10875 7875
7902 FORD 20 29025 10875 10875
7499 ALLEN 30 29025 9400 1600
7521 WARD 30 29025 9400 2850
7654 MARTIN 30 29025 9400 4100
7698 BLAKE 30 29025 9400 6950
7844 TURNER 30 29025 9400 8450
7900 JAMES 30 29025 9400 9400 분석함수 OVER절 안에서 ORDER BY절을 사용하면 ORDER BY 절의 컬럼을 기준으로 누적되어 계산됨을 알 수 있다
윈도우 함수
-
분석함수 중 윈도우절(WINDOWING 절)을 사용하는 함수를 윈도우 함수라고 함
-
윈도우 절을 사용하면 PARTITION BY 절에 명시된 그룹을 좀더 세부적으로 그룹핑 가능
-
윈도우 절은 분석함수 절에서 AVG<COUNT,SUM,MAX,MIN만 사용할 수 있다.
WINDOWING 절 Syntax
윈도우 함수 OVER( PARTITION BY 절 ORDER BY 절 [ASC|DESC] ROWS | RANGE BETWEEN UNBOUNDED PRECEDING | n PRECEDING | CURRENT ROW AND UNBOUNDED FOLLOWING | n FOLLOWING | CURRENT ROW -
ROWS : 물리적인 ROW단위로 행 집합을 지정
-
RANGE :논리적인 상대번지로 행집합을 지정
-
BETWEEN-AND절: 윈도우의 시작과 끝 위치를 지정
-
UNBOUNDED PRECEDING: PARTITION의 첫번째 로우에서 윈도우가 시작
-
UNBOUNDED FOLLOWING: PARTITION의 마지막 로우에서 윈도우가 시작
-
CURRENT ROW: 윈도우의 시작이나 끝 위치가 현재 로우
기타함수
LAG,LEAD(다른행의 값을 참조)
- LAG(컬럼명[,offset][,default]): 현재 ROW 기준으로 이전 행을 참조
- LEAD(컬럼명(,offset][,default]) : 현재 ROW기준으로 다음행을 참조
- LAG,LEAD 함수 사용시 ORDER BY절은 필수 입력사항이다.
RATIO_TO_REPORT(점유율)
RATIO_TO_REPORT 함수는 합에 대한 값의 비율을 계산
SELECT deptno
, SUM(sal) sal
, SUM(SUM(sal)) OVER() tot
, ROUND(SUM(sal) / SUM(SUM(sal)) OVER(), 2) rat1
, ROUND(RATIO_TO_REPORT(SUM(sal)) OVER(), 2) rat2
FROM emp
GROUP BY deptno
;
DEPTNO SAL TOT RAT1 RAT2
------- ---------- ---------- ---------- ----------
30 9400 29025 .32 .32
20 10875 29025 .37 .37
10 8750 29025 .3 .3- SAL: 부서별 급여 합계를 집계함수를 사용하여 계산한 결과
- TOT: SUM 분석함수를 이용하여 부서별 급여합계의 전체 합계를 계산한 결과
- RAT1: 집계함수를 이용해서 계산한 부서별 합계에서 분석함수를 이용해 계산한 전체 합계를 나누어 비율을 계산한 결과
- RAT2: RATIO_TO_REPORT 분석함수를 사용하여 부서별 급여 합계의 비율을 계산한 결과이다.
FIRST_VALUE, LAST_VALUE
- FIRST_VALUE :정렬된 값 중에서 첫 번째 값을 반환
- LAST_VALUE : 정렬된 값중에서 맨 마지막 값을 반환
아래 예제는 사원순 정렬의 첫번째 사원 급여(F_EMP)와 마지막 사원 급여(L_EMP)를 조회하는 예이다.
SELECT empno
,sal
,FIRST_VALUE(sal) OVER (ORDER BY empno) f_emp
,LAST_VALUE(sal) OVER( ORDER BY empno) l_emp
FROM emp;
EMPNO SAL F_EMP L_EMP
------ ---------- ---------- ----------
7369 800 800 800
7499 1600 800 1600
7521 1250 800 1250
7566 2975 800 2975
7654 1250 800 1250
7698 2850 800 2850
7782 2450 800 2450
7788 3000 800 3000
7839 5000 800 5000
7844 1500 800 1500
7876 1100 800 1100
7900 950 800 950
7902 3000 800 3000
7934 1300 800 1300ORDER BY 사용시 분석함수의 기본 계산 범위는 처음부터 현재까지 이다.
KEEP,LISTAGG
KEEP
사용방법
- MAX() KEEP(DENSE_RANK FIRST[LAST] ORDER BY)
- MIN() KEEP(DENSE_RANK FIRST[LAST] ORDER BY)
아래는 부서별 최대급여자 급여(MAX_SAL)와 사번(MAX_E), 최소급여자 (MIN_SAL)와 사번(MIN_E)을 조회하는 예제
SELECT deptno
, MAX(sal) max_sal
, MAX(empno) KEEP(DENSE_RANK LAST ORDER BY sal) max_e
주니어 개발자 되고싶어요