728x90
1. Window Function 개요
윈도우 함수랑 행과 행간의 관계를 쉽게 정의하기 위해 만든 함수를 말한다. 분석 함수(ANALYSTIC FUNCTION)이나 순위 함수(RANK FUNCTION)등이 해당된다. 서브쿼리는 사용이 가능하고, 중첩(NEST)해서는 사용이 불가하다.
WINDOW FUNCTION 종류
| 구분 | 함수 | 지원 정보 |
| 그룹 내 순위(RANK) 관련 함수 | RANK, DENSE_RANK, ROW_NUMBER | ANSI/ISO SQL 표준과 Oracle, SQL Sever등 대부분의 DBMS에서 지원 |
| 그룹 내 집계(AGGREGATE) 관련 함수 | SUM ,MAX, MIN, AVG, COUNT | ANSI/ISO SQL 표준과 Oracle, SQL Server 등 대부분의 DBMS 에서 지원 SQL Server 의 경우 집계 함수는 뒤에서 설명할 OVER 절의 내의 ORDER BY 지원 하지 않음 |
| 그룹 내 행 순서 관련 함수 | FIRST_VALUE, LAST_VALUE, LAG, LEAD | ORACLE에서만 지원 |
| 그룹 내 비율 관련 함수 |
CUME_DIST, PERCENT_RANK | ANSI/ISO SQL 표준과 Oracle, MS SQL에서 지원 |
| NTILE | ORACLE, MS SQL에서 지원 | |
| RATIO_TO_REPORT | ORACLE 에서만 지원 |
SYNTAX
SELECT WINDOW_FUNCTION (ARGUMENTS) OVER
( [PARTITION BY 컬럼][ORDER BY 절][WINDOWING 절])
FROM 테이블 명;
-- WINDOW_FUNCTION : 윈도우함수
-- ARGUMENTS : 함수에 따라 0~N개의 인수가 저장
-- OVER : Window 함수 사용에 있어 OVER 문구는 필수 키워드이다.
-- PARTITION BY 절 : 전체 집합을 기준에 대해 순위를 지정할 지 ORDER BY 절을 기술
-- WINDOWING절 : WINDOWS 절은 함수의 대상이 되는 행 기준의 범위를 강력하게 지정할 수 있다.
--ROWS는 물리적인 결과 행의 수, RANGE는 논리적인 값에 의한 범위를, 둘 중 하나 선택 사용
--WINDOWING절은 SQL Server에서는 지원하지 않는다.WINDOWING 절 SYNTAX 상세
ROWS | RANGE BETWEEN UNBOUNDED PRECEDING[CURRENT ROW] AND UNBOUNDED FOLLOWING[CURRENT ROW]
ROWS : 물리적인 행을 의미
RANGE : 논리적인 값으로 범위를 지정 / 같은값을 하나로 본다
WINDOWINDG 기본 설정값이 존재 : UNBOUNDED PRECEDING
UNBOUNDED PRECEDING : 현재행을 기준으로 선행(이전)하는 모든 행들
SELECT empno, ename, sal ,
SUM(sal) OVER (ORDER BY sal ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) c_sum
FROM emp
ORDER BY sal;
UNBOUNDED FOLLOWING : 현재행을 기준으로 이후 모든 행들
n PRECEDING : 현재행을 기준으로 n칸 앞
n FOLLWING : 현재행을 기준으로 n칸 뒤
그룹 내 비율 함수
* 칼럼 값에 대한 백분율 -> RATIO_TO_REPORT
* 행의 순서에 대한 (0부터 1사이 값) 백분율 -> PERCENT_RANK
* 1 / (파티션) 전체 건수로 표현하는 백분율 -> CUME_DIST
RATIO_TO_REPORT 함수
파티션 내 전체 SUM(칼럼) 값에 대한 행별 칼럼 값의 백분율을 소수점으로 구함
* 0 < 백분율 < 1, 개별 RATIO의 합 = 1
SQL Sever 에서는 지원하지 않는 함수
SELECT FIRST_NAME, SALARY,
ROUND(RATIO_TO_REPORT(SALARY) OVER (),2) AS R_R,
SUM(SALARY) Over ( Order by JOB_ID ) AS "Total"
FROM EMPLOYEES e
WHERE JOB_ID='SA_REP' ;
FIRST_NAME |SALARY|R_R |Total |
-----------+------+----+------+
Peter | 10000|0.04|250500|
David | 9500|0.04|250500|
Peter | 9000|0.04|250500|
Christopher| 8000|0.03|250500|
Nanette | 7500|0.03|250500|
Oliver | 7000|0.03|250500|
Janette | 10000|0.04|250500|
Patrick | 9500|0.04|250500|
Allan | 9000|0.04|250500|
Lindsey | 8000|0.03|250500|
Louise | 7500|0.03|250500|
Sarath | 7000|0.03|250500|
Clara | 10500|0.04|250500|
Danielle | 9500|0.04|250500|
Mattea | 7200|0.03|250500|
David | 6800|0.03|250500|
...PERCENT_RANK
파티션 별 윈도우에서 제일 먼저 나오는 것을 0으로, 제일 늦게 나오는 것을 1로 하여 값이 아닌 행의 순서별 백분율을 구함
* 0 < 백분율 < 1
같은 부서 소속 사원들의 집합에서 본인의 급여가 순서상 몇 번째 위치쯤에 있는지 0과 1사이의 값으로 출력
SELECT DEPARTMENT_ID, FIRST_NAME, SALARY,
PERCENT_RANK() OVER ( PARTITION BY DEPARTMENT_ID ORDER BY SALARY DESC ) AS P_R
FROM EMPLOYEES e ;
DEPARTMENT_ID|FIRST_NAME |SALARY|P_R |
-------------+-----------+------+------------------------------------------+
10|Jennifer | 4400| 0|
20|Michael | 13000| 0|
20|Pat | 6000| 1|
30|Den | 11000| 0|
30|Alexander | 3100| 0.2|
30|Shelli | 2900| 0.4|
30|Sigal | 2800| 0.6|
30|Guy | 2600| 0.8|
30|Karen | 2500| 1|
40|Susan | 6500| 0|
50|Adam | 8200| 0|
...CUME_DIST
파티션 별 윈도우에서 전체 건수에 현재 행보다 작거나 같은 건수에 대한 누적 백분율을 구함
* 0 < 백분율 < 1
같은 부서 소속 사원들의 집합에서 본인의 급여가 누적 순서상 몇 번째 위치쯤에 있는지 0과 1사이의 값으로 출력
SELECT DEPARTMENT_ID, FIRST_NAME, SALARY,
CUME_DIST() OVER ( PARTITION BY DEPARTMENT_ID ORDER BY SALARY ) AS C_D
FROM EMPLOYEES e ;
DEPARTMENT_ID|FIRST_NAME |SALARY|C_D |
-------------+-----------+------+------------------------------------------+
10|Jennifer | 4400| 1|
20|Pat | 6000| 0.5|
20|Michael | 13000| 1|
30|Karen | 2500|0.1666666666666666666666666666666666666667|
30|Guy | 2600|0.3333333333333333333333333333333333333333|
30|Sigal | 2800| 0.5|
30|Shelli | 2900|0.6666666666666666666666666666666666666667|
30|Alexander | 3100|0.8333333333333333333333333333333333333333|
30|Den | 11000| 1|
40|Susan | 6500| 1|
...NFILE
파티션 별 전체 건수를 ARGUMENT 값으로 N등분 한 결과 구함
사원들을 연봉이 높은 순으로 정렬 해서 4단계로 나눠 보여주는 예제
SELECT FIRST_NAME, SALARY, NTILE(4) OVER (ORDER BY SALARY DESC ) AS QUAR_TILE
FROM EMPLOYEES e ;
FIRST_NAME |SALARY|QUAR_TILE|
-----------+------+---------+
Steven | 24000| 1|
Neena | 17000| 1|
Lex | 17000| 1|
John | 14000| 1|
Karen | 13500| 1|
Michael | 13000| 1|
Nancy | 12008| 1|
Shelley | 12008| 1|
Alberto | 12000| 1|
Lisa | 11500| 1|
Den | 11000| 1|
Gerald | 11000| 1|
Ellen | 11000| 1|
Eleni | 10500| 1|
Clara | 10500| 1|
Janette | 10000| 1|
Peter | 10000| 1|
Hermann | 10000| 1|
Harrison | 10000| 1|
Tayler | 9600| 1|
Danielle | 9500| 1|
David | 9500| 1|
Patrick | 9500| 1|
Peter | 9000| 1|
Alexander | 9000| 1|
Allan | 9000| 1|
Daniel | 9000| 1|
Alyssa | 8800| 2|
Jonathon | 8600| 2|
Jack | 8400| 2|
William | 8300| 2|
Adam | 8200| 2|
John | 8200| 2|
Matthew | 8000| 2|
Lindsey | 8000| 2|
Christopher| 8000| 2|
Payam | 7900| 2|
Jose Manuel| 7800| 2|
Ismael | 7700| 2|
Louise | 7500| 2|
Nanette | 7500| 2|
William | 7400| 2|
Elizabeth | 7300| 2|
Mattea | 7200| 2|
Oliver | 7000| 2|
Kimberely | 7000| 2|
Sarath | 7000| 2|
Luis | 6900| 2|
David | 6800| 2|
Susan | 6500| 2|
Shanta | 6500| 2|
Sundar | 6400| 2|
Charles | 6200| 2|
Amit | 6200| 2|
Sundita | 6100| 3|
Pat | 6000| 3|
Bruce | 6000| 3|
Kevin | 5800| 3|
Valli | 4800| 3|
David | 4800| 3|
Jennifer | 4400| 3|
Nandita | 4200| 3|
Diana | 4200| 3|
Alexis | 4100| 3|
Sarah | 4000| 3|
Britney | 3900| 3|
Kelly | 3800| 3|
Jennifer | 3600| 3|
Renske | 3600| 3|
Trenna | 3500| 3|
Julia | 3400| 3|
Jason | 3300| 3|
Laura | 3300| 3|
Julia | 3200| 3|
Samuel | 3200| 3|
Winston | 3200| 3|
Stephen | 3200| 3|
Alexander | 3100| 3|
Alana | 3100| 3|
Jean | 3100| 3|
Curtis | 3100| 3|
Kevin | 3000| 4|
Anthony | 3000| 4|
Shelli | 2900| 4|
Timothy | 2900| 4|
Michael | 2900| 4|
Sigal | 2800| 4|
Vance | 2800| 4|
Girard | 2800| 4|
Mozhe | 2800| 4|
John | 2700| 4|
Irene | 2700| 4|
Guy | 2600| 4|
Douglas | 2600| 4|
Donald | 2600| 4|
Randall | 2600| 4|
Karen | 2500| 4|
James | 2500| 4|
Randall | 2500| 4|
Peter | 2500| 4|
Martha | 2500| 4|
Joshua | 2500| 4|
Ki | 2400| 4|
James | 2400| 4|
Hazel | 2200| 4|
Steven | 2200| 4|
TJ | 2100| 4|728x90
반응형
'Oracle > SQL' 카테고리의 다른 글
| [Oracle] EXECUTE IMMEDIATE 문 ( 동적 SQL) (0) | 2024.07.04 |
|---|---|
| [SQL] 계층형 쿼리 ( CONNECT BY ) (0) | 2024.03.05 |
| [SQL] 윈도우 함수(Window Function)의 종류(3) - 행 순서 관련 함수(FIRST_VALUE, LAST_VALUE, LAG, LEAD) (0) | 2023.04.17 |
| [SQL] 윈도우 함수(Window Function)의 종류(2) - 집계 관련 함수(SUM, MAX, MIN, AVG, COUNT) (0) | 2023.04.13 |
| [SQL] 윈도우 함수(Window Function)의 종류(1) - 순위 관련 함수(RANK, DENSE_RANK, ROW_NUMBER) (0) | 2023.04.05 |