해당 포스트는
SQLD를 학습하며 정리한 내용에 대한 포스트입니다.
이론을 학습한 내용을 정리하고 퀴즈, 기출문제 등을 기록하려고 합니다.
🌈 WINDOW FUNCTION (윈도우 함수)
💻 WINDOW FUNCTION (윈도우 함수)
OVER 키워드와 함께 사용되며 역할에 따라 다음과 같이 나눌 수 있다.
| 순위 함수 | RANK, DENSE_RANK, ROW_NUMBER |
| 집계 함수 | SUM, MAX, MIN, AVG, COUNT |
| 행 순서 함수 | FIRST_VALUE, LAST_VALUE, LAG, LEAD |
| 비율 함수 | CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT |
윈도우 함수는 행과 행 간의 관계를 정의하기 위해서 제공되는 함수이다.
윈도우 함수를 사용해서 순위, 합계, 평균, 행 위치 등을 조작할 수 있다.
윈도우 함수 구조
| 구조 | 설명 |
|---|---|
| ARGUMENTS | - 윈도우 함수에 따라서 0~N개의 인수를 설정한다. |
| PARTITION BY | - 전체 집합을 기준에 의해 소그룹으로 나눈다. |
| ORDER BY | - 어떤 항목에 대해서 정렬한다. |
| WINDOWING | - 행 기준의 범위를 정한다. - ROWS는 물리적 결과의 행 수이고 RANGE는 논리적인 값에 의한 범위이다. |
WINDOWING
| 구조 | 설명 |
|---|---|
| ROWS | 부분집합인 윈도우 크기를 물리적 단위로 행의 집합을 지정한다. |
| RANGE | 논리적인 주소에 의해 행 집합을 지정한다. |
| BETWEEN~AND | 윈도우의 시작과 끝의 위치를 지정한다. |
| UNBOUNDED PRECEDING | 윈도우의 시작 위치가 첫 번째 행임을 의미한다. |
| UNBOUNDED FOLLOWING | 윈도우의 마지막 위치가 마지막 행임을 의미한다. |
| CURRENT ROW | 윈도우 시작 위치가 현재 행임을 의미한다. |
- WINDOWING은 아래와 같이 사용한다.
UNBOUNDED PRECEDING은 처음 행을 의미하며, UNBOUNDED FOLLOWING은 마지막 행을 의미한다.
그러므로 TOTSAL에 처음부터 마지막까지의 합계 (SUM(SAL))를 계산한 것이다.
위의 SQL문은 처음부터 CURRENT ROW까지의 합계를 계산한다.
결과적으로 누적합계가 된다.
즉, 첫 번째 행의 SAL은 800이고 두 번째 행의 SAL은 950이다.
그러므로 두 번째 행의 TOTSAL은 800+950=1750이 된다.
세 번째 행은 다시 1100+1750=2850이 된다.
CURRENT ROW는 데이터가 인출된 현재 행을 의미한다.
위의 SQL문은 현재 행(CURRENT ROW)부터 마지막 행(UNBOUNDED FOLLOWING)까지의 합계를 계산한다.
첫 번째 행의 SAL이 800이므로 800부터 끝까지의 합계를 TOTSAL에 계산한다.
결과적으로 전체합계가된다.
그다음은 950부터 마지막까지이므로 800이 제외된 합계가 된다.
따라서 28225가 된다.
💻 순위 함수
윈도우 함수는 특정 항목과 파티 션에 대해서 순위를 계산할 수 있는 함수를 제공한다.
순위 함수는 RANK, DENSE_RANK, ROW_NUMBER 함수가 있다.
순위 관련 윈도우 함수
| 순위 함수 | 설명 |
| RANK | - 특정 항목 및 파티션에 대해서 순위를 계산한다. - 동일한 순위는 동일한 값이 부여된다. - 같은 순위가 존재하면 존재하는 수만큼 다음 순위를 건너뛴다. |
| DENSE_RANK | - 동일한 순위를 하나의 건수로 계산한다. - 같은 순위가 존재하더라도 다음 순위를 건너뛰지 않고 이어서 매긴다. |
| ROW_NUMBER | - 동일한 순위에 대해서 고유의 순위를 부여한다. - 즉, 순위를 매기면서 동일한 값이라도 각기 다른 순위를 부여한다. |
RANK 함수는 순위를 계산하며, 동일한 순위에는 같은 순위가 부여된다.
RANK( ) OVER (ORDER BY SAL DESC)는 SAL로 등수를 계산하고 내림차순으로 조회하게 한다.
RANK( ) OVER (PARTITION BY JOB ORDER BY SAL DESC)는 JOB으로 파티션을 만들고 JOB별 순위를 조회하게 한다.
- DENSE RANK는 동일한 순위를 하나의 건수로 인식해서 조회한다.
- ROW_NUMBER 함수는 동일한 순위에 대해서 고유의 순위를 부여한다.
💻 집계 함수
윈도우 함수를 제공한다.
집계 관련 윈도우 함수
| 집계 함수 | 설명 |
|---|---|
| SUM | 파티션 별로 합계를 계산한다. |
| AVG | 파티션 별로 평군을 계산한다. |
| COUNT | 파티션 별로 행 수를 계산한다. |
| MAX와 MIN | 파티션 별로 최댓값과 최솟값을 계산한다. |
🍳 SUM
ORACLE의 경우 OVER절 내에 ORDER BY 절을 써서 데이터의 누적값을 구할 수 있다.
SUM 하는 컬럼을 OVER 절에서 ORDER BY 절에 명시해주게 되면 RANGE UNBOUNDED PRECENDING 구문이 없어도 누적합이 집계된다.
집계 함수 SUM()와 OVER절에서 MGR파티션을 만들고 같은 관리자를 가지고 있는 합계를 계산하고 있다.
윈도우 함수 사용 옵션
WINDOWING 절을 이용하여 집계하려는 데이터의 범위를 지정할 수 있다.
RANGE ROW
- BETWEEN UNBOUNDED PRECEDING AND n PRECEDING
- BETWEEN UNBOUNDED AND CURRENT ROW
- BETWEEN UNBOUNDED PRECEDING AND n FOLLOWING
- BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
- BETWEEN n PRECEDING AND n PRECEDING
- BETWEEN n PRECEDING AND CURRNET ROW
- BETWEEN n PRECEDING AND n FOLLOWING
- BETWEEN n PRECEDING AND n UNBOUNDED FOLLOWING
- BETWEEN CURRENT ROW AND n FOLLOWING
- BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
- BETWEEN n FOLLOWING ROW AND n FOLLOWING
- BETWEEN n FOLLOWING ROW AND UNBOUNDED FOLLOWING
- UNBOUNDED PRECEDING
- *RANGE UNBOUNDED PRECEDING이 DEFAULT
- n PRECEDING
- CURRENT ROW
범위
UNBOUNDED PRECEDING : 위쪽 끝 행
UNBOUNDED FOLLOWING : 아래쪽 끝행
CURRENT ROW : 현재 행
n PRECEDING : 현재 행에서 위로 n 만큼 이동
n FOLLOWING : 현재 행에서 아래로 n 만큼 이동
기준
ROWS : 행 자체가 기준이 된다.
RANGE : 행이 가지고 있는 데이터 값이 기준이 된다.
- RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
- 처음행부터 현재 행까지, RANGE UNBOUNDED PRECEDING과 같음
- RANGE BETWEEN 10 PRECEDING AND CURRENT ROW
- 현재 행이 가지고 있는 값보다 10만큼 적은 행부터 현재 행까지, RANGE 10 PRECEDING과 같음
- ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
- 현재 행부터 끝까지
- ROWS BEWEEN CURRENT ROW AND 5 FOLLOWING
- 현재 행부터 아래로 5만큼 이동한 행까지
💻 행 순서 관련 함수
행 순서 관련 함수는 상위 행의 값을 하위에 출력하거나 하위 행의 값을 상위 행에 출력할 수 있다.
특정 위치의 행을 출력할 수 있다.
행 순서 관련 윈도우 함수
| 행 순서 | 설명 |
| FIRST_VALUE | - 파티션에서 가장 처음에 나오는 값을 구한다. - MIN 함수를 사용해서 같은 결과를 구할 수 있다. |
| LAST_VALUE | - 파티션에서 가장 나중에 나오는 값을 구한다. - MAX 함수를 사용해서 같은 결과를 구할 수 있다. |
| LAG | 파티션 별로 특정 수만큼 앞선 데이터를 가지고 온다. - 기본값은 1이다. |
| LEAD | - 파티션 별로 특정 수만큼 뒤에 있는 데이터를 가지고 온다. - 기본값은 1이다. |
FIRST_VALUE 함수는 파티션에서 조회 된 행 중에서 첫 번째 행의 값을 가지고 온다.
위의 예에서 TEST7과 TEST14가 조회되었다.
그중에서 TEST7이 첫 번째 행이므로 첫 번째 행의 TEST7을 가지고 온다.
단, SAL 내림차순으로 조회했기 때문에 의미상으로는 부서 내에 가장 급여가 많은 사원이 된다.
LAST_VALUE 함수는 파티션에서 마지막 행을 가지고 온다.
그래서 FIRST_VALUE와 다르게 TEST14가 출력된다.
“BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING”의 의미는 현재 행에서 마지막 행까지의 파티션을 의미한다.
LAG 함수는 특정 행 수만큼 앞선 데이터를 가지고 오는 것이다.
LAG함수의 두번째 인자를 생략하면 DEFAULT는 1이 된다.
예를 들어 PRE_SAL의 5000값은 SAL의 이전 데이터이다.
LEAD 함수는 지정된 행의 값을 가지고 오는 것이다.
위의 예는 SAL에서 2번째 행의 값을 가지고 온다.
LEAD의 기본값은 1며, 첫 번째 행의 값을 가지고 오는 것이다.
💻 비율 관련 함수
비율 관련 함수는 누적 백분율, 순서별 백분율, 파티션을 N분으로 분할한 결과 등을 조회할 수 있다.
| 비율 함수 | 설명 |
|---|---|
| CUME_DIST | - 파티션 전체 건수에서 현재 행보다 작거나 같은 건수에 대한 누적 백분율을 조회한다. - 누적 분포상의 위치를 0~1사이의 값을 가진다. |
| PERCENT_RANK | - 해당 파티션의 맨 위 끝 행을 0, 맨아래 끝 행을 1로 놓고 현재 행이 위치하는 백분위 순위 값을 구하는 함수 |
| NTILE | 파티션별로 전체 건수를 ARGUMENT 값으로 N 등분한 결과를 조회한다. |
| RATIO_TO_REPORT | - 파티션 별 합계에서 차지하는 비율을 구하는 함수 |
- PERCENT_RANK 함수는 파티션에서 등수의 퍼센트를 구하는 것이다.
- NTILE(4)는 4등분으로 분할하라는 의미로 위의 예에서는 급여가 높은 순으로 1〜4등분으로 분할한다.
🎯 추가 내용
❌
👉
📚 레퍼런스
정미나. (유튜브 선생님에게 배우는) 유선배 SQL개발자(SQLD) 과외노트 / [정미나 저] (2023). Print.
한국데이터산업진흥원. SQL 자격검정 실전문제 : 국가공인 SQL전문가·국가공인 SQL개발자 / 한국데이터산업진흥원 [편] (2020). Print.