티스토리 뷰
반응형
🚀 들어가며...
- 실무에서 많이 쓰이는 윈도우함수에 대해 정리하고자 하여 이번 포스팅을 준비하게 되었습니다!
윈도우함수를 적재적소에 쓸 줄 아는 능력을 갖춘다면 백개발자로써 한걸음 전진하는 계기가 될 것입니다. - 이후에도 공부하면서 새로운 윈도우함수를 알게된다면 글에 추가할 예정입니다.
📑 내용
1. 윈도우함수란?
사전적 정의를 이야기 하자면, 행과 행간의 관계를 쉽게 정의하기 위해 만든 함수가 WINDOW FUNCTION 입니다.
윈도우 함수를 사용하면 사용자가 분석 비즈니스 쿼리를 보다 효율적으로 생성할 수 있습니다. 창 함수는 결과 집합의 파티션, 즉 "창"에서 실행되어 해당 창에 속하는 모든 행에 대한 값을 반환합니다. 이와는 반대로 창이 없는 함수는 결과 집합의 모든 행에 대해 계산을 실행합니다. 그 밖에도 결과 행을 집계하는 그룹 함수와 달리 창 함수에서는 테이블 표현식의 모든 행이 그대로 유지됩니다.
반환 값은 해당 창에 속한 행 집합의 값을 사용하여 계산됩니다. 창은 테이블의 각 행마다 추가 속성을 계산하는 데 사용되는 행 집합을 정의합니다. 창은 창 명세(OVER 절)를 사용하여 정의되며, 다음과 같이 세 가지 주요 개념을 근거로 합니다.
- 창 파티션 - 행 그룹을 형성합니다(PARTITION 절).
- 창 순서 지정 - 각 파티션의 행 순서 또는 시퀀스를 정의합니다(ORDER BY 절).
- 창 프레임 - 행 집합을 제한하기 위해 각 행마다 정의됩니다(ROWS 명세).
창 함수는 최종 ORDER BY 절을 제외하고 쿼리에서 실행되는 마지막 연산 집합입니다. 창 함수를 처리할 때는 그 전에 모든 조인을 비롯한 WHERE, GROUP BY 및 HAVING 절까지 모두 완료됩니다. 따라서 창 함수는 선택 목록 또는 ORDER BY 절에만 나타날 수 있습니다. 다른 프레임 절이 있는 단일 쿼리 내에서 여러 윈도우 함수를 사용할 수 있습니다. CASE 등의 다른 스칼라 표현식에서 윈도우 함수를 사용할 수도 있습니다.
윈도우 함수의 표준 구문은 다음과 같습니다.
function (expression) OVER (
[ PARTITION BY expr_list ]
[ ORDER BY order_list [ frame_clause ] ] )윈도우 함수의 ORDER BY 절이 데이터의 전체 순서를 고유하게 지정하지 않으면 행의 순서는 비확정적입니다. 다시 말해 ORDER BY 표현식에서 중복 값이 산출되면(부분 순서 지정) 여러 차례 실행할 때마다 해당 행의 반환 순서가 달라질 수 있습니다. 이 경우 윈도 함수 역시 예상하지 못하거나 일관적이지 못한 결과를 반환하게 됩니다.
2. WINDOW FUNCTION 종류
| 구분 | 종류 | 종류 |
| 순위(RANK) 관련 | RANK, DENSE_RANK, ROW_NUMBER | 대부분 지원 |
| 집계(AGGREGATE) 관련 | SUM, MAX, MIN, AVG, COUNT | SQL Server 경우 Over절 내 Orderby 지원 X |
| 순서 관련 함수 | FIRST_VALUE, LAST_VALUE, LAG, LEAD | ORACLE, Postgresql 만 지원 |
| 그룹 내 비율 관련 함수 | CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT | PERCENT_RANK 함수는 ANSI/ISO SQL 표준과 Oracle DBMS, Postgresql에서 지원하고 있으며, NTILE 함수는 ANSI/ISO SQL 표준에는 없지만, Oracle, Postgresql, SQL Server에서 지원하고 있다. RATIO_TO_REPORT 함수는 Oracle, Postgresql에서만 지원되는 함수(현업에서 유용). |
| 선형분석을 포함한 통계분석 함수 | 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_AVGX, REGR_AVGY, REGR_SXX, REGR_SYY, REGR_SXY | 특화되어있으므로 생략 |
3. 윈도우함수 종류
그룹 내 순위함수
3.1 RANK 함수
- RANK 함수는 ORDER BY를 포함한 QUERY 문에서 특정 항목(칼럼)에 대한 순위를 구하는 함수입니다.
- 이때 특정 범위(PARTITION) 내에서 순위를 구할 수도 있고 전체 데이터에 대한 순위를 구할 수도 있습니다. 또한 동일한 값에 대해서는 동일한 순위를 부여하게 됩니다.
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 ENAME SAL ALL_RANK JOB_RANK
--------- ---------- ---------- ---------- ----------
PRESIDENT KING 5000 1 1
ANALYST FORD 3000 2 1
ANALYST SCOTT 3000 2 1
MANAGER JONES 2975 4 1
MANAGER BLAKE 2850 5 2
MANAGER CLARK 2450 6 3
SALESMAN ALLEN 1600 7 1
SALESMAN TURNER 1500 8 2
CLERK MILLER 1300 9 1
SALESMAN WARD 1250 10 3
SALESMAN MARTIN 1250 10 3
CLERK ADAMS 1100 12 2
CLERK JAMES 950 13 3
CLERK SMITH 800 14 4
14 rows selected.
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 364 | 5 (40)| 00:00:01 |
| 1 | WINDOW SORT | | 14 | 364 | 5 (40)| 00:00:01 |
| 2 | WINDOW SORT | | 14 | 364 | 5 (40)| 00:00:01 |
| 3 | TABLE ACCESS STORAGE FULL| EMP | 14 | 364 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------
SELECT JOB, ENAME, SAL,
RANK() OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK
FROM EMP;
JOB ENAME SAL JOB_RANK
--------- ---------- ---------- ----------
ANALYST FORD 3000 1
ANALYST SCOTT 3000 1
CLERK MILLER 1300 1
CLERK ADAMS 1100 2
CLERK JAMES 950 3
CLERK SMITH 800 4
MANAGER JONES 2975 1
MANAGER BLAKE 2850 2
MANAGER CLARK 2450 3
PRESIDENT KING 5000 1
SALESMAN ALLEN 1600 1
SALESMAN TURNER 1500 2
SALESMAN MARTIN 1250 3
SALESMAN WARD 1250 3
14 rows selected.
3.2 DENSE_RANK 함수
- DENSE_RANK 창 함수는 OVER 절의 ORDER BY 표현식을 기준으로 값 그룹에 속한 값의 순위를 결정합니다. PARTITION BY 절(옵션)이 존재하면 각 행 그룹의 순위가 재설정됩니다. 순위 기준 값이 같은 행은 순위도 동일하게 결정됩니다.
- DENSE_RANK 함수는 한 가지 측면에서 RANK와 다릅니다. 2개 이상의 행에서 순위가 동일하면 순위 값의 순서에서도 빈 자리가 없습니다. 예를 들어 두 행의 순위가 1로 결정되면 다음 순위는 2입니다.
SELECT JOB, ENAME, SAL
, RANK( ) OVER (ORDER BY SAL DESC) RANK
, DENSE_RANK( ) OVER (ORDER BY SAL DESC) DENSE_RANK
FROM EMP;
JOB ENAME SAL RANK DENSE_RANK
--------- ---------- ---------- ---------- ----------
PRESIDENT KING 5000 1 1
ANALYST FORD 3000 2 2
ANALYST SCOTT 3000 2 2
MANAGER JONES 2975 4 3
MANAGER BLAKE 2850 5 4
MANAGER CLARK 2450 6 5
SALESMAN ALLEN 1600 7 6
SALESMAN TURNER 1500 8 7
CLERK MILLER 1300 9 8
SALESMAN WARD 1250 10 9
SALESMAN MARTIN 1250 10 9
CLERK ADAMS 1100 12 10
CLERK JAMES 950 13 11
CLERK SMITH 800 14 12
14 rows selected.
3.3 ROW_NUMBER 함수
- ROW_NUMBER 함수는 RANK나 DENSE_RANK 함수가 동일한 값에 대해서는 동일한 순위를 부여하는데 반해, 동일한 값이라도 고유한 순위를 부여합니다.
SELECT JOB, ENAME, SAL
, RANK( ) OVER (ORDER BY SAL DESC) RANK
, ROW_NUMBER() OVER (ORDER BY SAL DESC) ROW_NUMBER
FROM EMP;
JOB ENAME SAL RANK ROW_NUMBER
--------- ---------- ---------- ---------- ----------
PRESIDENT KING 5000 1 1
ANALYST FORD 3000 2 2
ANALYST SCOTT 3000 2 3
MANAGER JONES 2975 4 4
MANAGER BLAKE 2850 5 5
MANAGER CLARK 2450 6 6
SALESMAN ALLEN 1600 7 7
SALESMAN TURNER 1500 8 8
CLERK MILLER 1300 9 9
SALESMAN WARD 1250 10 10
SALESMAN MARTIN 1250 10 11
CLERK ADAMS 1100 12 12
CLERK JAMES 950 13 13
CLERK SMITH 800 14 14
14 rows selected.
일반 집계 함수
3.4 SUM 함수
- SUM 함수를 이용해 파티션별 윈도우의 합을 구할 수 있습니다.
SELECT MGR, ENAME, SAL
, SUM(SAL) OVER (PARTITION BY MGR) MGR_SUM
FROM EMP;
MGR ENAME SAL MGR_SUM
---------- ---------- ---------- ----------
7566 FORD 3000 6000
7566 SCOTT 3000 6000
7698 JAMES 950 6550
7698 ALLEN 1600 6550
7698 WARD 1250 6550
7698 TURNER 1500 6550
7698 MARTIN 1250 6550
7782 MILLER 1300 1300
7788 ADAMS 1100 1100
7839 BLAKE 2850 8275
7839 JONES 2975 8275
7839 CLARK 2450 8275
7902 SMITH 800 800
KING 5000 5000
14 rows selected.
SELECT MGR, ENAME, SAL
, SUM(SAL) OVER (PARTITION BY MGR ORDER BY SAL RANGE UNBOUNDED PRECEDING) as MGR_SUM
FROM EMP;
MGR ENAME SAL MGR_SUM
---------- ---------- ---------- ----------
7566 SCOTT 3000 6000
7566 FORD 3000 6000
7698 JAMES 950 950
7698 WARD 1250 3450
7698 MARTIN 1250 3450
7698 TURNER 1500 4950
7698 ALLEN 1600 6550
7782 MILLER 1300 1300
7788 ADAMS 1100 1100
7839 CLARK 2450 2450
7839 BLAKE 2850 5300
7839 JONES 2975 8275
7902 SMITH 800 800
KING 5000 5000
14 rows selected.
(SQL Server의 경우 집계 함수의 경우 OVER 절 내의 ORDER BY 절을 지원하지 않는다.)
3.5 MAX 함수
- MAX 함수를 이용해 파티션별 윈도우의 최댓값을 구할 수 있습니다.
SELECT MGR, ENAME, SAL
, MAX(SAL) OVER (PARTITION BY MGR) as MGR_MAX
FROM EMP;
MGR ENAME SAL MGR_MAX
---------- ---------- ---------- ----------
7566 FORD 3000 3000
7566 SCOTT 3000 3000
7698 JAMES 950 1600
7698 ALLEN 1600 1600
7698 WARD 1250 1600
7698 TURNER 1500 1600
7698 MARTIN 1250 1600
7782 MILLER 1300 1300
7788 ADAMS 1100 1100
7839 BLAKE 2850 2975
7839 JONES 2975 2975
7839 CLARK 2450 2975
7902 SMITH 800 800
KING 5000 5000
14 rows selected.
SELECT MGR, ENAME, SAL
FROM
( SELECT MGR, ENAME, SAL
, MAX(SAL) OVER (PARTITION BY MGR) as IV_MAX_SAL
FROM EMP
)
WHERE SAL = IV_MAX_SAL ;
MGR ENAME SAL
---------- ---------- ----------
7566 FORD 3000
7566 SCOTT 3000
7698 ALLEN 1600
7782 MILLER 1300
7788 ADAMS 1100
7839 JONES 2975
7902 SMITH 800
KING 5000
8 rows selected.
3.6 MIN 함수
- MIN 함수를 이용해 파티션별 윈도우의 최솟값을 구할 수 있습니다.
SELECT MGR, ENAME, HIREDATE, SAL
, MIN(SAL) OVER(PARTITION BY MGR ORDER BY HIREDATE) as MGR_MIN
FROM EMP;
MGR ENAME HIREDATE SAL MGR_MIN
---------- ---------- --------- ---------- ----------
7566 FORD 03-DEC-81 3000 3000
7566 SCOTT 09-DEC-82 3000 3000
7698 ALLEN 20-FEB-81 1600 1600
7698 WARD 22-FEB-81 1250 1250
7698 TURNER 08-SEP-81 1500 1250
7698 MARTIN 28-SEP-81 1250 1250
7698 JAMES 03-DEC-81 950 950
7782 MILLER 23-JAN-82 1300 1300
7788 ADAMS 12-JAN-83 1100 1100
7839 JONES 02-APR-81 2975 2975
7839 BLAKE 01-MAY-81 2850 2850
7839 CLARK 09-JUN-81 2450 2450
7902 SMITH 17-DEC-80 800 800
KING 17-NOV-81 5000 5000
14 rows selected.
3.7 AVG 함수
- AVG 함수를 이용해 파티션별 윈도우의 평균을 구할 수 있습니다.
- 밑에 예시를 들어보면, EMP 테이블에서 같은 매니저를 두고 있는 사원들 대상으로 조건은 같은 매니저 내에서 자기 바로 앞의 사번과 바로 뒤의 사번인 직원만의 평균 SALARY를 구하는 예시입니다.
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;
MGR ENAME HIREDATE SAL MGR_AVG
---------- ---------- --------- ---------- ----------
7566 FORD 03-DEC-81 3000 3000
7566 SCOTT 09-DEC-82 3000 3000
7698 ALLEN 20-FEB-81 1600 1425
7698 WARD 22-FEB-81 1250 1450
7698 TURNER 08-SEP-81 1500 1333
7698 MARTIN 28-SEP-81 1250 1233
7698 JAMES 03-DEC-81 950 1100
7782 MILLER 23-JAN-82 1300 1300
7788 ADAMS 12-JAN-83 1100 1100
7839 JONES 02-APR-81 2975 2913
7839 BLAKE 01-MAY-81 2850 2758
7839 CLARK 09-JUN-81 2450 2650
7902 SMITH 17-DEC-80 800 800
KING 17-NOV-81 5000 5000
3.8 COUNT 함수
- COUNT 함수를 이용해 파티션별 조건에 맞는 윈도우의 수를 구할 수 있습니다.
SELECT ENAME, SAL
, COUNT(*) OVER (ORDER BY SAL RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) as SIM_CNT
FROM EMP;
ENAME SAL SIM_CNT
---------- ---------- ----------
SMITH 800 2
JAMES 950 2
ADAMS 1100 3
WARD 1250 3
MARTIN 1250 3
MILLER 1300 3
TURNER 1500 2
ALLEN 1600 1
CLARK 2450 1
BLAKE 2850 4
JONES 2975 3
SCOTT 3000 3
FORD 3000 3
KING 5000 1
14 rows selected.
그룹 내 행 순서 함수
3.9 FIRST_VALUE 함수
- FIRST_VALUE 함수를 이용해 파티션별 윈도우에서 가장 먼저 나온 값을 구합니다.
- SQL Server에서는 지원하지 않는 함수입니다. MIN 함수를 활용하여 같은 결과를 얻을 수도 있습니다.
SELECT DEPTNO, ENAME, SAL
, FIRST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC ROWS UNBOUNDED PRECEDING) as DEPT_RICH
FROM EMP;
DEPTNO ENAME SAL DEPT_RICH
---------- ---------- ---------- ----------
10 KING 5000 KING
10 CLARK 2450 KING
10 MILLER 1300 KING
20 SCOTT 3000 SCOTT
20 FORD 3000 SCOTT
20 JONES 2975 SCOTT
20 ADAMS 1100 SCOTT
20 SMITH 800 SCOTT
30 BLAKE 2850 BLAKE
30 ALLEN 1600 BLAKE
30 TURNER 1500 BLAKE
30 MARTIN 1250 BLAKE
30 WARD 1250 BLAKE
30 JAMES 950 BLAKE
14 rows selected.
SELECT DEPTNO, ENAME, SAL
, FIRST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC, ENAME ASC ROWS UNBOUNDED PRECEDING) as RICH_EMP
FROM EMP;
DEPTNO ENAME SAL RICH_EMP
---------- ---------- ---------- ----------
10 KING 5000 KING
10 CLARK 2450 KING
10 MILLER 1300 KING
20 FORD 3000 FORD
20 SCOTT 3000 FORD
20 JONES 2975 FORD
20 ADAMS 1100 FORD
20 SMITH 800 FORD
30 BLAKE 2850 BLAKE
30 ALLEN 1600 BLAKE
30 TURNER 1500 BLAKE
30 MARTIN 1250 BLAKE
30 WARD 1250 BLAKE
30 JAMES 950 BLAKE
14 rows selected.
3.9 LAST_VALUE 함수
- LAST_VALUE 함수를 이용해 파티션별 윈도우에서 가장 나중에 나온 값을 구합니다.
- SQL Server에서는 지원하지 않는 함수입니다. MAX 함수를 활용하여 같은 결과를 얻을 수도 있습니다.
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;
DEPTNO ENAME SAL DEPT_POOR
---------- ---------- ---------- ----------
10 KING 5000 MILLER
10 CLARK 2450 MILLER
10 MILLER 1300 MILLER
20 SCOTT 3000 SMITH
20 FORD 3000 SMITH
20 JONES 2975 SMITH
20 ADAMS 1100 SMITH
20 SMITH 800 SMITH
30 BLAKE 2850 JAMES
30 ALLEN 1600 JAMES
30 TURNER 1500 JAMES
30 MARTIN 1250 JAMES
30 WARD 1250 JAMES
30 JAMES 950 JAMES
14 rows selected.
3.9 LAG 함수
- LAG 함수를 이용해 파티션별 윈도우에서 이전 몇 번째 행의 값을 가져올 수 있습니다. SQL Server에서는 지원하지 않는 함수입니다.
직원들을 입사일자가 빠른 기준으로 정렬을 하고, 본인보다 입사일자가 한 명 앞선 사원의 급여를 본인의 급여와 함께 출력한다.
SELECT ENAME, HIREDATE, SAL
, LAG(SAL) OVER (ORDER BY HIREDATE) as PREV_SAL
FROM EMP
WHERE JOB = 'SALESMAN';
ENAME HIREDATE SAL PREV_SAL
---------- --------- ---------- ----------
ALLEN 20-FEB-81 1600
WARD 22-FEB-81 1250 1600
TURNER 08-SEP-81 1500 1250
MARTIN 28-SEP-81 1250 1500
SELECT ENAME, HIREDATE, SAL
, LAG(SAL, 2, 0) OVER (ORDER BY HIREDATE) as PREV_SAL
FROM EMP
WHERE JOB = 'SALESMAN' ;
ENAME HIREDATE SAL PREV_SAL
---------- --------- ---------- ----------
ALLEN 20-FEB-81 1600 0
WARD 22-FEB-81 1250 0
TURNER 08-SEP-81 1500 1600
MARTIN 28-SEP-81 1250 1250
- LAG 함수는 3개의 ARGUMENTS 까지 사용할 수 있는데,
- 두 번째 인자는 몇 번째 앞의 행을 가져올지 결정하는 것이고 (DEFAULT 1),
- 세 번째 인자는 예를 들어 파티션의 첫 번째 행의 경우 가져올 데이터가 없어 NULL 값이 들어오는데
- 이 경우 다른 값으로 바꾸어 줄 수 있습니다. 결과적으로 NVL이나 ISNULL 기능과 같습니다.
3.10 LEAD 함수
- LEAD 함수를 이용해 파티션별 윈도우에서 이후 몇 번째 행의 값을 가져올 수 있습니다.
- 참고로 SQL Server에서는 지원하지 않는 함수입니다.
SELECT ENAME, HIREDATE
, LEAD(HIREDATE, 1) OVER (ORDER BY HIREDATE) as "NEXTHIRED"
FROM EMP;
ENAME HIREDATE NEXTHIRED
---------- --------- ---------
SMITH 17-DEC-80 20-FEB-81
ALLEN 20-FEB-81 22-FEB-81
WARD 22-FEB-81 02-APR-81
JONES 02-APR-81 01-MAY-81
BLAKE 01-MAY-81 09-JUN-81
CLARK 09-JUN-81 08-SEP-81
TURNER 08-SEP-81 28-SEP-81
MARTIN 28-SEP-81 17-NOV-81
KING 17-NOV-81 03-DEC-81
JAMES 03-DEC-81 03-DEC-81
FORD 03-DEC-81 23-JAN-82
MILLER 23-JAN-82 09-DEC-82
SCOTT 09-DEC-82 12-JAN-83
ADAMS 12-JAN-83
14 rows selected.
- LEAD 함수는 3개의 ARGUMENTS 까지 사용할 수 있는데,
- 두 번째 인자는 몇 번째 후의 행을 가져올지 결정하는 것이고 (DEFAULT 1),
- 세 번째 인자는 예를 들어 파티션의 마지막 행의 경우 가져올 데이터가 없어 NULL 값이 들어오는데
- 이 경우 다른 값으로 바꾸어 줄 수 있습니다. 결과적으로 NVL이나 ISNULL 기능과 같습니다.
그룹 내 비율 함수
3.11 RATIO_TO_REPORT 함수
- RATIO_TO_REPORT 함수를 이용해 파티션 내 전체 SUM(칼럼)값에 대한 행별 칼럼 값의 백분율을 소수점으로 구할 수 있습니다.
- 결과 값은 > 0 & <= 1 의 범위를 가집니다.
- 그리고 개별 RATIO의 합을 구하면 1이 됩니다. SQL Server에서는 지원하지 않는 함수입니다.
ex) JOB이 SALESMAN인 사원들을 대상으로 전체 급여에서 본인이 차지하는 비율을 출력하고자 합니다.
SELECT ENAME, SAL
, ROUND(RATIO_TO_REPORT(SAL) OVER (), 2) as R_R
FROM EMP
WHERE JOB = 'SALESMAN';
ENAME SAL R_R
---------- ---------- ----------
ALLEN 1600 .29
WARD 1250 .22
MARTIN 1250 .22
TURNER 1500 .27
3.11 PERCENT_RANK 함수
- PERCENT_RANK 함수를 이용해 파티션별 윈도우에서 제일 먼저 나오는 것을 0으로,
- 제일 늦게 나오는 것을 1로 하여, 값이 아닌 행의 순서별 백분율을 구합니다.
- 결과 값은 >= 0 & <= 1 의 범위를 가집니다. 참고로 SQL Server에서는 지원하지 않는 함수입니다.
SELECT DEPTNO, ENAME, SAL
, PERCENT_RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) as P_R
FROM EMP;
DEPTNO ENAME SAL P_R
---------- ---------- ---------- ----------
10 KING 5000 0
10 CLARK 2450 .5
10 MILLER 1300 1
20 SCOTT 3000 0
20 FORD 3000 0
20 JONES 2975 .5
20 ADAMS 1100 .75
20 SMITH 800 1
30 BLAKE 2850 0
30 ALLEN 1600 .2
30 TURNER 1500 .4
30 MARTIN 1250 .6
30 WARD 1250 .6
30 JAMES 950 1
14 rows selected.
3.11 CUME_DIST 함수
- CUME_DIST 함수를 이용해 파티션별 윈도우의 전체건수에서 현재 행보다 작거나 같은 건수에 대한 누적백분율을 구합니다.
- 결과 값은 > 0 & <= 1 의 범위를 가집니다. 참고로 SQL Server에서는 지원하지 않는 함수입니다.
SELECT DEPTNO, ENAME, SAL
, CUME_DIST() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) as CUME_DIST
FROM EMP;
DEPTNO ENAME SAL CUME_DIST
---------- ---------- ---------- ----------
10 KING 5000 .333333333
10 CLARK 2450 .666666667
10 MILLER 1300 1
20 SCOTT 3000 .4
20 FORD 3000 .4
20 JONES 2975 .6
20 ADAMS 1100 .8
20 SMITH 800 1
30 BLAKE 2850 .166666667
30 ALLEN 1600 .333333333
30 TURNER 1500 .5
30 MARTIN 1250 .833333333
30 WARD 1250 .833333333
30 JAMES 950 1
14 rows selected.
3.11 NTILE 함수
- NTILE 함수를 이용해 파티션별 전체 건수를 ARGUMENT 값으로 N 등분한 결과를 구할 수 있습니다.
SELECT ENAME, SAL
, NTILE(4) OVER (ORDER BY SAL DESC) as QUAR_TILE
FROM EMP ;
ENAME SAL QUAR_TILE
---------- ---------- ----------
KING 5000 1
FORD 3000 1
SCOTT 3000 1
JONES 2975 1
BLAKE 2850 2
CLARK 2450 2
ALLEN 1600 2
TURNER 1500 2
MILLER 1300 3
WARD 1250 3
MARTIN 1250 3
ADAMS 1100 4
JAMES 950 4
SMITH 800 4
14 rows selected.
🙋🏻♂️ 후기
윈도우함수에 대해 정리해보았는데, 실무에서 정말 유용하게 사용하는 함수입니다. 저도 필요할때마다 검색하면서 사용했었는데 이렇게 페이지하나에 정리하니 이해하기도 쉽고 보기도 쉬울것 같습니다.
요새 블로그를 하면서 보람을 느꼈던 점이, 코딩을 하면서 잠시 잊었던 함수나 개념들을 구글링하는 것이 아니라 저의 블로그에서 제가 정리한 글을 보면서 다시 재정리하는 저의 모습을 보면서 이렇게 글로 정리하는 것의 중요성을 많이 느끼는 것 같습니다.
윈도우 함수 또한 추후에 제가 추가로 공부한 내용, 또는 바뀐 내용들을 지속적으로 업데이트 할 예정이니 자주 방문 부탁드립니다!
🔗 참고한 글
https://docs.aws.amazon.com/ko_kr/redshift/latest/dg/c_Window_functions.html
윈도 함수 - Amazon Redshift
이 페이지에 작업이 필요하다는 점을 알려 주셔서 감사합니다. 실망시켜 드려 죄송합니다. 잠깐 시간을 내어 설명서를 향상시킬 수 있는 방법에 대해 말씀해 주십시오.
docs.aws.amazon.com
http://www.gurubee.net/lecture/2382
윈도우 함수(WINDOW FUNCTION)
제6절 윈도우 함수(WINDOW FUNCTION)행과 행간의 관계를 쉽게 정의하기 위해 만든 함수가 바로 WINDOW FUNCTION이다.윈도우 함수를 활용하면 복잡한 프..
www.gurubee.net
반응형
'Postgresql' 카테고리의 다른 글
| [Postgresql] Psycopg2란? 트랜잭션 ID 추출방법은? (0) | 2022.08.09 |
|---|---|
| [DB] Master & Slave 개념에 대해 알아보자! (1) | 2022.07.06 |
| [Postgresql] 정수 범위를 벗어남 오류 분석 및 해결 과정 (1) | 2022.06.13 |
| [Postgresql] WITH문 사용하기, WITH가 성능에 미치는 영향 (0) | 2022.05.13 |
| [SQL] 연산자 우선순위 (0) | 2022.05.13 |
댓글
반응형
공지사항
최근에 올라온 글
최근에 달린 댓글
- Total
- Today
- Yesterday
링크
TAG
- JS
- container
- SQL
- docker
- generator expression
- lv2
- db
- 탐욕법
- Algorithm
- django
- Named export
- MVT
- uSWGI
- lv1
- union-find
- Default export
- static files
- react
- list
- Greedy Algorithm
- This
- ORM
- programmers
- PostgreSQL
- Linux
- Master & Slave
- JavaScript
- data formatting
- Python
- django ORM
| 일 | 월 | 화 | 수 | 목 | 금 | 토 |
|---|---|---|---|---|---|---|
| 1 | 2 | 3 | 4 | 5 | ||
| 6 | 7 | 8 | 9 | 10 | 11 | 12 |
| 13 | 14 | 15 | 16 | 17 | 18 | 19 |
| 20 | 21 | 22 | 23 | 24 | 25 | 26 |
| 27 | 28 | 29 | 30 |
글 보관함