2장_6절. 윈도우 함수(Window Function)
1.WINDOW FUNCTION 개요
-기존 데이터베이스는 칼럼과 칼럼간의 연산, 칼럼과 칼럼간의 비교(인라인뷰, 서브쿼리)
연결(JOIN), 집합(UNION,INTERSECT,..)에 대한 집계(그룹함수:ROLLUP, CUBE,GROUPING SETS)가 쉽다
-행과 행간의 연산, 행과 행간의 비교하는것은 하나의 SQL문으로 처리하는게 어렵다
-기존 데이터베이스에서 인라인뷰를 이용해 행과 행간의 연산을 하던거를 쉽게 만듬 함수가 WINDOW함수이다.
-분석함수(ANALYTIC), 순위함수(RANK)로 알려져있다.
-윈도우함수는 중첩해서 사용은 못한다. 하지만 서브쿼리는 사용할 수 있다.
1-(1)WINDOW FUNCTION 종류
(1)순위함수 : RANK, DENSE_RANK, ROW_NUMBER함수가 있다.
(2)집계함수 : SUM, MAX, MIN, AVG, COUNT함수가 있다.
(3)그룹내 행순서함수 : FIRST_VALUE, LAST_VALUE, LAG, LEAD함수가 있다.
(4)그룹내 비율함수 : CUME_DIST, PERCENT_RANK(표준), NTILE(비표준), RATIO_TO_REPORT함수(ORACLE에서만지원)
(5)통계분석함수 : 생략
1-(2)WINDOW FUNCTION 문법
-윈도우함수에는 OVER 키워드가 반드시 포함된다.
SELECT WINDOW_FUNCTION(인수) OVER
([파티션 BY 칼럼] [ORDER BY] [WINDOWING절])
FROM 테이블명;
2.그룹 내 순위함수
가.RANK함수
-ORDER BY를 포함한 쿼리문에서 특정 칼럼에 대한 순위를 구하는 함수
-특정 범위는 파티션으로 구분가능하다. DAFAULT값은 전체 행 대상이다
SQL> SELECT JOB, ENAME, SAL,
2 RANK() OVER (ORDER BY SAL DESC) 전체랭크,
3 RANK() OVER (PARTITION BY JOB ORDER BY SAL DESC) 직무별랭크
4 FROM EMP;
->전체랭크순서로 정렬된것
->하나의 SQL문장에 SAL정렬 조건과 JOB정렬조건이 충돌해서 SAL기준으로만 정렬됨.
#직무별랭크 기준 정렬
SQL> SELECT JOB, ENAME, SAL,
2 RANK() OVER (ORDER BY SAL DESC) 전체랭크,
3 RANK() OVER (PARTITION BY JOB ORDER BY SAL DESC) 직무별랭크
4 FROM EMP
5 ORDER BY JOB;
또는
SQL> SELECT JOB, ENAME, SAL,
2 RANK() OVER(PARTITION BY JOB ORDER BY SAL DESC) 직무별랭크
3 FROM EMP;
->직무순서로 정렬된것, JOB기준 정렬만 되고, SAL기준 정렬은 안됨
나.DENSE_RANK 함수
-RANK는 1,2,2,2,5등으로 순위를 매기지만,
DENSE_RANK는 1,2,2,2,3등으로 순위를 매긴다.
SQL> SELECT JOB, ENAME, SAL,
2 RANK() OVER (ORDER BY SAL DESC) RANK,
3 DENSE_RANK() OVER (ORDER BY SAL DESC) DENSE_RANK
4 FROM EMP;
다.ROW_NUMBER 함수
-RANK는 1,2,2,2,5등으로 순위를 매기지만,
DENSE_RANK는 1,2,2,2,3등으로 순위를 매기지만,
ROW_NUMBER는 1,2,3,4,5등으로 순위를 매긴다.(동일한 값도 정렬순서에 따라서 다른 순위를 매김)
SQL> SELECT JOB, ENAME, SAL,
2 RANK() OVER (ORDER BY SAL DESC) RANK,
3 DENSE_RANK() OVER (ORDER BY SAL DESC) DENSE_RANK,
4 ROW_NUMBER() OVER (ORDER BY SAL DESC) ROW_NUMBER
5 FROM EMP;
3.일반 집계 함수
가. SUM 함수
-파티션별 SUM을 구할 수 있다.
-파티션별 합계값을 누적하여 누적값을 출력한다.
SQL> SELECT JOB, ENAME, SAL, SUM(SAL) OVER(PARTITION BY JOB) JOB기준
2 FROM EMP;
나.MAX 함수
-파티션별 MAX를 구할 수 있다.
SQL> SELECT JOB, ENAME, SAL, MAX(SAL) OVER(PARTITION BY JOB) JOB기준최대값
2 FROM EMP;
#JOB기준 최대값을 알기쉽도록 JOB기준정렬후 -> SAL기준 정렬하기.
SQL> SELECT JOB, ENAME, SAL, MAX(SAL) OVER (PARTITION BY JOB) JOB기준최대값
2 FROM EMP
3 ORDER BY 1,3 DESC;<-JOB별 파티션+SAL별 내림차순
->파티션별 최대값이 나옴.
다.MIN함수
-파티션별 MIN을 구할 수 있다.
SQL> SELECT JOB, ENAME, SAL, MIN(SAL) OVER (PARTITION BY JOB) JOB기준최소값
2 FROM EMP
3 ORDER BY 1,3;<-JOB별 파티션+SAL별 오름차순
->파티션별 최소값이 나옴
라.AVG함수
->파티션별 ROWS윈도우를 이용해? 원하는 평균값을 구할 수 있다.
SQL> SELECT MGR, ENAME, SAL, AVG(SAL) OVER(PARTITION BY MGR) MGR_평균임금
2 FROM EMP;
->MGR기준 파티션별 평균임금이 출력됨
마.COUNT함수
-파티션별 ROWS를 이용해 원하는 조건에 맞는 카운트값을 구한다.
SQL> SELECT ENAME, SAL, COUNT(*) OVER (ORDER BY SAL
2 RANGE BETWEEN 50 PRECENDING AND 150 FOLLOWING) AS "50~150급여범위의 사람수"
3 FROM EMP;
RANGE BETWEEN 50 PRECENDING AND 150 FOLLOWING) AS "50~150급여범위의 사람수"
*
ERROR at line 2:
ORA-00905: missing keyword
-왜 오류남???뭔소린지 모르겠군..
4.그룹 내 행 순서 함수
가.FIRST_VALUE함수
-파티션별 윈도우에서 가장 먼저 나온 값을 구한다.
-SQL SERVER는 지원안함
-MIN함수를 이용하여 같은 결과를 얻을 수 있음.
SQL> SELECT DEPTNO, ENAME, SAL,
2 FIRST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC
3 ROWS UNBOUNDED PRECENDING) AS DEPT_RICH
4 FROM EMP;
ROWS UNBOUNDED PRECENDING) AS DEPT_RICH
*
ERROR at line 3:
ORA-00905: missing keyword
-왜 오류남???ROWS...PRECENDING, FOLLOWING 키워드가 없는건가?? 11g Express라 그런가??
나.LAST_VALUE함수
-파티션별 윈도우에서 가장 나중에 나온 값을 구한다.
-SQL SERVER는 지원안함
-MAX함수를 이용하여 같은 결과를 얻을 수 있음.
-FIRST_VALUE처럼 똑같은 오류남.
다.LAG함수
-파티션별 윈도우에서 이전 몇 번째 행의 값을 가져올 수 있다.
-LAG뜻 : 1. 뒤에 처지다, 뒤떨어지다
-SQL SERVER는 지원안함
SQL> SELECT ENAME, HIREDATE, SAL, LAG(SAL) OVER(ORDER BY HIREDATE) AS PREV_SAL
2 FROM EMP;
->SAL기준 자기행 이전 1번째행의 SAL값을 가져옴
#LAG는 파라미터를 3개를 가질 수 있다.
LAH(기준칼럼, 몇번째 이전인지, 가져올 데이터가 없을때 넣을 값)
SQL> SELECT ENAME, HIREDATE, SAL, LAG(SAL, 5, 000) OVER(ORDER BY HIREDATE) AS PREV_SAL
2 FROM EMP;
->BLAKE까지는 가져올 데이터가 없으므로 000값이 들어감.
라.LEAD함수
-파티션별 윈도우에서 이후 몇번째 행의 값을 가져온다.
-LEAD뜻 : 이끌다. LAG의 반대
-SQL SERVER는 지원안함
SQL> SELECT JOB, SAL, LEAD(SAL,1) OVER ( PARTITION BY JOB ORDER BY SAL) "JOB기준 이후의 SAL값"
2 FROM EMP;
->LEAD함수도 파라미터가 3개이다
->LEAD(기준칼럼, 몇번째 이후인지, 값이 없을때 대체할값)
->LEAD(SAL,1) OVER(ORDER BY SAL) : SAL기준 1번째 이후 SAL값을 넣어라
->OVER(PARTITION BY JOB) : JOB기준으로 파티션을 나눠라
->통합 : JOB기준으로 파티션을 나눈후에 1번째 이후의 SAL값을 찾아라, ANALYT와 CLERK는 JOB기준으로 파티션이 다르므로 2번째 행의 ANALYT는 값이 800이 아니라 없다.
5.그룹내 비율함수
가.RATIO_TO_REPORT함수
-파티션내 행별 칼럼값의 백분율을 소수점으로 구한다.
-비율이므로 결과값은 0~1사이의 값이다.
-SQL SERVER는 지원하지 않음
#총 SAL합 기준 백분율
SQL> SELECT ENAME, JOB, SAL, ROUND(RATIO_TO_REPORT(SAL) OVER(),2) AS SAL비율
2 FROM EMP;
#JOB기준으로 파티션한후 JOB의 총SAL기준 백분율
SQL> SELECT ENAME, JOB, SAL, ROUND(RATIO_TO_REPORT(SAL) OVER(PARTITION BY JOB),2) AS SAL비율
2 FROM EMP;
나.PERCENT_RANK함수
-RATIO_TO_REPORT가 칼럼값기준 비율을 나타낸다면, PERCENT_RANK는 행의 개수기준 비율을 나타낸다.
-즉 도수분포비율을 나타냄
SQL> SELECT DEPTNO, ENAME, SAL, PERCENT_RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) AS 직무별분포
2 FROM EMP;
다.CUME_DIST함수
-파티션별 윈도우의 전체건수에서 현재 행보다 작거나 같은 건수에 대한 누적백분율을 구함.
-내가 꼴찌면 나보다 작거나 같은 누적건수가 모든사람이므로 누적백분율은 1
-나의 순위를 백분율로 알 수 있음.
SQL> SELECT DEPTNO, ENAME, SAL, CUME_DIST() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) AS "작거나같은분포"
2 FROM EMP;
라.NTILE 함수
-파티션별 전체건수의 몇분위수를 구한다.
-N의 개수를 기준으로 분류함. N이 4라면 사분위수를 구한다.
#사분위수
SQL> SELECT ENAME, SAL, NTILE(4) OVER (ORDER BY SAL DESC) AS 사분위수
2 FROM EMP;
#십분위수
SQL> SELECT ENAME, SAL, NTILE(10) OVER (ORDER BY SAL DESC) AS 십분위수
2 FROM EMP;
<윈도우함수 정리>
-윈도우 함수는 행끼리 계산을 하기위하여 필요하다.
#문법
SELECT 윈도우함수(파라미터) OVER ([PARTITION BY 칼럼] [ORDER BY 절] [WINDOW 절])
FROM 테이블명
(1)순위함수
RANK() : 칼럼에 대한 순위(1,2,2,4,5)
DENSE_RANK() : 칼럼에 대한 순위(1,2,2,3,4)
ROW_NUMBER() : 칼럼에 대한 순위(1,2,3,4,5)
=>RANK() OVER(PATIRION BY 칼럼 ORDER BY 칼럼)
(2)집계함수
SUM(칼럼) : 파티션별 칼럼의 합계값
MAX(칼럼) : 파티션별 칼럼의 최대값
MIN(칼럼) : 파티션별 칼럼의 최소값
AVG(칼럼) : 파티션별 칼럼의 평균값
COUNT(칼럼) : 파티션별 칼럼개수기준 범위의 계층값?
=>SUM(칼럼) OVER(PARTITION BY 칼럼 ORDER BY 칼럼 WINDOW절
<WINDOW절종류>
ROWS BETWEEN 숫자1 PRECENDING AND 숫자2 FOLLOWING
RANGE BETWEEN 숫자1 PRECENDING AND 숫자2 FOLLOWING
(3)그룹내 행순서함수
FIRST_VALUE(칼럼) : 파티션별 가장 먼저 나온값
LAST_VALUE(칼럼) : 파티션별 가장 끝에 나온값
LAG(칼럼, N, 대체값) : 파티션별 칼럼기준 이전 N번째값, 없으면 대체값을 출력
LEAD(칼럼, N, 대체값) : 파티션별 칼럼기준 이후 N번째값, 없으면 대체값을 출력
=>LEAD(칼럼, 2, 0000) OVER(ORDER BY 칼럼)
(4)그룹내 비율함수
RATIO_TO_REPORT(칼럼) :칼럼총합 기준 해당 행의 칼럼비율값
PERCENT_RANK() OVER(ORDER BY 칼럼) : 행의총개수 기준 해당 행의 누적비율값.
CUME_DIST() OVER(ORDER BY 칼럼) : 칼럼총합 기준 해당 행보다 작거나 같은 건수의 누적비율
NTILE(N) : N퍼센타일에서 해당 행이 속한 퍼센타일을 출력함.
(5)통계분석함수 : 생략
댓글
댓글 쓰기