그룹 및 집계 함수

ROLL UP

group by의 확장, 소계/총계 계산

select deptno, nvl(job,'null'), sum(sal)
from emp
group by rollup(deptno, job)
order by 1,2;

10		CLERK		1300
10		MANAGER		2450
10		PRESIDENT	5000
10		null		8750

20		ANALYST		6000
20		CLERK		1900
20		MANAGER		2975
20		null		10875

30		CLERK		950
30		MANAGER		2850
30		SALESMAN	5600
30		null		9400

null    null    	29025

 

분석함수

하나의 행을 계산하는데 다른 행들의 데이터를 활용할 수 있다

 

Partition By

group by와 동일한 역할

SELECT EMPNO, ENAME, SAL, DEPTNO, ROUND(AVG(SAL) OVER (PARTITION BY DEPTNO),0) AVGSAL
FROM EMP;
-- 본인부서의 평균 SAL

EMPNO   ENAME   SAL   DEPTNO   AVGSAL
7934	MILLER	1300	10	2917
7782	CLARK	2450	10	2917
7839	KING	5000	10	2917
7902	FORD	3000	20	2175
7788	SCOTT	3000	20	2175
7876	ADAMS	1100	20	2175
7566	JONES	2975	20	2175
7369	SMITH	800	20	2175
7900	JAMES	950	30	1567
7844	TURNER	1500	30	1567
7698	BLAKE	2850	30	1567
7521	WARD	1250	30	1567
7499	ALLEN	1600	30	1567
7654	MARTIN	1250	30	1567

 

Order By

정렬 순서에 따라 누적 계산

SELECT EMPNO, ENAME, SAL, DEPTNO, SUM(SAL) OVER (ORDER BY SAL) SUMSAL
FROM EMP;

EMPNO	ENAME	SAL	DEPTNO	SUMSAL
7369	SMITH	800	20	800
7900	JAMES	950	30	1750
7876	ADAMS	1100	20	2850
7654	MARTIN	1250	30	5350
7521	WARD	1250	30	5350
7934	MILLER	1300	10	6650
7844	TURNER	1500	30	8150
7499	ALLEN	1600	30	9750
7782	CLARK	2450	10	12200
7698	BLAKE	2850	30	15050
7566	JONES	2975	20	18025
7788	SCOTT	3000	20	24025
7902	FORD	3000	20	24025
7839	KING	5000	10	29025

SELECT EMPNO, ENAME, SAL, DEPTNO, SUM(SAL) OVER (ORDER BY EMPNO
                                RANGE BETWEEN 100 PRECEDING AND 100 FOLLOWING) SUMSAL
FROM EMP;
-- 논리적 WINDOW, EMPNO 기준으로 앞 -100, 뒤 +100 더해서 계산

EMPNO   ENAME   SAL   DEPTNO   SUMSAL
7369	SMITH	800	20	800
7499	ALLEN	1600	30	5825
7521	WARD	1250	30	5825
7566	JONES	2975	20	7075
7654	MARTIN	1250	30	7075
7698	BLAKE	2850	30	9550
7782	CLARK	2450	10	15900
7788	SCOTT	3000	20	15900
7839	KING	5000	10	18300
7844	TURNER	1500	30	18300
7876	ADAMS	1100	20	18300
7900	JAMES	950	30	12850
7902	FORD	3000	20	12850
7934	MILLER	1300	10	12850

 

Rank

순위 계산

SELECT EMPNO, ENAME, DEPTNO,SAL, RANK() OVER (ORDER BY SAL DESC) RANK,
DENSE_RANK() OVER (ORDER BY SAL DESC) DRANK,
ROW_NUMBER() OVER (ORDER BY SAL DESC) RNUMBER
FROM EMP;

EMPNO   ENAME   DEPTNO  SAL RANK    DRANK   RNUMBER
7839	KING	10	5000	1	1	1
7902	FORD	20	3000	2	2	2
7788	SCOTT	20	3000	2	2	3
7566	JONES	20	2975	4	3	4
7698	BLAKE	30	2850	5	4	5
7782	CLARK	10	2450	6	5	6
7499	ALLEN	30	1600	7	6	7
7844	TURNER	30	1500	8	7	8
7934	MILLER	10	1300	9	8	9
7521	WARD	30	1250	10	9	10
7654	MARTIN	30	1250	10	9	11
7876	ADAMS	20	1100	12	10	12
7900	JAMES	30	950	13	11	13
7369	SMITH	20	800	14	12	14

 

LISTAGG

여러 행의 값을 한 행으로 나열

within group과 order by는 필수

SELECT DEPTNO, LISTAGG(ENAME, ', ') WITHIN GROUP (ORDER BY ENAME)
FROM EMP
GROUP BY DEPTNO;

DEPTNO  ENAMES
10	CLARK, KING, MILLER
20	ADAMS, FORD, JONES, SCOTT, SMITH
30	ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD

 

 

강의정보

https://www.multicampus.com/search?siteAppntSrcwdNm=&siteRalSrcwdNm=&kwd=%EC%97%85%EB%AC%B4%EC%97%90+%EB%B0%94%EB%A1%9C+%EC%93%B0%EB%8A%94+SQL+%ED%8A%9C%EB%8B%9D&input_kwd=%EC%97%85%EB%AC%B4%EC%97%90+%EB%B0%94%EB%A1%9C+%EC%93%B0%EB%8A%94+SQL+%ED%8A%9C%EB%8B%9D&category=TOTAL 

멀티캠퍼스