행마다 계산한 값을 같이 보여주는 것이 필요한 경우 사용한다.
즉 여러 행 간의 관계 정의 함수라고 할 수 있다.
WINDOW FUNCTION 개요
기존 관계형 데이터베이스는 컬럼과 컬럼간의 연산, 비교, 집계 등은 쉬운 반면, 행과 행간의 관계를 정의하거나 비교, 연산하는 것은 하나의 SQL 문으로 처리하기 어렵다.
절차형 프로그램을 작성하거나, INLINE VIEW 를 이용하여 복잡한 SQL 문을 작성해야 했던 것을 부분적으로라도 행과 행간의 관계를 쉽게 정의하기 위해 사용하는 함수가 윈도우 함수(WINDOW FUNCTION) 이다.
행과 행간의 관계를 쉽게 정의 하기 위해 만든 함수를 이름이다.
분석 함수(ANALYTIC FUNCTION)이나 순위 함수(RANK FUNCTION) 라고도 부른다.
기존 집계 함수, 새로이 WINDOW 함수 전용 존재한다.
서브쿼리 사용 가능, 중첩(NEST)해서 사용 불가하다.
OVER문구가 키워드로 필수 포함된다
WINDOWING함수 2가지 종류(BETWEEN 사용 타입, BETWEEN 미사용 타입)
SQL>>
SELECT WINDOW_FUNCTION (ARGUMENTS)
OVER ( [PARTITION BY 칼럼] [ORDER BY 절] [WINDOWING 절] ) FROM 테이블 명;
순위함수 | 집계함수 | 행순서함수 | 비율함수 |
---|---|---|---|
RANK DENSE_RANK ROW_NUMBER |
SUM, AVG MAX, MIN COUNT |
FIRST_VALUE LAST_VALUE LAG, LEAD |
CUME_DIST PERCENT_RANK NTILE RATIO_TO_REPORT |
이름 그대로 각 행의 순위를 보여주는 함수이며,ORDER BY 를 포함한 QUERY 문에서 특정 컬럼에 대한 순위를 구하는 함수이다.
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 별로 급여가 높은 순서를 같이 출력하라
-결과-
동일 SALARY 에 대해서 같은 순위 부여한다.
ORDER BY SAL DESC 와 PARTITION BY JOB ORDER BY SAL DESC 가 충돌 하여 ORDER BY SAL DESC 기준으로 정렬한다.
DENSE_RANK 함수는 RANK 함수와 다르게 순서 있는 집합에서 각 행의 순위를 구하는 함수이다.
DENSE_RANK() OVER (
PARTITION BY A, B,...
ORDER BY A [ASC | DESC], B,..
)
SELECT JOB, ENAME, SAL,
RANK() OVER ( ORDER BY SAL DESC ) RANK,
DENSE_RANK() OVER ( ORDER BY SAL DESC ) DENSE_RANK
FROM EMP ;
사원데이터에서 급여가 높은 순서와, 동일한 순위를 하나의 등수로 간주한 결과도 같이 출력하라.
-결과-
동일한 값이라도 고유한 순위를 부여한다는 점이 RANK,DENSE_RANK와 다르다.
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 별로 급여가 높은 순서를 같이 출력하라.
SELECT JOB, ENAME, SAL,
RANK( ) OVER (ORDER BY SAL DESC) RANK,
ROW_NUMBER() OVER (ORDER BY SAL DESC) ROW_NUMBER
FROM EMP;
사원데이터에서 급여가 높은 순서의 RANK, ROW_NUMBER 결과를 조회
-결과-
튜플에 관심있는 것이 아니라 몇개의 튜플에 관심이 있는 경우, 집계함수를 사용하며, 집계함수와 일반 애트리뷰트를 같이 출력할 수 없다.
SELECT AVG(Midterm) AS 중간평균 FROM ENROL WHERE Cno = ‘A1997’;
등록테이블에서 과목번호가 A1997인 과목의 중간교사의 평균을 구하라.
SELECT count(distinct sno), avg(final) from A where cno = 'SQLD';
SELECT cno, count(*), avg(final) FROM A GROUP BY cno
등록테이블에서 과목번호가 'SQLD'인 과목의 기말고사의 평균을 구하라.
SELECT e.cno, e.ename , MIN(final), MAX(final)
FROM enrol e, student s
WHERE e.sno = s.sno AND s.year = 2
GROUP BY cno ORDER BY cno DESC
2학년 학생들이 수강하는 과목들의 과목 번호, 과목명 , 기말고사 최대/최소값을 과목번호 순으로 구하여라.
SELECT COUNT(*) AS 학생수 FROM STUDENT;
학생 테이블의 전체 튜플의 개수가 몇개인지 출력
SELECT COUNT(cno) AS 학생수 FROM STUDENT;
중복을 제거하지 않은 cno 의 튜플의 개수 출력
SELECT DEPTNO, ENAME, SAL,
FIRST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC ROWS UNBOUNDED PRECEDING) AS DEPT_RICH
FROM EMP;
부서별 직원을 연봉이 높은 순서대로 정렬하고, 파티션 내에서 가장 먼저 나온 값을 출력
ROWS UNBOUNDED PRECEDING : 현재 행을 기준으로 파티션 내의 첫 번째 행까지의 범위를 지정한다.
FIRST_VALUE 는 다른 함수와 달리 공동 등수를 인정하지 않고 처음 나온 행만을 처리한다.
DEPTNO = 20 파티션의 최고 연봉은 3000 이고, SCOTT 과 FORD 가 대상 ➡️ 이중 하나인 SCOTT 만 처리된다.
명시적인 정렬을 위해서 INLINE VIEW 나, OVER () 내의 ORDER BY 절 에 칼럼을 추가 한다.
❓RANGE UNBOUNDED PRECEDING : 현재 행을 기준으로 파티션 내의 첫 번째 행까지의 범위를 지정한다.
SELECT DEPTNO, ENAME, SAL,
LAST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS DEPT_POOR
FROM EMP;
부서별 직원을 연봉이 높은 순서대로 정렬하고, 파티션 내에서 가장 마지막에 나온 값을 출력
❓ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING: 현재 행을 포함해서 파티션 내의 마지막 행까지의 범위를 지정한다.
LAG 함수를 이용해 파티션별 윈도우에서 이전 몇 번째 행의 값을 가져올 수 있다.
즉 현재 읽혀진 데이터의 이전 값을 알아내는 함수
SELECT ENAME, HIREDATE, JOB, SAL, LAG(SAL) OVER (
ORDER BY HIREDATE ) AS PREV_SAL
FROM EMP
WHERE JOB ='SALESMAN';
직원을 입사일자 기준으로 정렬하고, 본인보다 입사일자가 한 명 앞선 사원의 급여를 본인의 급여와 함께 출력
인수1 : 입력 칼럼
인수2 : 몇 번째 앞의 행 을 가져올지 결정(DEFAULT 1)
인수3 : 파티션의 첫 번째 행의 경우 가져올 데이터가 없어 NULL 값일 경우, 변경할 값 입력 (✅ NVL/ISNULL함수 와 동일)
LEAD 함수를 이용해 파티션별 윈도우에서 이후 몇 번째 행의 값을 가져올 수 있다. 즉 이후 값을 알아내는 함수
SELECT ENAME, HIREDATE, LEAD(HIREDATE) OVER ( ORDER BY HIREDATE ) AS "NEXTHIRED"
FROM EMP ;
직원을 입사일자 기준으로 정렬하고, 바로 다음에 입사한 직원의 입사일자를 출력
LEAD(인수1,인수2,인수3 )
인수1 - 입력 칼럼
인수2 - 몇 번째 후 의 행을 가져올지 결정(DEFAULT 1)
인수3 - 파티션의 마지막 행의 경우 가져올 데이터가 없어 NULL 값일 경우, 변경할 값 입력
행의 순서에 대한 (0~1사이 값) 백분률 ➡️ PERCENT_RANK
1/(파티션) 전체 건수로 표현하는 백분률 ➡️ CUME_DIST
SELECT ENAME, SAL,
ROUND(RATIO_TO_REPORT(SAL) OVER (), 2) AS R_R
FROM EMP
WHERE JOB = 'SALESMAN';
JOB 이 SALESMAN 인 사원을 대상으로 전체 급여에서 본인이 차지하는 비율을 출력
실행 결과에서 전체 값은 1650+1250+1250+1500=5600 이 되고, RATIO_TO_REPORT 함수 연산의 분모로 사용된다.
개별 RATIO 의 전체 합을 구하면 1이 되는 것을 확인할 수 있다. 0.29+0.22+0.22+0.27=1
SELECT DEPTNO, ENAME, SAL,
PERCENT_RANK() OVER ( PARTITION BY DEPTNO ORDER BY SAL DESC ) AS P_R
FROM EMP ;
같은 부서 소속 사원들의 집합에서 본인의 급여가 순서상 몇 번째 위치에 있는지 0과 1 사이의 값으로 출력
DEPTNO 10 은 3건이므로 2개의 구간이 되어, 0과 1 사이를 2개로 나누면
➡️ 0, 0.5, 1
DEPTNO 20 은 5건이므로 4개의 구간이 되어, 0과 1 사이를 4개로 나누면
➡️ 0, 0.25, 0.5, 0.75, 1
DEPTNO 30 은 6건이므로 5개의 구간이 되어, 0과 1 사이를 5개로 나누면
➡️ 0, 0.2, 0.4, 0.6, 0.8, 1
SCOTT-FORD, WARD-MARTIN 은 ORDER BY SAL DESC 구문에 의해 급여가 같으므로 같은 ORDER 로 취급한다.
SELECT DEPTNO, ENAME, SAL,
CUME_DIST() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) AS CUME_DIST
FROM EMP;
같은 부서 소속 사원들의 집합에서 본인의 급여가 누적 순서상 몇 번째 위치에 있는지 0과 1 사이의 값으로 출력
파티션별 전체 건수를 ARGUMENT 값으로 N 등분한 결과 구한다.
SELECT ENAME, SAL,
NTILE(4) OVER (ORDER BY SAL DESC) AS QUAR_TILE
FROM EMP;
전체 사원을 급여가 높은 순서로 정렬하고, 급여를 기준으로 4개의 그룹으로 분류
SELECT WINDOW_FUNCTION (ARGUMENTS)
OVER ( [PARTITION BY 컬럼] [ORDER BY 절] [WINDOWING 절] )
FROM 테이블 명