-서로 다른 행의 비교나 연산을 위해 만든 함수
-GROUP BY를 쓰지 않고 그룹 연산 가능
-LAG, LEAD, SUM, AVG, MIN, MAX, COUNT, RANK
SELECT 윈도우함수([대상]) OVER(
[ PARTITION BY 컬럼 ]
[ ORDER BY 컬럼 ASC|DESC ]
[ROWS|RANGE BETWEEN A AND B]
);
PARTITION BY 절
- 출력할 총 데이터 수 변화 없이 그룹연산 수행할 GROUP BY 컬럼
ORDER BY 절
- RANK의 경우 필수(정렬 컬럼 밑 정렬 순서에 따라 순위 변화)
- SUM, AVG, MIN, MAX, COUNT 등은 누적값 출력 시 사용
ROWS | RANGE BETWEEN A AND B
-연산 범위 설정
-ORDER BY절 필수
*PARTITION BY, ORDER BY, ROWS...절 전달 순서 중요(ORDER BY를 PARTITION BY 전에 사용 불가)
그룹 합수의 형태를 지닌 WINDOW 함수
-SUM(), COUNT(), AVG(), MAX 등..
-OVER 절을 사용하여 윈도우 함수로 사용 가능
- 반드시 연산할 대상을 그룹함수의 입력값으로 전달
#문법
SELECT SUM(대상) OVER (
[PARTITION BY 컬럼]
[ORDER BY 컬럼 ASC | DESC]
[ROWS | RANGE BETWEEN A AND B]
);
1) SUM OVER()
-전체 총 합, 그룹별 총 합 출력가능
EX) 기존
#스칼라 서브쿼리를 이용한 출력
#SELECT를 두번에 걸쳐 가져오기 때문에 성능이 떨어짐
SELECT
EMPNO
, ENAME
, SAL
, DEPTNO
, (SELECT SUM(SAL) FROM EMP) AS TOTAL
FROM EMP;
EX) SUM OVER() 사용
#SUM OVER()사용 후 쿼리
#성능도 이전 보다 좋다
SELECT
EMPNO
, ENAME
, SAL
, DEPTNO
, SUM(SAL) OVER() AS TOTAL
FROM EMP;
2) AVG OVER() : SUM 과 동일하게 사용
SELECT
EMPNO
, ENAME
, SAL
, DEPTNO
, AVG(SAL) OVER(PARTITION BY DEPTNO) AS AVG_SAL
FROM EMP;
3) MIN/MAX OVER : SUM 과 동일하게 사용
SELECT
EMPNO
, ENAME
, SAL
, DEPTNO
, MAX(SAL) OVER(PARTITION BY DEPTNO) AS 부서별급여총합
FROM EMP;
4) COUNT OVER : SUM과 동일하게 사용한다
SELECT
EMPNO
, ENAME
, SAL
, DEPTNO
, COUNT(EMPNO) OVER(PARTITION BY DEPTNO) AS 부서별총인원
FROM EMP;
*윈도우 함수의 연산 범위 : 집계 연산 시 행의 범위 설정 가능
1. ROWS, RANGE 차이
1) ROWS : 값이 같더라도 각 행씩 연산
2) RANGE : 같은 값의 경우 하나의 TANGE 로 묶어서 동시 연산(DEFAULT)
2. BETWEEN A AND B
A) 시작점 정의
- CURRENT ROW : 현재행부터
- UNBOUNDED PRECEDING: 처음부터( DEFAULT )
- N PRECEDING : N 이전부터
B) 종료시점 정의
- CURRENT ROW : 현재행까지 (DEFAULT)
- UNBOUNDED FOLLOWING : 마지막까지
- N FOLLOWING : N 이후까지
CASE1) RANGE 범위 전달(DEFAULT) : 값이 같을 경우 같은 범위로 취급하여 동시 연산
SELECT
A.*
, SUM(SAL) OVER(ORDER BY SAL) AS SUM_SAL
FROM RANGE_TEST A| ENPNO | ENAME | DEPTNO | SAL | SUM_SAL |
| 0001 | 민우1 | 10 | 100 | 100 |
| 0002 | 민우2 | 20 | 200 | 300 |
| 0003 | 민우3 | 30 | 300 | 900 |
| 0004 | 민우4 | 30 | 300 | 900 |
| 0005 | 민우5 | 40 | 400 | 1300 |
CASE2) ROWS 범위 설정 시 : 각 행 별로 연산 수행
SELECT
A.*
, SUM(SAL) OVER (
ORDER BY SAL
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS RESULT1
FROM RANGE_TEST A;
| EMPNO | ENAME | DEPTNO | SAL | RESULT1 |
| 0001 | 민우1 | 10 | 100 | 100 |
| 0002 | 민우2 | 20 | 200 | 300 |
| 0003 | 민우3 | 30 | 300 | 600 |
| 0004 | 민우4 | 30 | 300 | 900 |
| 0005 | 민우5 | 40 | 400 | 1300 |
CASE3) BETWEEN A AND B 수정 시
SELECT
A.*
, SUM(SAL) OVER (
ORDER BY SAL
ROWS BETWEEN UNBOUNDED PRECEDING
AND 1 FOLLOWING
) AS RESULT2
FROM RANGE_TEST A;| EMPNO | ENAME | DEPTNO | SAL | RESULT2 |
| 0001 | 민우1 | 10 | 100 | 300 |
| 0002 | 민우2 | 20 | 200 | 600 |
| 0003 | 민우3 | 30 | 300 | 900 |
| 0004 | 민우4 | 30 | 300 | 1300 |
| 0005 | 민우5 | 40 | 400 | 1300 |
UNBOUNDED PRECEDING AND 1 FOLLOWING : 각 행마다 누적합 계산 시 처음부터 다음행 까지 연산
순위 관련 함수(RANK)
1.RANK(순위)
1) RANK WITHIN GROUP
-특정값에 대한 순위 확인
-윈도우함수가 아닌 일반함수
#문법
SELECT RANK(값) WITHIN GROUP(ORDER BY 컬럼);#EX) EMP테이블에서 급여가 3000이면 전체 급여 순위가 얼마인지 궁금할때
SELECT RANK(3000) WITHIN GROUP(ORDER BY SAL DESC) AS RANK_VALUE
FROM EMP;| RANK_VALUE |
| 2 |
EMP_TABLE
2) RANK() OVER()
- 전체 중/특정 그룹 중 값의 순위 확인
- ORDER BY절 필수
- 순위를 구할 대상을 ORDER BY 절에 명시( 여러 개 나열 가능 )
-그룹 내 순위 구할 시 PARTITION BY 사용
#문법
SELECT RANK() OVER([PARTITION BY 컬럼] ORDER BY 컬럼 ASC | DESC);#EX) 각 직원의 급여의 전체 순위(큰 순서대로)를 구하고 싶을때
SELECT EANME, DEPTNO, SAL,
RANK() OVER(ORDER BY SAL DESC) AS RANK_VALUE1
FROM EMP;| ENAME | DEPTNO | SAL | RANK_VALUE1 |
| 민우1 | 10 | 1000 | 1 |
| 민우2 | 20 | 800 | 2 |
| 민우3 | 20 | 800 | 2 |
| 민우4 | 20 | 700 | 4 |
| 민우5 | 30 | 600 | 5 |
| 민우6 | 10 | 500 | 6 |
| 민우7 | 30 | 300 | 7 |
#EX) 각 직원 이름, 부서번호, 급여, 부서별 급여 순위(큰 순서대로)
SELECT EANME, DEPTNO, SAL,
RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) AS RANK1
FROM EMP;| ENAME | DEPTNO | SAL | RANK1 |
| 민우1 | 10 | 1000 | 1 |
| 민우2 | 10 | 700 | 2 |
| 민우3 | 10 | 600 | 3 |
| 민우4 | 20 | 2000 | 1 |
| 민우5 | 20 | 2000 | 1 |
| 민우6 | 20 | 1000 | 3 |
| 민우7 | 20 | 800 | 4 |
| 민우8 | 20 | 300 | 5 |
3) DENSE_RANK() OVER()
- 누적 순위
- 값이 같을 떄 동일한 순위 부여 후 다음 순위가 바로 이어지는 순위 부여 방식
EX) 1등이 5명 이더라고 그 다음 순위가 2 등
4) ROW_NUMBER() OVER()
- 연속된 행 번호
- 동일한 순위를 인정하지 않고 단순히 순서대로 나열한대로의 순서 값 리턴
#EX) RANK, DENSE_RANK, ROW_NUMBER 비교
SELECT ENAME, DEPTNO, SAL,
RANK() OVER(ORDER BY SEL DESC) AS 'RANK_OVER'
DENSE_RANK() OVER(ORDER BY SAL DESC) AS 'DENSE_RANK'
ROW_NUMBER() OVER(ORDER BY SAL DESC) AS 'ROW_NUMBER'
FROM EMP;| ENAME | DEPTNO | SAL | RANK_OVER | DENSE_RANK | ROW_NUMBER |
| 민우1 | 10 | 5000 | 1 | 1 | 1 |
| 민우2 | 20 | 3000 | 2 | 2 | 2 |
| 민우3 | 20 | 3000 | 2 | 2 | 3 |
| 민우4 | 20 | 2975 | 4 | 3 | 4 |
| 민우5 | 30 | 2850 | 5 | 4 | 5 |
| 민우6 | 10 | 2450 | 6 | 5 | 6 |
| 민우7 | 30 | 1600 | 7 | 6 | 7 |
LAG, LEAD
-행 순서대로 각각 이전 값(LAG)
-행 순서대로 각각 이후 값(LEAD) 가져오기
-ORDER BY 절 필수
#문법
#SELECT LAG(컬럼,[N]) OVER([PARTITION BY 컬럼 ORDER BY 컬럼 ASC | DESC]);
SELECT LAG(컬럼 -- 가져올 값을 갖는 컬럼
,[N]) -- 몇번째 값을 가져올지(DEFAULT:1)
OVER([PARTITION BY 컬럼 --행의 이동 그룹
ORDER BY 컬럼 ASC | DESC]); --정렬컬럼#EX) EMP에서 바로 이전 입사자와 급여 비교
SELECT ENAME, HIREDATE, SAL,
LAG(SAL) OVER(ORDER BY HIREDATE) AS 바로직전상사급여
FROM EMP;| ENAME | HIREDATE | SAL | 바로직전상사급여 |
| 민우1 | 1980/12/17 00:00:00 | 800 | |
| 민우2 | 1981/02/20 00:00:00 | 1600 | 800 |
| 민우3 | 1981/02/22 00:00:00 | 1250 | 1600 |
| 민우4 | 1981/04/02 00:00:00 | 2975 | 1250 |
FIRST_VALUE, LAST_VALUE
-정렬 순서대로 정해진 범위에서의 처음 값, 마지막 값 출력
-순서와 범위 정의에 따라 최솟값과 최댓값 리턴 가능
-PARTITION BY, ORDER BY 절 생략 가능
#문법
SELECT FIRST_VALUE(대상) OVER(
[PARTITION BY 컬럼]
[ORDER BY 컬럼]
[RANGE | ROWS BETWEEN A AND B]
)#EX) FIRST_VALUE를 사용한 최소, 최대 출력
SELECT ENAME, DEPTNO, SAL,
FIRST_VALUE(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL) AS MIN_VALUE,
FIRST_VALUE(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL SESC) AS MAX_VALUE
FROM EMP;| ENAME | DEPTNO | SAL | MIN_VALUE | MAX_VALUE |
| 민우1 | 10 | 1000 | 1000 | 5000 |
| 민우2 | 10 | 5000 | 1000 | 5000 |
| 민우3 | 20 | 3000 | 3000 | 8000 |
| 민우4 | 20 | 8000 | 3000 | 8000 |
#EX) LAST_VALUE를 사용한 최소, 최대 출력
#PARTITION BY DEPTNO : 현재 부서에서
#UNBOUNDED PRECEDING : 처음부터
#UNBOUNDED FOLLOWING : 마지막까지
SELECT ENAME, DEPTNO, SAL,
LAST_VALUE(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL) AS VALUE1,
LAST_VALUE(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL
RANGE BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING) AS MAX_VALUE,
LAST_VALUE(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL DESC
RANGE BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING) AS MIN_VALUE
FROM EMP;| ENAME | DPETNO | SAL | VALUE1 | MAX_VALUE | MIN_VALUE |
| 민우1 | 10 | 1000 | 1000 | 5000 | 1000 |
| 민우2 | 10 | 5000 | 5000 | 5000 | 1000 |
| 민우3 | 20 | 3000 | 3000 | 8000 | 3000 |
| 민우4 | 20 | 8000 | 8000 | 8000 | 3000 |
NTITLE
-행을 특정 컬럼 순서에 따라 정해진 수의 그룹으로 나누기 위한 함수
-그룹 번호가 리턴굄
-ORDER BY 필수
-PARTITION BY 를 사용하여 특정 그룹을 또 원하는 수 만큼 그룹 분리 가능
-총 행의 수가 명확히 나눠지지 않을 때 앞 그룹의 크기가 더 크게 분리됨
EX) 14명 3개 그룹 분리 시 -> 5, 5, 4 로 나뉨
#문법
SELECT NTILE(N) OVER([PARTITION BY 컬럼] ORDER BY 컬럼 ASC | DESC)#EX) NTILE 을 사용한 그룹 분리
SELECT ENAME, SAL, DEPTNO,
NTILE(2) OVER (ORDER BY SAL) AS GROUP_NUMBER
FROM EMP;
| ENAME | SAL | DEPTNO | GROUP_NUMBER |
| 민우1 | 100 | 10 | 1 |
| 민우2 | 200 | 20 | 1 |
| 민우3 | 300 | 30 | 1 |
| 민우4 | 400 | 10 | 2 |
| 민우5 | 500 | 10 | 2 |
| 민우6 |
600 | 20 | 2 |
비울관련 함수
1) RATIO_TO_REPORT
- 각 값의 비율 리턴(전체 비율 또는 특정 그룹 내 비율 가능)
- ORDER BY 사용 불가
#문법
SELECT RATIO_TO_REPORT(대상) OVER([PARTITION BY 컬럼])
FROM 테이블;
2) CUME_DIST : 각 행의 수에 대한 누적비율
-특정 값이 전체 데이터 집합에서 차지하는 위치를 백분수위로 계산하여 출력
-ORDER BY 를 사용하여 누적비율을 구하는 순서 정할 수 있음
-ORDER BY 필수
-값이 3 개이면 1/3 = 0.33. 부터 시작
#문법
SELECT CUME_DIST() OVER([PARTITION BY 컬럼] ORDER BY 컬럼 ASC|DESC)
FROM 테이블;
3) PERCENT_RANK
-PERCENTILE(분위수) 출력
-전체 COUNT중 상대적 위치 출력(0~1 범위 내)
-ORDER BY 필수
#문법
SELECT PERCENT_RANK() OVER([PARTITION BY 컬럼 ORDER BY 컬럼 ASC | DESC])
FROM 테이블#EX)CUME_DIST 와 PERCENT_RANK 비교
SELECT CUME_DIST() OVER(ORDER BY SAL) AS CUME_DIST,
PERCENT_RANK() OVER(ORDER BY SAL) AS PERCENT_RANK, SAL
FROM EMP
WHERE DEPTNO = 10;| CUM_DIST | PERCENT_RANK | SAL |
| 0.333333333333333333333 | 0 | 1300 |
| 0.666666666666666666667 | 0.5 | 2450 |
| 1 | 1 | 5000 |
#누적 비율 비교
SELECT ENAME, DEPTNO, SAL,
ROUND(RATIO_TO_REPORT(SAL) OVER (PARTITION BY DEPTNO),2) AS RATE1,
ROUND(CUM_DIST(SAL) OVER (PARTITION BY DEPTNO ORDER BY SAL),2) AS RATE2
ROUND(CUM_DIST(SAL) OVER (PARTITION BY DEPTNO ORDER BY SAL, ENAME),2) AS RATE3
FROM ENP;| ENAME | DEPTNO | SAL | RATE1 | RATE2 | RATE3 |
| 민우1 | 10 | 1300 | 0.15 | 0.33 | 0.33 |
| 민우2 | 10 | 2450 | 0.28 | 0.67 | 0.67 |
| 민우3 | 10 | 5000 | 0.57 | 1 | 1 |
| 민우4 | 20 | 800 | 0.7 | 0.2 | 0.2 |
| 민우5 | 20 | 1100 | 0.1 | 0.4 | 0.4 |
| 민우6 | 20 | 2975 | 0.27 | 0.6 | 0.6 |
| 민우7 | 20 | 3000 | 0.28 | 1 | 0.8 |
| 민우8 | 20 | 3000 | 0.28 | 1 | 1 |
'SQL' 카테고리의 다른 글
| [SQL] 계층형 질의, 계층형 쿼리 (0) | 2024.08.19 |
|---|---|
| [SQL] 탑 앤 쿼리 (TOP N QUERY) (0) | 2024.08.18 |
| [SQL] 그룹함수 (ROLLUP(), CUBE(), GROUPING SETS()) (0) | 2024.08.13 |
| [SQL]테이블, 데이터 복사 쿼리 (0) | 2024.06.07 |
| DB, USER 생성 및 권한설정 (0) | 2023.11.10 |