그룹함수 (2)

chinggin.tistory.com/494

그룹 함수 (1)

을 이어서 WINDOW 함수에 대해서 알아보려고 한다.

 

 

WINDOW FUNCTION

 

기본 관계형 데이터베이스는 칼럼과 칼럼 간의 연산, 비교, 연결이나 집합에 대한 집계는 쉬운 반면 행과 행간의 관계를 정의하거나, 행과 행간의 비교, 연산하는 것을 하나의 SQL문으로 처리하는 것은 매우 어려운 문제였다.

 

PL/SQL과 같은 절차형 프로그램을 작성하거나, INLINE VIEW를 이용해 복잡한 SQL문을 작성해야 하던 것을 부분적이나마 행과  행간의 관계를 쉽게 정의하기 위해서 만든 함수가 WINDOW FUNCTION이다.

 

- WINDOW FUNCTION은 다른 함수와 달리 중첩해서 사용하지는 못하지만, 서브 쿼리에서는 사용할 수 있다.

 

 

WINDOW FUNCTION의 종류는 크게 5가지로 그룹 지을 수 있다.

 

1. 그룹 내 순위(RANK) 관련 함수는 RANK, DENSE_RANK, ROW_NUMBER 등 함수가 있다. ANSI/ISO SQL 표준과 ORACLE, SQL SERVER 등 대부분의 DBMS에서 지원하고 있다.

 

2. 그룹 내 집계 관련 함수는 일반적으로 많이 사용하는 SUM, MAX, MIN, AVG.. 등등 함수를 사용한다.

 

3. 그룹 내 행 순서 관련 함수는 FIRST_VALUE, LAST_VALUE, LAG, LEAD 함수가 있다.

 

4. 그룹 내 비율 관련 함수는 CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT 함수가 있다.

 

5. 선형 분석을 포함한 통계 분석 관련 함수가 있는데 종류가 많다.

 

 

 

WINDOW FUNCTION SYNTAX

 

SELECT WINDOW_FUNCTION (ARFUMENTS) OVER ([PARTITION BY]. [ORDER BY], [WINDOW 절]) FROM [TABLE_NAME]

 - WINDOW_FUNCTION : 기존 함수 OR 새롭게 추가된 윈도 함수를 사용한다.

 

 - ARGUMENTS : 인수를 가진다. 함수에 따라 여러 개의 인수가 될 수 있다.

 

 - PARTITION BY : 전체 집합을 기준에 의해 소그룹으로 나눌 수 있다.

 

 - ORDERT BY : 어떤 항목에 대해 순위를 지정할지 ORDER BY절에 기술한다.

 

 - WINDOW 절 : WINDOW 절은 함수의 대상이 되는 행 기준의 범위를 강력하게 지정할 수 있다.

                      ROWS는 물 지적인 결과의 행의 수를 RANGE는 논리적인 값에 의한 범위이며 둘 중 하나를 사용한다.

 

 

 

그룹 내 순위 함수

 

RANK 함수

 

 - RANK 함수는 ORDER BY를 포함한 QUERY문에서 특정 항목(칼럼)에 대한 순위를 구하는 함수이다. 이때 특정  범위(PARTITION) 내에서 순위를 구할 수도 있고 전체 데이터에 대한 순위를구할 수도 있다. 또한 동일 값에 대해서는 동일한 순서를 부여한다.

 

--RANK 사원데이터에서 급여가 높은 순서와 JOB별로 급여가 높은 순서를 같이 출력
SELECT JOB,ENAME,SAL,RANK() OVER (ORDER BY SAL DESC) ALL_RANK, RANK() OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK FROM EMP;

 

파티션을 JOB으로 나눈 RANK함수는 각 직업별 SAL 순위를 구했지만 ALL_RANK는 전체 행에 대하여 전체 순위를 구한 것을 확인할 수 있다.

 

 

DENSE_RANK 함수

 

 DENSE_RANK함수는 RANK 함수와 흡사하나 동일한 순위를 하나의 건수로 취급하는 것이 틀린 점이다.

--DENSE_RANK

SELECT JOB,ENAME,SAL,RANK() OVER(ORDER BY SAL DESC) "RANK", DENSE_RANK() OVER (ORDER BY SAL DESC) "DENSE_RANK" FROM EMP e ;

 

ROW_NUMBER 함수

 

 동일한 값이라 하더라도 고유한 순위를 부여한다. 동일 값에 대한 순서까지 관리하고 싶으면 ROW_NUMBER() OVER( COL1, COL2) 같이 추가적인 정렬 기준을 정의해야 한다.

 

--ROW number
SELECT JOB, ENAME, SAL, RANK()OVER(ORDER BY SAL DESC) "RANK",ROW_NUMBER()OVER(ORDER BY SAL DESC) "ROW_NUMBER" FROM EMP;

 

 

 

일반 집계 함수

 

자주 사용하는 그룹 집계 함수는 SUM, MAX, MIN들이 있다

 

SUM 함수를 이용해 파티션 별 윈도의 합을 구할 수 있다.

--사원들의 급여와 같은 매니저를 두고 있는 사원들의 SAL합
SELECT ENAME, SAL, MGR ,SUM(SAL) OVER (PARTITION BY MGR ORDER BY SAL DESC) "SAL_SUM" FROM EMP e;

처음 보면 결과가 의아할 수도 있다.  행간의 연산을 한다고 했다.

MGR =7698을 보면 첫 행은 1600의 SAL을 가지고 있다.

두 번 대행은 1500 SAL을 가지고 있으며 SAL_SUM은 1600 + 1500 인 3100을 가지고 있는 걸 확인할 수 있다.

 

MIN 함수를 이용해 파티션 별 윈도의 최소값을 구할 수 있다.

MAX 함수를 이용해 파티션 별 윈도우의 최대값을 구할 수 있다.

--최대값
SELECT ENAME, SAL, MGR ,MAX(SAL) OVER (PARTITION BY MGR) "SAL_SUM" FROM EMP e;

--최소값
SELECT ENAME, SAL, MGR ,MIN(SAL) OVER (PARTITION BY MGR ORDER BY HIREDATE) "MIN" FROM EMP e;

 

AVG 함수와 파티션별 ROWS 윈도를 이용해 조건에 맞는 데이터에 대한 통계 값을 구할 수 있다.

--평균값
SELECT MGR, ENAME, HIREDATE, SAL, 
ROUND(AVG(SAL) OVER(PARTITION BY MGR ORDER BY HIREDATE ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)) AS MGR_AVG 
FROM EMP;

이때 윈도 우절은 물리적인 결과 행의 범위

 

 

 

COUNT함수와 파티션 별 ROWS 윈도를 이용해 원하는 조건에 맞는 데이터에 대한 통계 값을 구할 수 있다.

--COUNT  급여기준 정렬 본인 급여보다 50이하가 적거나,150이상 많이 주는 경우 현재행 기준 앞뒤 , 현재 행만 범위지정
SELECT ENAME,SAL, COUNT(*) OVER (ORDER BY SAL RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) FROM EMP; 

 

이때 윈도 우절은 논리적인 값의 이한 범위이다.

 

그룹 내 행순 서함수

 

FIRST_VALUE

 

파티션 별 윈도에서 가장 먼저 나온 값을 구하는 함수이다.

--FIRST_VALUES 부서별 연봉이 높은순에서 첫번 째 나온 행출력 동일한 값에대한 순서는 ORDER BY 절에 추가 조건 서술
SELECT DEPTNO,ENAME, SAL, 
FIRST_VALUE(ENAME) OVER(PARTITION BY DEPTNO ORDER BY SAL DESC ROWS UNBOUNDED PRECEDING) AS DEPT_RICH
FROM EMP; 

 

 

LAST_VALUE 

 

 파티션에 별 윈도에서 가장 나중에 나온 값을 구한다.

--LAST_VALUE 부서별 직원들을연봉이 높은 순으로 정렬  파티션 내 가장 마지막 나온 값
SELECT DEPTNO, ENAME, SAL, 
LAST_VALUE(ENAME) OVER(PARTITION BY DEPTNO ORDER BY SAL DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS 연봉좀올려줘
FROM EMP; 

파티션을 부서별로 나누었으며 해당 부서에서 연봉이 높은 순으로 정렬했기 때문에 마지막에  나온 값은 가장 연봉이 낮은 사원이다. 인자로 ENAME을 넘겼기 때문에 해당 칼럼마다 ENAME을 출력해 연봉이 낮은 사람을 두 번 죽이는 효과가 있다. 연봉 좀 더 줘라..

 

 

 

LAG 함수 

 

파티션 별 윈도에서 이전  몇 번째 행의 값을 가져올 수 있다.

 3개의 인자까지 사용할 수 있는데, 2번째 인자는 몇 번째 앞의 행을 가져올지 결정한다. (default : 1) , 세 번째 인자는 해당 데이터가 없어 null값이 들어오는데 이 null값 대신 출력해줄 값을 지정한다. NVL과 같다.

 

--직원들을 입사일자가 빠른 기준으로 정렬을 하고, 본인보다 입사일자가 한 명 앞선 사원의 급여를 본인의 급여와 함께 출력
SELECT 
DEPTNO , 
JOB,ENAME, 
SAL, 
HIREDATE, 
LAG(SAL,1,0) OVER(ORDER BY HIREDATE DESC) AS PREV_SAL 
FROM EMP;

PREV_SAL에 결과 집합에서 자신보다 한 개 앞선 행의 사원의 연봉값을 행에 추가한 것을 볼 수 있다.

 

LEAD 함수

 

파티션 별 윈도에서 이후 몇 번째 행의 값을 가져올 수 있다. 

LAG함수랑 다를 게 없어서 설명은 생략