윈도우(분석) 함수
윈도우(분석) 함수#
분석 함수(analytic functions)라고 흔히 알려져 있는 윈도우 함수(ANSI/ISO SQL 표준은 window functions이란 용어를 사용함)는 행들의 그룹을 기반으로 하여 집계 값을 계산한다.
윈도우 함수(또는 분석 함수)는 쿼리가 반환하는 결과 집합 내에서 여러 개의 행들, 또는 행들의 그룹에 대해 작업을 한다는 점에서 일반적인 집계 함수와 유사하다. 하지만 윈도우 함수가 작업하는 행들의 그룹은 GROUP BY 절이 아니라 OVER 절 아래의 PARTITION BY 및 ROWS/RANGE 하위 절에 의해 정의된다. 또한 이러한 그룹 내에서의 순서도 주 쿼리의 ORDER BY 절이 아니라 OVER 절 아래의 ORDER BY 하위 절에 의해 결정된다.
이 절에서는 PARTITION BY 하위 절로 나뉘어진 그룹을 "파티션"이라 부르고, ROWS/RANGE 하위 절로 결정되는 그룹을 "윈도우"라고 부를 것이다.
Altibase는 버전 6.3.1부터 아래의 윈도우 함수를 지원한다.
-
집계(Aggregate) 윈도우 함수
AVG, COUNT, MAX, MIN, STDDEV, SUM, VARIANCE, GROUP_CONCAT, RATIO_TO_REPORT 등
-
순위(Ranking) 윈도우 함수
RANK, DENSE_RANK, ROW_NUMBER, LAG, LEAD, NTILE 등
-
행 순서 관련 윈도우 함수
FIRST_VALUE, LAST_VALUE, NTH_VALUE 등
집계 윈도우 함수는 한 파티션 내에서 칼럼 값들의 합계 또는 평균을 구하는 계산을 수행한다. Altibase는 "집계 함수" 절에서 기술한 모든 집계 함수를 윈도우 함수로 사용하는 것을 지원한다(CUME_DIST, PERCENT_RANK는 예외). 집계 함수는 일반적으로 그룹 별로 한 개의 결과 행을 반환하지만, 윈도우 함수에서 사용될 때는 결과 값이 행 별로 반환된다.
순위 윈도우 함수는 한 파티션 내에서 각 행의 순위 값을 반환한다. 이 유형의 함수들은 OVER 절 아래의 ORDER BY 하위 절을 필요로 한다.
행 순서 관련 함수는 하나의 정렬된 분할 집합 내에서 첫 번째나 마지막 값, 또는 해당 집합 내에서 이전 또는 다음 값을 검색한다.
집계 함수를 제외한 나머지 함수는 아래의 절에서 함수 별로 설명한다.
구문#
window_function ::=#
window_specification ::=#
window_partition_clause ::=#
window_order_clause ::=#
window_frame_clause ::=#
설명#
윈도우 함수는 SELECT 목록 또는 ORDER BY 절에만 올 수 있다.
윈도우 함수가 포함된 질의가 처리되는 단계는 다음과 같다.
-
1단계: 윈도우 함수와 ORDER BY 절 (만약 존재하면)을 제외한 질의 처리
-
2단계: 윈도우 함수에 PARTITION BY 하위절이 포함된 경우, 1단계의 질의 결과를 윈도우 함수가 적용될 파티션으로 분리
-
3단계: ORDER BY 하위절이 있는 경우 파티션 별로 정렬
-
4단계: ROWS/RANGE 절이 있는 경우 윈도우 프레임 결정
-
5단계: 윈도우 함수 계산 수행
-
6단계: ORDER BY 절이 있는 경우 처리
window_function#
윈도우 함수로 사용할 함수의 이름을 명시한다.
arg_expr#
윈도우 함수를 위한 인자로 사용될 수식을 명시한다.
IGNORE NULLS#
IGNORE NULLS 구문을 명시하면 널(null)이 아닌 값이 반환된다.
OVER window_specificatoin#
OVER는 이 함수가 쿼리의 결과 집합에 대해 작업할 것을 질의 처리기에게 알려주는 키워드이다.
이 절을 포함하는 윈도우 함수를 select 리스트 또는 ORDER BY 절에 명시할 수 있다.
쿼리의 select 리스트에 사용된 모든 윈도우 함수 뒤에는 OVER 절이 따라온다. OVER 절은 윈도우 함수를 적용하기 위해 결과 집합을 분할하는 방법과 정렬하는 방법을 결정한다. OVER 키워드 다음에는 분할 및 정렬 정책을 지정하는 아래의 세 하위 절이 따라올 수 있다.
window_partition_clause#
이 절은 쿼리 결과 집합을 그룹(파티션)으로 묶을 기준이 되는 한 개 이상의 칼럼 또는 표현식을 명시한다. 모든 윈도우 함수가 이 절을 지원하지만, 사용은 선택적이다. 이 절을 생략하면, 윈도우 함수는 전체 결과 집합을 하나의 파티션으로 처리한다.
집계 관련 함수가 ORDER BY 하위 절 없이 이 절을 사용한다면 함수 인자에 DISTINCT 키워드를 사용할 수 있다.
window_order_clause#
이 절은 파티션 내에서 데이터 정렬 기준이 되는 한 개 이상의 칼럼 또는 표현식을 명시한다. 집계 함수는 이 절을 선택적으로 사용할 수 있지만, 순위 함수의 경우 이 절을 반드시 사용해야 한다.
집계 관련 함수가, 이 절을 사용하면 함수 인자에 DISTINCT 키워드를 사용할 수 없다.
NULLS FIRST 또는 NULLS LAST 키워드를 사용해서 NULL을 정렬 순서에서 맨 처음 또는 맨 마지막에 위치시킬 수 있다.
window_frame_clause#
이 절은 행을 기준으로 함수의 대상이 되는 윈도우(물리적 또는 논리적인 행의 집합)를 정의하며, 함수는 윈도우 내의 모든 행에 적용된다. 윈도우는 쿼리 결과 집합 또는 파티션을 위에서 아래로 통과해서 움직인다.
ROWS는 행의 수를 기준으로 윈도우를 명시하고, RANGE는 행의 값을 기준으로 윈도우를 명시한다. 아래의 예를 참고하라:
-
RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING
파티션 내에서 (현재 행의 값 – 50)에서 (현재 행의 값 + 150)까지의 값에 해당하는 모든 행이 윈도우로 정의된다. -
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
현재 행을 기준으로 파티션 내에서 앞, 뒤로 한 행씩 포함한 행이 윈도우로 정의된다. -
RANGE UNBOUNDED PRECEDING
현재 행을 기준으로 파티션 내의 첫 번째 행까지 윈도우로 정의된다. -
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
현재 행을 기준으로 파티션 내의 첫 번째 행부터 마지막 행까지 윈도우로 정의된다.
순위 관련 함수는 이 절을 사용할 수 없지만, 집계 관련 함수는 선택적으로 사용할 수 있다. 행 순서 관련 함수는 이 절을 선택적으로 사용할 수 있지만, 이 절을 사용하지 않는다면 함수 사용의 의미가 없을 것이다.
이 절을 지정하려면 ORDER BY 하위 절이 반드시 있어야 한다. 이 절이 포함된 경우 윈도우 함수 인자에 DISTINCT 키워드를 사용할 수 없다.
또한 ROWS/RANGE 절을 지원하는 윈도우 함수를 사용하면서 ROWS/RANGE 절을 지정하지 않는다면, 기본값은 'RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW'이다.
value에 표현식은 올 수 없고 양의 정수만 올 수 있다.
윈도우 함수의 ORDER BY 하위 절에 DATE 타입의 표현식이 사용된 경우, RANGE 절의 value 위치에 아래의 형식을 사용할 수 있다.
INTERVAL n [YEAR | MONTH | DAY | HOUR | MINUTE | SECOND]
DENSE_RANK#
구문#
DENSE_RANK () OVER {...}
설명#
DENSE_RANK 함수는 RANK함수처럼 결과 집합 또는 파티션의 특정 멤버를 기준으로 순위를 매긴다. 그러나 중복 순위 발생 후에 건너뛰지 않고 다음 순위가 매겨진다. 반환 값의 타입은 BIGINT이다.
FIRST_VALUE#
구문#
FIRST_VALUE (expr) OVER {...}
설명#
파티션 또는 윈도우 내에서 첫 번째 행의 값을 구하는 함수이다.
제약 사항#
-
함수 인자에 DISTINCT 키워드를 사용할 수 없다.
-
OVER 절이 반드시 존재해야 한다.
FIRST_VALUE_IGNORE_NULLS#
구문#
FIRST_VALUE_IGNORE_NULLS (expr) OVER {...}
설명#
파티션 또는 윈도우 내에서 널 값을 제외한 첫 번째 행의 값을 구하는 함수이다.
제약 사항#
FIRST_VALUE 함수와 동일하다.
LAG#
구문#
LAG (expr [, offset [, default_value]]) OVER {...}
설명#
정렬된 각 파티션 내에서 현재 행을 기준으로 이전의 offset 번째 행의 값을 구하는 함수이다. offset에는 양의 정수만 올 수 있고, 생략하면 기본값은 1이다. 파티션 내에서 지정한 offset을 넘어서는 행에 대해서는 default_value가 적용된다. default_value를 생략하면 기본값은 NULL이다.
제약 사항#
-
함수 인자에 DISTINCT 키워드를 사용할 수 없다.
-
OVER 절 내에 ORDER BY 하위절이 반드시 존재해야 한다.
-
offset에는 양의 정수만 올 수 있다.
LAG_IGNORE_NULLS#
구문#
LAG_IGNORE_NULLS (expr [, offset [, default_value]]) OVER {...}
설명#
정렬된 각 파티션 내에서 현재 행을 기준으로 이전의 offset 번째 행부터 NULL이 아닌 첫 번째 값을 구하는 함수이다. offset에는 양의 정수만 올 수 있고, 생략하면 기본값은 1이다. 파티션 내에서 지정한 offset을 넘어서는 행에 대해서는 default_value가 적용된다. default_value를 생략하면 기본값은 NULL이다.
제약 사항#
LAG 함수와 동일하다.
LAST_VALUE#
구문#
LAST_VALUE (expr) OVER {...}
설명#
파티션 또는 윈도우 내에서 마지막 번째 행의 값을 구하는 함수이다.
제약 사항#
-
함수 인자에 DISTINCT 키워드를 사용할 수 없다.
-
OVER 절이 반드시 존재해야 한다.
LAST_VALUE_IGNORE_NULLS#
구문#
LAST_VALUE_IGNORE_NULLS (expr) OVER {...}
설명#
파티션 또는 윈도우 내에서 널 값을 제외한 마지막 번째 행의 값을 구하는 함수이다.
제약 사항#
LAST_VALUE 함수와 동일하다.
LEAD#
구문#
LEAD (expr [, offset [, default_value]]) OVER {...}
설명#
정렬된 각 파티션 내에서 현재 행을 기준으로 이후의 offset 번째 행의 값을 구하는 함수이다. offset에는 양의 정수만 올 수 있고, 생략하면 기본값은 1이다. 파티션 내에서 지정한 offset을 넘어서는 행에 대해서는 default_value가 적용된다. default_value를 생략하면 기본값은 NULL이다.
제약 사항#
-
함수 인자에 DISTINCT 키워드를 사용할 수 없다.
-
OVER 절 내에 ORDER BY 하위절이 반드시 존재해야 한다.
-
offset에는 양의 정수 값의 상수만 올 수 있다.
LEAD_IGNORE_NULLS#
구문#
LEAD_IGNORE_NULLS (expr [, offset [, default_value]]) OVER {...}
설명#
정렬된 각 파티션 내에서 현재 행을 기준으로 이후의 offset 번째부터 NULL이 아닌 첫 번째 행의 값을 구하는 함수이다. offset에는 양의 정수만 올 수 있고, 생략하면 기본값은 1이다. 파티션 내에서 지정한 offset을 넘어서는 행에 대해서는 default_value가 적용된다. default_value를 생략하면 기본값은 NULL이다.
제약 사항#
LEAD 함수와 동일하다.
LISTAGG#
구문#
설명#
입력된 expr에 해당하는 칼럼 값을 order_by_clause에 지정한 그룹내의 순서대로 하나의 칼럼처럼 값을 반환하는 함수이다. arg는 반환 값의 문자열을 구분하는 구분자이며, 지정하지 않으면 문자열에 구분자가 삽입되지 않는다.
LISTAGG 함수는 집계 함수와 분석 함수로 사용할 수 있다.
예제#
<질의> 다음은 집계 함수에서 LISTAGG를 사용하는 예제이다. employees 테이블에서 dno로 그룹화하여 emp_job의 순서대로 e_lastname을 출력할 때, 이름 사이에 ';'을 삽입하여 출력하라.
SELECT dno
, CAST(LISTAGG(e_lastname,';') WITHIN GROUP( ORDER BY emp_job) AS VARCHAR(100)) "emp_job"
FROM employees
GROUP BY dno;
DNO emp_job
-------------------------------------------------------------------------------------------------
1001 Kobain ;Chen
1002 Jones ;Momoi
1003 Miura ;Bae ;Davenport ;Liu
2001 Fubuki
3001 Foster
3002 Moon ;Ghorbani
4001 Wang ;Huxley ;Diaz
4002 Fleischer ;Blake ;Hammond ;Marquez
Davenport
9 rows selected.
<질의> 다음은 분석 함수에서 LISTAGG를 사용하는 예제이다. employees 테이블에서 emp_job 순서대로 e_lastname 출력할 때, 이름 사이에 ';'을 삽입하여 출력하라.
SELECT dno
, CAST(LISTAGG(e_lastname,';') WITHIN GROUP(ORDER BY emp_job) OVER ( PARTITION BY dno ) AS VARCHAR(100)) "emp_job"
FROM employees;
DNO emp_job
-------------------------------------------------------------------------------------------------
1001 Kobain ;Chen
1001 Kobain ;Chen
1002 Jones ;Momoi
1002 Jones ;Momoi
1003 Miura ;Bae ;Davenport ;Liu
1003 Miura ;Bae ;Davenport ;Liu
1003 Miura ;Bae ;Davenport ;Liu
1003 Miura ;Bae ;Davenport ;Liu
2001 Fubuki
3001 Foster
3002 Moon ;Ghorbani
3002 Moon ;Ghorbani
4001 Wang ;Huxley ;Diaz
4001 Wang ;Huxley ;Diaz
4001 Wang ;Huxley ;Diaz
4002 Fleischer ;Blake ;Hammond ;Marquez
4002 Fleischer ;Blake ;Hammond ;Marquez
4002 Fleischer ;Blake ;Hammond ;Marquez
4002 Fleischer ;Blake ;Hammond ;Marquez
Davenport
20 rows selected.
NTH_VALUE#
구문#
NTH_VALUE (expr, offset) OVER {...}
설명#
파티션 또는 윈도우 내에서 offset 번째 행의 값을 구하는 함수이다.
제약 사항#
-
함수 인자에 DISTINCT 키워드를 사용할 수 없다.
-
OVER 절이 반드시 존재해야 한다.
NTH_VALUE_IGNORE_NULLS#
구문#
NTH_VALUE_IGNORE_NULLS (expr, offset) OVER {...}
설명#
파티션 또는 윈도우 내에서 널 값을 제외한 offset 번째 행의 값을 구하는 함수이다.
제약 사항#
NTH_VALUE 함수와 동일하다.
NTILE#
구문#
설명#
NTILE함수는 정렬된 데이터의 특정 멤버를 기준으로 입력된 expr의 숫자만큼 균등하게 배분하여 그룹의 순번을 정한다. 반환 값의 타입은 BIGINT이다.
예제#
<질의> 정렬된 급여를 기준으로 3개의 그룹으로 균등하게 나누어서 그룹의 순번을 확인한다.
SELECT e_firstname
, salary
, NTILE(3) OVER(ORDER BY salary)
FROM employees;
E_FIRSTNAME SALARY NTILE(3)OVER(ORDERBYSALARY)
------------------------------------------------------------------
Gottlieb 500 1
Mitch 980 1
Jason 1000 1
Curtis 1200 1
Takahiro 1400 1
Susan 1500 1
Ryu 1700 1
Aaron 1800 2
Alvar 1800 2
Sandra 1890 2
John 1900 2
Ken 2000 2
Yuu 2003 2
Wei-Wei 2300 2
Farhad 2500 3
Zhen 2750 3
Elizabeth 4000 3
Chan-seung 3
Xiong 3
William 3
20 rows selected.
PERCENTILE_CONT#
구문#
설명#
이 함수는 연속 분포 모델을 가정한 역분포 함수이다. 이 함수는 연속 분포 모델에서 백분율의 값을 가지고 정렬한 후 백분율에 해당하는 값을 반환한다. 이 함수는 FLOAT 타입의 값을 반환하며, NULL 값은 무시된다.
expr1에는 0과 1사이의 상수 값을 지정하며, expr2는 숫자 타입을 지정할 수 있다.
PERCENTILE_CONT 함수는 집계 함수와 분석 함수로 사용할 수 있다.
예제#
<질의> 다음은 집계 함수에서 PERCENTILE_CONT를 사용하는 예제이다. employees 테이블에서 dno 내에서 중간 급여를 출력하라.
SELECT dno
, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary ASC) "median asc cont"
, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary DESC) "median desc cont"
FROM employees
GROUP BY dno;
DNO median asc cont median desc cont
-------------------------------------------------
1001 2150 2150
1002 1340 1340
1003 2376.5 2376.5
2001 1400 1400
3001 1800 1800
3002 2500 2500
4001 1550 1550
4002 1800 1800
1500 1500
9 rows selected.
<질의> 다음은 분석 함수에서 PERCENTILE_CONT를 사용하는 예제이다.
SELECT dno
, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary ASC) OVER ( PARTITION BY dno ) "median asc cont"
, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary DESC) OVER ( PARTITION BY dno ) "median desc cont"
FROM employees;
DNO median asc cont median desc cont
-------------------------------------------------
1001 2150 2150
1001 2150 2150
1002 1340 1340
1002 1340 1340
1003 2376.5 2376.5
1003 2376.5 2376.5
1003 2376.5 2376.5
1003 2376.5 2376.5
2001 1400 1400
3001 1800 1800
3002 2500 2500
3002 2500 2500
4001 1550 1550
4001 1550 1550
4001 1550 1550
4002 1800 1800
4002 1800 1800
4002 1800 1800
4002 1800 1800
1500 1500
20 rows selected.
PERCENTILE_DISC#
구문#
설명#
이 함수는 이산 분포 모델을 가정한 역분포 함수이다. 이 함수는 불연속 분포 모델에서 백분율의 값을 가지고 정렬한 후 백분율에 해당하는 값을 반환한다. 이 함수는 FLOAT 타입의 값을 반환하며, NULL 값은 무시된다.
expr1에는 0과 1사이의 상수 값을 지정하며, expr2는 숫자 타입을 지정할 수 있다.
PERCENTILE_DISC 함수는 집계 함수와 분석 함수로 사용할 수 있다.
예제#
<질의> 다음은 집계 함수에서 PERCENTILE_DISC를 사용하는 예제이다. employees 테이블에서 dno 내에서 중간 급여를 출력하라. PERCENTILE_CONT가 짝수 개의 그룹내에서 중앙 값의 평균을 구하는 반면, PERCENTILE_DISC는 중앙 값 사이의 첫번 째 값을 출력한다.
SELECT dno
, PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY salary ASC) "median asc cont"
, PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY salary DESC) "median desc cont"
FROM employees
GROUP BY dno;
DNO median asc cont median desc cont
-------------------------------------------------
1001 2000 2300
1002 980 1700
1003 2003 2750
2001 1400 1400
3001 1800 1800
3002 2500 2500
4001 1200 1900
4002 1800 1800
1500 1500
9 rows selected.
<질의> 다음은 분석 함수에서 PERCENTILE_DISC를 사용하는 예제이다.
SELECT dno
, PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY salary ASC) OVER ( PARTITION BY DNO ) "median asc cont"
, PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY salary DESC) OVER ( PARTITION BY dno ) "median desc cont"
FROM employees;
DNO median asc cont median desc cont
-------------------------------------------------
1001 2000 2300
1001 2000 2300
1002 980 1700
1002 980 1700
1003 2003 2750
1003 2003 2750
1003 2003 2750
1003 2003 2750
2001 1400 1400
3001 1800 1800
3002 2500 2500
3002 2500 2500
4001 1200 1900
4001 1200 1900
4001 1200 1900
4002 1800 1800
4002 1800 1800
4002 1800 1800
4002 1800 1800
1500 1500
20 rows selected.
RANK#
구문#
설명#
RANK 함수는 결과 집합 (또는 파티션)의 특정 멤버를 기준으로 순위를 매긴다. 같은 값에는 동일한 순위가 매겨지고, 그만큼 건너뛰어 다음 순위가 매겨진다. 반환 값의 타입은 BIGINT이다. RANK함수는 집계 함수와 분석 함수로 사용할 수 있다.
주의 : RANK의 인자 개수와 WITHIN GROUP 절의 인자 개수는 일치해야 하며, 인자의 타입은 자동으로 호환되지 않는다. RANK인자의 데이터 타입은 제한되지 않으나 상수 사용을 권장한다.
예제#
<질의> 부서번호(DNO)가1003이고 급여가(SALARY)가 1001인 직원의 순위를 출력하라.
SELECT dno
, salary
FROM employees
ORDER BY 1, 2;
DNO SALARY
---------------------------
1001 2000
1001 2300
1002 980
1002 1700
1003 1000
1003 2003
1003 2750
1003 4000
2001 1400
3001 1800
3002 2500
3002
4001 1200
4001 1900
4001
4002 500
4002 1800
4002 1890
4002
1500
20 rows selected.
SELECT RANK(1003, 1001) WITHIN GROUP (ORDER BY dno, salary )
FROM employees ;
RNK
-----------------------
6
1 row selected.
RATIO_TO_REPORT#
구문#
설명#
RATIO_TO_REPORT 함수는 파티션의 특정 멤버를 기준으로 입력 표현식이 차지하는 비율을 구한다. 입력 표현 식에 null을 입력하면 null이 반환된다. window_partition_clause 구문을 생략하면 반환되는 모든 열의 값을 기준으로 비율이 계산된다.
예제#
<질의> 사원들의 급여가 각각의 부서 내에서 차지하는 비율을 확인한다.
SELECT e_firstname
, dno
, salary
, RATIO_TO_REPORT(salary) OVER (PARTITION BY dno) AS result
FROM employees LIMIT 9;
E_FIRSTNAME DNO SALARY RESULT
---------------------------------------------------------------
Ken 1001 2000 0.465116279
Wei-Wei 1001 2300 0.534883721
Ryu 1002 1700 0.634328358
Mitch 1002 980 0.365671642
Elizabeth 1003 4000 0.410130216
Zhen 1003 2750 0.281964524
Yuu 1003 2003 0.205372706
Jason 1003 1000 0.102532554
Takahiro 2001 1400 1
9 rows selected.
ROW_NUMBER#
구문#
ROW_NUMBER () OVER {...}
설명#
ROW_NUMBER 함수는 결과 집합 또는 파티션의 특정 멤버를 기준으로 연속적인 유일한 수를 부여한다. 1부터 시작하여 ORDER BY 식에 명시된 순서대로 부여된다.
결과 집합에 같은 값이 존재하면, 중복 값에 부여되는 row number 의 순서는 보장되지 않는다. 부여되는 row number의 순서를 보장할 필요가 있다면, 다른 칼럼을 ORDER BY 절에 지정하라. 반환 값의 타입은 BIGINT이다.
예제#
<질의> 각 부서별로 급여가 낮은 순서대로 출력하라.
SELECT e_lastname
, dno
, salary
, RANK() OVER (PARTITION BY dno ORDER BY salary DESC) rank
FROM employees;
E_LASTNAME DNO SALARY RANK
--------------------------------------------------------------
Chen 1001 2300 1
Kobain 1001 2000 2
Momoi 1002 1700 1
Jones 1002 980 2
Bae 1003 4000 1
Liu 1003 2750 2
Miura 1003 2003 3
Davenport 1003 1000 4
Fubuki 2001 1400 1
Foster 3001 1800 1
Ghorbani 3002 2500 1
Moon 3002 2
Huxley 4001 1900 1
Diaz 4001 1200 2
Wang 4001 3
Hammond 4002 1890 1
Marquez 4002 1800 2
Fleischer 4002 500 3
Blake 4002 4
Davenport 1500 1
20 rows selected.
<질의> 다음 예제는 RANK, DENSE_RANK, 및 ROW_NUMBER 함수의 결과가 다름을 보여준다.
SELECT salary
, RANK() OVER (ORDER BY salary DESC) rank
, DENSE_RANK() OVER (ORDER BY salary DESC) dense_rank
, ROW_NUMBER() OVER (ORDER BY salary DESC) row_number
FROM employees;
SALARY RANK DENSE_RANK ROW_NUMBER
---------------------------------------------------------------------------
4000 1 1 1
2750 2 2 2
2500 3 3 3
2300 4 4 4
2003 5 5 5
2000 6 6 6
1900 7 7 7
1890 8 8 8
1800 9 9 9
1800 9 9 10
1700 11 10 11
1500 12 11 12
1400 13 12 13
1200 14 13 14
1000 15 14 15
980 16 15 16
500 17 16 17
18 17 18
18 17 19
18 17 20
20 rows selected.
<질의> 부서별 최고 급여에 대한 사원 각자의 급여 비율을 모든 사원에 대하여 구하라.
SELECT e_lastname
, dno
, salary
, ROUND(salary/MAX(salary) OVER (PARTITION BY dno)*100) rel_sal
FROM
E_LASTNAME DNO SALARY REL_SAL
---------------------------------------------------------------
Kobain 1001 2000 87
Chen 1001 2300 100
.
.
.
Diaz 4001 1200 63
Fleischer 4002 500 26
Marquez 4002 1800 95
Blake 4002
Hammond 4002 1890 100
Davenport 1500 100
20 rows selected.
<질의> 아래의 예제는 FIRST_VALUE, LAST_VALUE, NTH_VALUE함수를 사용하여 성별로 최저 급여, 최고 급여 및 세 번째로 적은 급여를 구한다.
SELECT sex
, salary
, FIRST_VALUE( salary ) OVER ( PARTITION BY sex ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) f_value
, LAST_VALUE( salary ) OVER ( PARTITION BY sex ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) l_value
, NTH_VALUE( salary, 3 ) OVER ( PARTITION BY sex ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) n_value
FROM employees;
SEX SALARY F_VALUE L_VALUE N_VALUE
------------------------------------------------------------------
F 1500 1500 4000 2300
F 1890 1500 4000 2300
F 2300 1500 4000 2300
F 4000 1500 4000 2300
M 500 500 1000
M 980 500 1000
M 1000 500 1000
M 1200 500 1000
M 1400 500 1000
M 1700 500 1000
M 1800 500 1000
M 1800 500 1000
M 1900 500 1000
M 2000 500 1000
M 2003 500 1000
M 2500 500 1000
M 2750 500 1000
M 500 1000
M 500 1000
M 500 1000
20 rows selected.
<질의> 아래는 LAG, LEAD 함수를 사용한 예제이다.
SELECT salary
, RANK() OVER (ORDER BY salary DESC) rank
, DENSE_RANK() OVER (ORDER BY salary DESC) dense_rank
, ROW_NUMBER() OVER (ORDER BY salary DESC) row_number
, LAG( salary ) OVER (ORDER BY salary DESC) lag
, LEAD( salary ) OVER (ORDER BY salary DESC) lead
FROM employees;
SALARY RANK DENSE_RANK ROW_NUMBER LAG LEAD
----------------------------------------------------------------------------------------------------------
4000 1 1 1 2750
2750 2 2 2 4000 2500
2500 3 3 3 2750 2300
2300 4 4 4 2500 2003
2003 5 5 5 2300 2000
2000 6 6 6 2003 1900
1900 7 7 7 2000 1890
1890 8 8 8 1900 1800
1800 9 9 9 1890 1800
1800 9 9 10 1800 1700
1700 11 10 11 1800 1500
1500 12 11 12 1700 1400
1400 13 12 13 1500 1200
1200 14 13 14 1400 1000
1000 15 14 15 1200 980
980 16 15 16 1000 500
500 17 16 17 980
18 17 18 500
18 17 19
18 17 20
20 rows selected.