-
기존 관계형 데이터베이스는 컬럼과 컬럼 간의 연산, 비교, 연결이나 집합에 대한 집계는 쉬운 반면,
행과 행간의 관계를 정의하거나 행과 행간을 비교, 연산하는 것을 하나의 SQL 에서 처리하는 것은 매우 어려운 문제였다. 부분적이나마 이를 쉽게 하기 위해 만든 함수가 바로 WINDOW FUNTION 이다.
분석함수, 순위 함수로 알려진 윈도우 함수는 데이터 웨어하우스에서 발전한 기능이다.
윈도우 함수는 기존의 사용하던 집계함수도 있고, 새로이 윈도우 전용 함수로 만들어진 기능도 있다.
그리고 윈도우 함수는 다른 함수와 달리 중접해서 사용하지는 못하지만, 서브 쿼리에서는 사용할 수 있다.
종류는 크게 다섯 개의 그룹으로 분류할 수 있다.
그룹 내 순위(RANK) 함수 RANK
DENSE_RANK
ROW_NUMBER그룹 내 집계(AGGREGATE) 함수 SUM, MAX, MIN, AVG, COUNT
* SQL SERVER 에서는 OVER 절의 ORDER BY 구문을 지원하지 않는다.그룹 내 행 순서 관련 함수 FIRST_VALUE , LAST_VALUE
LAG
LEAD그룹 내 비율 관련 함수 CUME_DIST
PERCENT_RANK
NTILE
RATIO_TO_REPORT (oracle 에서만 지원)통계 분석 함수 CORR, COVAR_POP, COVAR_SAMP, STDDEV, STDDEV_POP, STDDEV_SAMP,
VARIANCE, VAR_POP , VAR_SAMP, REGR_(LINEAR REGRESSION),REGR_SLOPE,
REGR_INTERCEPT, REGR_COUNT, REGR_R2, REGR_AVGY,REGR_SXX,REGR_SYY,REGR_SXY윈도우 함수에는 OVER 문구가 키워드로 필수 포함된다.
SELECT WINDOW_FUNCTION (ARGS) OVER [PARTITION BY 컬럼][ORDER BY 절][WINDOWING 절] FROM 테이블명;PARTITION BY : 전체 집합을 기준에 의해 소그룹으로 나눌 수 있다.
WINDOWING 절 : WINDOWING 절은 함수의 대상이 되는 행 기준의 범위를 강력하게 지정할 수 있다. (*SQL SERVER 지원 안함)
ROWS 는 물리적인 결과 행의 수,
RANGE 는 논리적인 값에 의한 범위를 나타낸다.
둘 중 하나를 선택해서 사용할 수 있다.
BETWEEN 사용 타입 ROWS | RANGE BETWEEN UNBOUNDED PRECEDING | CURRENT ROW | VALUE_EXPR PRECEDING/FOLLOWING AND UNBOUNDED PRECEDING | CURRENT ROW | VALUE_EXPR PRECEDING/FOLLOWING BETWEEN 미사용 타입 ROWS | RANGE UNBOUNDED PRECEDING | CURRENT ROW | VALUE_EXPR PRECEDING/FOLLOWING1. RANK 함수
ORDER BY 를 포함한, 특정 컬럼에 대한 순위를 구하는 함수
특정 범위(PARTITION) 내에서 순위를 구할 수도 있고, 전체 데이터에 대한 순서도 구할 수 있다.
동일한 값에 동일한 순위를 부여한다.
예) 사원 테이블에서 급여가 높은 순서와 JOB 별로 급여가 높은 순서를 같이 출력한다.
SELECT JOB,ENAME, SAL ,RANK () OVER (ORDER BY SAL DESC) AS ALL_RK ,RANK () OVER (PARTITION BY JOB ORDER BY SAL DESC) AS JOB_RK FROM EMP;* RANK, DENSE_RANK, ROW_NUMBER 의 차이점
RANK 는 동일한 값에 동일 순서를 1-2-2-4
DENSE_RANK 는 동일 순위를 하나의 건 수로 취급 1-2-2-3
ROW_NUMBER 는 동일한 값에도 고유한 순위를 부여 1-2-3-4
2.SUM 함수
OVER 절 내에 ORDER BY 절을 추가해 파티션 내 데이터를 정렬하고 이전 SALARY 데이터까지 누적값을 출력한다.
RANGE UNBOUNDED PRECEDING 은 현재 행을 기준으로 파티션 내의 첫번째 행까지의 범위를 지정한다.
SELECT MGR, ENAME, SAL ,SUM (SAL) OVER (PARTITION BY MGR ORDER BY SAL RANGE UNBOUNDED PRECEDING) AS SAL_SUM FROM EMP;
* 표시가 되어있는 데이터는 SAL 이 동일하게 1250 이라서 ORDER 를 동일하게 취급해 한 번에 계산되었다.
3. MAX 함수
INLINE VIEW 를 이용해 파티션별 최대값을 다진 행만 추출할 수도 있다.
SELECT MGR, ENAME, SAL FROM ( SELECT MGR, ENAME, SAL ,MAX(SAL) OVER (PARTITION BY MGR) AS MAX_SAL FROM EMP ) WHERE SAL= MAX_SAL이 쿼리는 성능이 저하될 수 있어 아래와 같이 파티션별 최대값을 가진 행만 추출할 때는 순위 함수를 사용하는 것이 바람직하다.
SELECT MGR, ENAME, SAL FROM ( SELECT MGR, ENAME, SAL ,RANK() OVER (PARTITION BY MGR ORDER BY SAL DESC) AS SAL_PK FROM EMP ) WHERE SAL_PK = 1;4. AVG 함수
AVG 함수와 파티션별 ROWS 윈도우를 이용해 원하는 조건에 맞는 데이터에 대한 통곗값을 구할 수 있다.
EMP 테이블에서 같은 매니저를 두고 있는 사원들의 평균 임금을 구하는데,
조건은 같은 매니저 내에서 자기 바로 앞의 사번과 바로 뒤의 사번인 직원을 대상으로 한다.
ROWS BETWEEN 1 PROCEDING 1 FOLLOWING 은 현재 행을 기준으로 앞의 1건, 뒤의 1건을 범위로 지정한다.
SELECT MGR, ENAME, HIREDATE, SAL ,ROUND ( AVG(SAL) OVER (PARTITION BY MGR ORDER BY HIREDATE ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) ) AS AVG_SAL FROM EMP;아래 결과를 보면 동일 MGR 내 ENAME= ALLEN 인 사람은 본인의 앞에 아무도 없다.
그래서 본인의 1600 과 다음 사람 WARD 의 1250 의 평균인 1425가
TURNER 은 자신의 1500, 본인 앞에 WARD 의 1250 , 뒤에 MARTIN 의 1250 의 평균값 1333 이 나온다.
5. COUNT 함수
사원들을 급여 기준으로 정렬하고, 본인의 급여보다 50 이하가 작거나 150 이하로 많은 급여를 받는 인원수를 출력
RANGE BETWEEN 50 PROCEDING AND 150 FOLLOWING 은 현재 행을 기준으로 -50, +150 의 범위 안에 포함된 모든 행이 대상이 된다.
SELECT ENAME, SAL ,COUNT(*) OVER (ORDER BY SAL RANGE BETWEEN 50 PROCEDING AND 150 FOLLOWING) AS EMP_CNT FROM EMP;
ADAMS 의 1100 기준으로 50 아래 1050 , 150 위 1250 사이에 SAL 을 가진 데이터는 WARD 와 MARTIN 뿐이다.
그래서 ADAMS 의 COUNT 는 3 이 나온다.
6. LAG 함수
LAG 함수로 이전 몇 번째 행의 값을 가져올 수 있다. DEFAULT로 1행 앞의 값을 가져온다.
LAG(SAL,2,1) 처럼 인자를 세 개까지 넣을 수 있는데,
두 번째인자는 몇 번째 행의 값인지 지정하며, 세 번째 인자는 NVL 과 같은 역할을 한다. NULL 일 경우 1 이 출력된다.
SELECT ENAME, HIREDATE, SAL ,LAG (SAL) OVER (ORDER BY HIREDATE) AS LAG_SAL FROM EMP WHERE JOB='SALEMAN';
반대로 이후 몇 번째 행의 값을 가져오고 싶을 때는 LEAD 함수를 동일한 방법으로 이용하면 된다.
7, RATIO_TO_REPORT 함수
파티션 내 전체 SUM 값에 대한 백분율을 소수점으로 구할 수 있다. 결과값을 0 초과, 1이하의 범위를 가진다.
개별 RATIO 의 합은 1이 된다.
SELECT ENAME, SAL ,ROUND( RATIO_TO_REPORT (SAL) OVER (), 2 ) AS SAL_PR FROM EMP WHERE JOB='SALEMAN';
NTIL 함수
NTIL(N) 파티션별 전체 건수를 N 등분한 결과를 구할 수 있다.
SELECT ENAME,SAL ,NTILE(4) OVER (ORDER BY SAL DESC) AS NT FROM EMP;14명의 직원이 있을 때 4등분하면 3명씩 4조를 이루고 2명이 남는다. 3-3-3-3-나머지 2명
나머지 2명을 각 앞에 3명 있는 조에 한명씩 넣어 4-4-3-3 으로 결과가 나온다.
출처
SQL 전문가 가이드 - 한국데이터산업진흥원
반응형