* 윈도우 함수
- BUSINESS 분야에 자주 행하여지는 여러 가지 형태의 분석에 유용하게 활용될 수 있는 함수
- 각 window별 집합 연산을 수행한 결과를 Return 하는 함수
- join이나 프로그램의 Over Head를 줄임
- 간결한 SQL로 복잡한 분석 작업 수행 가능
- 이해 및 활용 용이함
- 기존에 사용하던 집게 함수와 WINDOW 함수 전용으로 만들어진 기능도 있음
- 다른 함수와는 달리 중첩 (NEST)해서 사용할 수 없지만, 서브쿼리에서 사용이 가능함
거의 대부분의 분석함수는 SELECT절 바로 뒤에서 볼 수 있습니다.
Analytic Function : 분석함수명 (입력 인자)
OVER : 분석함수임을 나타내는 키워드
Partition By : 계산 대상 그룹 결정
Order By : 대상 그룹에 대한 정렬 수행
Windowing절 : 분석함수의 계산 대상 범위 지정
* Order By절에 종속적임
* 기본 생략 구문 : 정렬된 결과의 처음부터 현재행까지 [range betwwen unbounded Preceding and Current row]
partition by ~~~ order by ~~~ ROWS ~~~
(1) (2) (3)
*각각 하나씩 써도 되지만, 1.2.3번의 순서는 지켜주어야 합니다.
(1) : 분석 함수 안에 partition by 함수를 붙여주면 partition by 기준으로 함수가 초기화된다.
(2) : RANK, DENSE_RANK, ROW_NUMBER 함수들은 ORDER BY 꼭 써줘야 하는 함수이다.
(적용할 때 어떤 순서대로 정렬 해 놓고 함수 적용하러 갈 것인지 정할 때 사용한다.)
(3) : 줄단위로 (어느줄~어느 줄까지 지정할 때 사용) 단독 사용 불가 X, ORDER BY와 함께 사용해야 합니다.
윈도우 함수 처리 단계
|
joins, WHERE조건절, GROUP BY, HAVING절 -> 윈도우 함수 처리 -> ORDER BY |
*joins, WHERE조건절, GROUP BY, HAVING절이 끝나고 난 후에 윈도우 함수 처리를 하기 때문에 해당 절에는 윈도우 함수를 사용할 수 없습니다.
- 대상 집합을 Analytic Function 이 적용되어야 할 각 GROUP으로 나눔 (PARTITON BY절 지정된 항목으로 partitioning)
- 윈도우 함수에 지정한 order by절에 기준 자료를 정렬함
- pointer와 off-set개념을 적용하여 각 Row 간에 필요한 계산을 수행함
윈도우 함수 종류
RANK :
- 각 로우(row)마다 순위를 매겨주는 함수
- PARTITION 내에서 ORDER BY절에 명시된 대로 정렬한 후 순위 부여
- 1부터 시작하여 동일한 값은 동일한 순위를 가지며, 동일한 순위의 수만큼 다음 순위는 건너뜀
예제) RANK()
emp테이블에서 partition by가 없기 때문에 모든 애들을 sal(급여)가 큰 것부터 1등 2등 3등... 나오도록
사원번호, 이름, 부서 번호, 급여, 급여가 많은 사원부터 순위 조회
예제)
partition by가 사용되었기 때문에 부서 별로 따로따로 등수를 부여해준다.
사원번호, 이름, 부서 번호, 급여, 부서 내(deptno)에서 급여가 많은 사원부터 순위 조회
partition by 사용하여 부서 내에서 급여가 많은 사원부터 등수 1번에 해당하는 칼럼을 보면 확인할 수 있다.
DENSE_RANK
- DENSE_RANK()는 RANK()와 유사한 함수이다.
- order by절에 사용된 칼럼이나 표현식에 대하여 순위를 부여하는데,
RANK()와 달리 동일 순위 다음의 순위는 동일 순위의 수와 상관없이 1 증가된 값을 돌려준다.
|
즉, 100점 100점 99점 받은 사람이 있다. 이럴 때 RANK() 함수는 1등 1등 3등 DENSE_RANK() 함수는 1등 1등 2등으로 순위에 있는 값이 빠지지 않고 표시된다. |
예제)
partition by 사용하지 않았기 때문에 전체 emp에서
사원 번호, 이름, 부서 번호, 급여, 급여가 많은 사원부터 순위 조회
등수 1 등수 2의 차이점 확인할 수 있습니다.
예제)
사원번호, 이름, 부서 번호, 급여,부서 내에서 급여가 많은 사원부터 순위 조회
partition by 사용하여 부서별로 급여가 많은 사원부터 순위 조회
등수 2에 해당하는 조회 칼럼을 보면 된다.
ROW_NUMBER
- row_number()는 각 partition 내에서 order by절에 의해 정렬된 순서로 유일한 값을 돌려주는 함수
- rownum과 혼동하지 않도록 함
RANK(), DENSE_RANK()는 순위를 부여하는 거라고 한다면, row_number은 전부다 다른 값을 부여한다고 보면 된다.
예제)
사원번호, 이름, 부서 번호, 급여, 급여가 많은 사원부터 순위 조회
예를 들어, 3000이 두 개 있어도 하나는 1번, 하나는 2번으로 다 다른 값을 부여한다.
부여하고 싶은 기준이 따로 없고, 조회되는 순서대로 2번 3번 순위를 준다.
예제)
사원번호, 이름, 연봉, 입사일, 순번 호회
(급여가 많은 순으로, 같은 급여를 받는 경우 입사일이 빠른 사람부터 앞 번호 부여)
order by 절에 칼럼 두 개 (sal, hiredate) : 1차 정렬 기준이 똑같을 때만 2번째 값을 비교해서 정렬해줘
즉 급여가 같은 급여가 있다면, 입사 먼저 한 사람부터 앞번호를 부여한다.
똑같은 급여를 받을 때 ename의 정렬 순서로 결과를 보여준다.
NTILE (엔타일)
- 파티션을 BUCKET이라 불리는 그룹별로 나누고 파티션 내의 각 ROW 등을 BUCKET에 배치하는 함수
- 각 BUCKET에는 동일한 수의 ROW가 배치됨
- 예를 들어 파티션 내에 100개의 ROW를 가지고 있고, 4개의 BUCKET으로 나누는 NTILE(4)를 사용하면, 1개의 BUCKET당 25개의 ROW가 배정된다.
- 만약 103개의 ROW에 대하여 NTILE(5)를 적용하면 첫 번째 BUCKET부터 세 번째까지는 21개의 ROW가, 나머지는 20개의 ROW가 배치됨
-> 즉, 103개의 row를 5개의 그룹으로 나누면, 20개씩 row를 가진 그룹이 만들어지고
3개의 남는 row가 발생한다. 20, 21, 22그룹까지는 1개씩 추가된 21개의 Row가 있고, 나머지는 20개씩의 row가 배치된다는 것이다.
예제)
(order by sal ) 급여가 적은 사원부터, NTILE(4)를 사용하여 4개로 분류해서 조회
데이터 총 14개이고, 3개로 나누면
1그룹 받은 거 5개, 2그룹 5개 , 3그룹 4개 나오는 것을 확인할 수 있습니다.
Window Aggregate FAmily
- 윈도우를 근간으로 하여 각각의 로우들에 대한 집계 값을 반환함
사원 이름, 부서 번호, 급여, 전체 급여 합계, 부서별 급여 합계 조회
그냥 SUM(sal) : 집계 함수이지만 뒤에 over() 함수를 붙여주게 되면, 분석함수처럼 볼 수 있다.
줄마다 계산한 결과를 찍을 수 있다.
사원 이름, 업무, 급여, 업무별 급여 평균, 해당 업무의 최대급여 조회
AVG(sal) , MAX(sal) 둘 다 over() 함수 붙여서 사용 가능합니다.
Window절 사용
ROW옵션을 사용해 윈도우 함수 계산 범위를 지정함.
sum1 (ROWS betwwen 1 preceding and 1 following) : 기준을 현재 row로 r기준 앞 1개부터 뒷줄 1개까지 3줄씩 더해서 누적 합계를 조회
sum2 (rows unbounded preceding) : 현재 데이터 앞줄에 있는 모든 값들을 더할 때 사용된다.
order by ename : 이름 순으로 정렬된 것을 확인할 수 있다.
ROWS betwwen 1 preceding and 1 following : 계산 범위를 지정
ADAMS의 결과: 본인행 이전 데이터 0 + 본인 데이터 1100 + 뒷줄 1600 = 2700
현재 데이터 앞줄에 있는 모든 값들을 더할 때 사용된다.
ADAMS 결과 : 앞의 아무 데이터가 없기 때문에 본인 데이터의 합 즉 1100 결과 확인
ALLEN 결과 : 앞의 값이 1100 + 본인 값 1600 = 2700 결괏값 확인
일자별 누적된 결과 확인하고 싶을 때 많이 사용된다.
LAG
현재 데이터를 기준으로 앞줄 뒷줄 값을 가져오고 싶을 때
앞에 있는 값을 끌어내릴 때 사용하는 값.
LAG(sal, 1, 0) : sal은 어떤 칼럼을 가지고 오고 싶은지 인자로 지정
LAG(sal, 1, 0) : 1 은 기준으로 줄 간격 (1줄 앞에 있는 sal을 가져오겠다.)
LAG(sal, 1, 0) : 0은 한 줄 앞의 결과가 없으면 0을 쓴다.
LAG(sal, 1, sal) : sal은 1줄 앞줄에 값이 없을 때 내 급여로 그냥 쓴다.
LEAD
- Lead()는 Lag()와 유사한 함수다.
- offset에 지정된 값 (default =1)만큼 상대적으로 하위에 위치한 (row)를 참조하기 위해 사용됨
lead는 앞줄에 있는 데이터가 아닌 뒷줄에 있는 데이터를 끌어올릴 때 사용하는 함수다.
앞에 있는 걸 끌어내릴 땐 LAG() 사용.
사원 이름, 부서 번호, 연봉, 본인 다음의 연봉 값 조회
lead(sal,1, 0) : sal이 본인 데이터 뒷줄에 있는 값을 가져오는데 없으면 0을 사용한다.
<마지막 정리 >
윈도우 함수 동작 원리
- SELECT문 수행 결과를 대상으로 윈도우 함수가 적용된다.
- Partition by 절을 사용해 윈도우 함수 적용 대상을 나누어 처리할 수 있다.
- window절 (rows옵션)을 사용하여 계산 범위를 제한할 수 있다.
윈도우 함수 종류
- 순위를 부여하기 위해 RANK, DENESE_RANK 함수를 사용한다.
- sum, avg, min, max, count도 윈도우 함수로 사용할 수 있다.
- 앞줄 자료를 가져오기 위해 lag함수를, 뒷줄 자료를 가져오기 위해 lead함수를 사용한다.
'데이터베이스 > 기초 SQL 입문' 카테고리의 다른 글
| 조인 처리과정 이해 및 기본 조인 문장 (0) | 2020.09.16 |
|---|---|
| 그룹 함수 (0) | 2020.09.14 |
| 일반 함수 (0) | 2020.09.13 |
| 변환 함수 (0) | 2020.09.13 |
| 날짜 연산 및 날짜 함수 (0) | 2020.09.11 |
