SQLD 4-6 ] 윈도우 함수(WINDOW FUNCTION)
❏ WINDOW FUNCTION
- 칼럼과 칼럼과의 연산, 비교, 연결 집합 등 은 쉬운데.. 행과 행간의 관계 정의하거나 비교, 연산 하려고 만들었다!
- 분석함수 나 순위함수 로도 알려졌다.
- 중첩(NEST) 해서 사용하지는 못하지만, 서브쿼리에서는 사용할 수 있다!
❏ 윈도우함수의 종류
1. 그룹 내 순위 함수
-> RANK / DENSE_RANK / ROW_NUMBER
2. 그룹 내 집계 함수
-> SUM / MAX / MIN / AVG / COUNT
3. 그룹 내 행 순서 함수
-> FIRST_VALUE / LAST_VALUE / LAG / LEAD
4. 그룹 내 비율 관련 함수
-> CUME_DIST / PERCENT_RANK / NTILE / RATIO_TO_REPORT
5. 선형 분석을 포함한 통계 분석 함수
-> 어려웡
오... 함수 엄청많다.. ㅠㅠ 1, 2 번까지는 그래도 그냥 알꺼같은데 3, 4번..!!!
❏ 윈도우함수 문법
SELECT WINDOW_FUNCTION ( ARGUMENTS ) OVER
( [ PARTITION BY 칼럼] [ ORDER BY 절] [ WINDOWING 절 ])
FROM 테이블 명 ;
윈도우 함수에는 OVER 문구가 필수로 들어가야한다!
1. WINDOWS_FUNCTION : 쓸 윈도우 함수
2. ARGUMENTS : ( 인수 ). 0 ~ N 개
3. OVER : 필수로 그냥 쓰고
4. PARTITION BY : 전체 집합을 기준에 의해 소그룹으로 나눌 수 있다.
5. ORDER BY : 순서
6. WINDOWING : window ing? 함수의 대상이 되는 행 기준의 범위를 강력하게! 지정한다. ??
ROWS : 물리적인 결과 행의 수
RANGE : 논리적인 값에 의한 범위
둘중 하나 택해서 사용할 수 있다.. 뭐지..
BETWEEN 사용 타입
ROWS | RANGE BETWEEN
UNBOUNDED PRECEDING | CURRENT ROW | VALUE_EXPR PRECEDING/FOLLOWING
AND
UNBOUNDED FOLLOWING | CURRENT ROW | VALUE_EXPR PRECEDING/FOLLOWING
BETWEEN 미사용 타입
ROW | RANGE
UNBOUNDED PRECEDING | CURRENT ROW | VALUE_EXPR PRECEDING
❏ 그룹 내 순위 함수
-> RANK / DENSE_RANK / ROW_NUMBER
RANK : 1 2 2 4 (동일 순위 시 다음순위 생략)
DENSE_RANK : 1 2 2 3 (동일 순위 시 다음순위 부터)
ROW_NUMBER : 1 2 3 4 (동일 따윈 없다)
❏ RANK
- ORDER BY 를 포함한 쿼리문'에서 칼럼에 대한 순위를 구하는 함수.
- 특정범위 (PARTITION) 또는 전체데이터 에서 순위를 구할 수 있다.
- 동일한 값에 대해서는 동일한 순위를 부여한다.
사원 데이터에서 급여가 높은 순서와 JOB별로 급여가 높은 순서를 같이 출력한다
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;
FORD, SCOTT 는 동일 월급 이라서 동일 순위.
하나의 문장에 PARTITION BY JOB 과 ORDER BY SAL DESC 조건이 충돌 나서.. JOB 별로는 정렬되지 않았고
ORDER BY SAL DESC 조건으로 정렬이 되었다.
위에 SQL 문은 JOB 이름순과 SALARY 봉급순으로 정렬되진 않았다.
새로 JOB 도 이름순, SALARY 도 봉급순 대로 해보자
select JOB, ENAME, SAL,
rank() over (partition by JOB order by SAL desc) JOB_RANK
from EMP;
된거같다.
❏ DENSE_RANK
- RANK 함수와 흡사한데, 동일 순위 시 하나의 건수로 취급
select JOB, ENAME, SAL,
rank() over (order by SAL desc) RANK,
dense_rank() over (order by SAL desc) DENSE_RANK
from EMP;
❏ ROW_NUMBER
- 동일 따윈 있을 수 없다..!!!!
select JOB, ENAME, SAL,
rank() over (order by SAL desc) RANK,
row_number() over (order by SAL desc) ROW_NUMBER
from EMP;
여기까지가 1,2,3,4 윈도우 함수 중 1번 RANK 관련 부분이다..
2번 집계 함수 갑시다
❏ 그룹 내 집계 함수
-> SUM / MAX / MIN / AVG / COUNT
❏ SUM
- 파티션 별로 윈도우의 합을 구할 수 있다.
사원들의 급여와 / 같은 매니저를 두고 있는 사원의 SALARY 합을 구한다
같은 매니저를 두고 있는 사원이라.
그럼 매니저를 소그룹 맺으면 그에 따라 정보가 딸려오겠지?
select MGR, ENAME, SAL, sum(SAL) over (partition by MGR) MGR_SUM
from EMP;
PARTITION BY MGR 구문을 통해 매니저 별로 파티션화 했다.
ORDER BY 를 껴서 데이터를 정렬해보자.
이전 SALARY 데이터까지의 누적값을 출력한다
select MGR, ENAME, SAL, SUM(SAL) over (partition by MGR order by SAL range unbounded preceding) as MGR_SUM
from EMP;
RANGE UNBOUNDED PRECEDING
: 현재 행을 기준으로 파티션 내의 첫 번째 행까지의 범위를 지정한다. 즉 누적 한다는 말이네. 새로운 행 뜨면 맨 첫번째부터 범위. 반복. 즉 누적
7698 WARD 1250 , 7698 MARTIN 1250 이 동일 순위 여서. 950+1250+1250 = 3450 을 동일하게 줬고.
그 후 950+1250+1250+1500 = 4950 .. 이렇게..
❏ MAX
- 파티션 별 윈도우의 최대값을 구할 수 있다
select MGR, ENAME, SAL, max(SAL) over (partition by MGR) as MGR_MAX
from EMP;
이렇게. 파티션 별 최대값을 나타낸다.
추가로, INLINE VIEW 를 이용해 최대값을 가진 행만 추출할 수 있다.
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;
이렇게 인라인 뷰를 써서 최대값을 가진 행만 추출할 수 있다.
문제를보고 어떻게 저렇게 쿼리문을 바로 떠올릴까...
❏ MIN
- 파티션별 윈도우의 최소값을 구할 수 있다. MAX 랑 똑같을 것 같은데?
사원들의 급여와 / 같은 매니저를 두고 있는 사원들을 '입사일자' 기준으로 정렬하고 SALARY 최소값을 같이 출력해라
select MGR, ENAME, HIREDATE, SAL, min(SAL) over (partition by MGR order by HIREDATE) as MGR_MIN
from EMP;
❏ 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;
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
: 현재 행을 기준으로 파티션 내에서 앞의 한건, 뒤의 한건을 범위로 지정한다.
ROWS 는 현재 행의 앞 뒤 건수를 말한다.
ALLEN 의 앞 데이터는 없고 뒷 데이터 (WARD) 만 있다.
( 1600 + 1250 ) / 2 = 1425
TURNER 의 앞 데이터는 WARD 1250, 뒷 데이터는 MARTIN 1250 이다.
( 1250 + 1500 + 1250 ) / 3 = 1333
❏ COUNT
- 갯수 세는거겠지
사원들을 급여 기준으로 정렬하고 / 본인의 급여보다 50 이하가 적거나 150 이하로 많은 급여를 받는 인원수를 출력하라
select ENAME, SAL, count(*) over (order by SAL range between 50 preceding and 150 following) as SIM_CNT
from EMP;
range between 50 precdeing and 150 following
: 현재 행에서 앞 -50 뒤 +150 범위.
❏ 그룹 내 행 순서 함수
- FIRST_VALUE / LAST_VALUE / LAG / LEAD
❏ FIRST_VALUE
- 파티션 별 윈도우에서 가장 먼저 나온 값? 맨 첫번째 행인가?
부서별 직원들을 연봉이 높은 순서부터 정렬하고 / 파티션 내에서 가장 먼저 나온 이름을 출력한다.
select DEPTNO, ENAME, SAL, first_value(ENAME) over (partition by DEPTNO order by SAL desc rows unbounded preceding) as DEPT_RICH
from EMP;
ROWS UNBOUNDED PRECEDING
: 현재 행을 기준으로 파티션 내의 첫 번째 행까지의 범위를 지정한다.
부서코드 20 을 보면. SCOTT 와 FORD 가 공동 부서 내 연봉왕인데. 왜 SCOTT 가 연봉왕일까.
공등등수를 인정하지 않고 그냥 단순하게 처음 나온 행 갖고 처리하기 때문에. SCOTT 가 연봉왕!!
인라인뷰나 over () 내에 order by 절에 정렬 칼럼 을 추가하면. 결과가 바뀔수 있겠지
❏ LAST_VALUE
- 그 파티션별 윈도우에서 가장 맨 마지막에 나온값.
부서별 직원들을 / 연봉이 높은 순서부터 정렬하고 / 파티션 내에서 가장 마지막에 나온 사람 이름
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;ROWS BETWEEN CORRENT ROW AND UNBOUNDED FOLLOWING
: 현재 행을 포함해서 파티션 내의 마지막 행까지를 범위.
궁금!
FIRST_VALUE 에서는 ROWS UNBOUNDED PRECEDING 으로 현재행 ~ 파티션 맨앞 이고.
근데 왜 LAST_VALUE 에서는 ROWS UNBOUNDED FLOOWING 하면 현재행 ~ 파티션 맨뒤 는 안되지?
이 문구는 에러나고 ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING 이 맞는 문구다. 음..
❏ LAG
- 파티션별 윈도우에서 이전 몇 번째 행의 값을 가져올 수 있다.
직원들을 입사일자가 빠른 기준으로 정렬 / 본인보다 입사일자가 한 명 앞선 사원의 급여를 / 본인의 급여와 함께 표시
select ENAME, HIREDATE, SAL, lag(SAL) over (order by HIREDATE) as PREV_SAL
from EMP
where JOB = 'SALESMAN';
LAG(인자1, 인자2, 인자3) 까지 사용 가능
인자1 : 칼럼명
인자2 : 몇 번째 앞의 행을 가져올껀지
인자3 : NULL 이면 다른값으로 변경. (첫번째 경우 앞에것이 없으니.. NULL 이 들어오는데 이걸 뭐로 처리할꺼냐)
select ENAME, HIREDATE, SAL, lag(SAL, 2, 0) over (order by HIREDATE) as PREV_SAL
from EMP
where JOB = 'SALESMAN';
❏ LEAD
- 파티션 별 윈도우에서 '이후 몇번째 행의 값' 을 가져올 수 있다. LAG 랑 반대네.
select ENAME, HIREDATE, lead(HIREDATE, 1) over (order by HIREDATE) as NEXTHIRED
from EMP;
똑같이 이것도 3개의 인자 사용 가능
LEAD(인자1, 인자2, 인자3)
인자1 : 칼럼명
인자2 : 몇 번째 뒤의 행을 가져올건지
인자3 : NULL 이면 뭐로 대체할껀지. (맨 마지막의 경우...)
❏ 그룹 내 비율 관련 함수
- CUME_DIST / PERCENT_RANK / NTILE / RATIO_TO_REPORT
❏ CUME_DIST
- 파티션 별 윈도우의 전체건수에서 현재 행보다 작거나 같은 건수에 대한 누적백분율
결과값은 >0 & <=1 의 범위를 가진다. 0 초과 1이하
같은 부서 소속 사원들의 집합에서 / 본인의 급여가 누적 순서상 몇 번째 위치쯤에 있는지 / 0과 1사이의 값으로 출력
select DEPTNO, ENAME, SAL, round(cume_dist() over (partition by DEPTNO order by SAL desc),3) as CUME_DIST
from EMP;
DEPTNO 가 10인 경우 윈도우가 전체 3건이므로 건당 0.3333 단위의 간격.
DEPTNO 가 20인 경우 윈도우가 전체 5건이므로 건당 0.2000 단위의 간격.
신기하네. 0 ~ 1 사이의 숫자로 표현.
❏ PERCENT_RANK
- 파티션별 윈도우에서 '제일 먼저 나오는 것은 0', '제일 늦게 나오는 것은 1' 로 하여 값이 아닌. 행의 순서별 백분율?
결과값은 >= 0 & <= 1 의 범위를 가진다. 0이상 1이하
같은 부서 소속 사원들의 집합에서 본인의 급여가 순서상 몇 번째 위치해 있는지 0과 1사이의 값으로 출력
select DEPTNO, ENAME, SAL, percent_rank() over (partition by DEPTNO order by SAL desc) as P_R
from EMP;
DEPTNO 가 10인 경우 3건. 구간은 2개가 된다.
0 0.5 1
❏ NTILE
- N개의 타일..? 파티션별 전체 건수를 인자 값으로 N등분한 결과. 맞네. 이름 그대로.
전체 사원을 급여가 높은 순으로 정렬하고 / 급여를 기준으로 4개의 그룹으로 분류
select DEPTNO, ENAME, SAL, ntile(4) over (order by SAL desc) as QUAR_TILE
from EMP;
NTILE(4) 는 전체 인원을 4개 조로 나눈다는 뜻이다.
총 14명의 결과가 나왔다. 4개조로 무조건! 나오게 해야한다.
14 / 4 = 3.. 2
3명씩은 무조건 넣어주자. 3 / 3 / 3 / 3
나머지 2명은 앞에서부터 채워준다 4 / 4 / 3 / 3
9명의 결과를 NTILE(4) 로 해보자.
9 / 4 = 2 ... 1
2 / 2 / 2 / 2 에 나머지 1명을 맨앞에부터 채우면
3 / 2 / 2 / 2
❏ RATIO_TO_REPORT
- 파티션 내 전체 SUM 값에 대한 행별 칼럼 값의 백분율을 소수점으로 나타낸다.
결과값은 >0 & <=1 의 범위를 가진다.
그리고, 개별 RATIO 의 합을 구하면 1이 된다.
JOB 이 SALESMAN 인 사원들을 대상으로 / 전체 급여에서 본인이 차지하는 비율울 출력한다
select ENAME, SAL, round(ratio_to_report(SAL) over (), 2) as R_R
from EMP
where JOB = 'SALESMAN';
1. where JOB = 'SALESMAN' 은 4건이다.
2. 이 4건 중에서 RATIO_TO_REPORT(SAL) 로 나온 파티션 내 전체 SAL 합계에 대한 백분율을 소수점으로 나타낸것이다
1600 + 1250 + 1250 + 1500 = 5600
ALLEN -> 1600 / 5600
각 RATIO 전체 합은 1이 된다.