[SQLD] 2과목 "SQL 활용" - 윈도우 함수 파트 핵심 요약 정리

윈도우 함수 WINDOW FUNCTION

행과 행 사이 관계를 쉽게 정의하기 위한 함수.
순위 관련, 집계 관련, 행 순서 관련, 비율 관련 함수가 있다.

윈도우 함수 문법

SELECT 컬럼명1, 컬럼명2, ....,
WINDOW FUNCTION (<인수>) OVER
(<PARTITION BY 컬럼명> <ORDER BY 컬럼명> <WINDOWING절>)

 

- `OVER` : 필수 키워드
- `PARTITION BY` : 소그룹으로 분류. 윈도우 함수 적용 범위는 PARTITION을 넘을 수 없다.
- `ORDER BY` : 그룹 내 순서 정의
- `WINDOWING 절` : 함수의 대상이 되는 행 기준의 범위 지정

 PARTITION BY / ORDER BY는 띄어쓰기로 구분한다.
윈도우 함수는 SELECT문 내부에 작성하기 때문에 앞선 컬럼 뒤에 콤마(`,`)를 붙여줘야 함에 주의!

순위 관련 함수

`RANK()` : 동일한 값이면 같은 순위. 공동 순위가 있으면 다음 순위는 해당 등수를 제거하고 부여 (1등, 1등, 3등, ...)
`DENSE_RANK()` : 동일한 값이면 같은 순위. 공동 순위에 관계없이 누적 순위 부여 (1등, 1등, 2등, ...)
`ROW_NUMBER()` : 동일한 값이어도 다른 순위. 고유한 순위 부여

순위 함수는 순위를 매기고 정렬할 기준이 필요하기 때문에 ORDER BY가 필수적으로 사용된다.

-- 순위 관련
-- RANK, DENSE_RANK, ROW_NUMBER

SELECT EMPNO, ENAME, SAL, DEPTNO,
RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) RANK,
DENSE_RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) RANK,
ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) RANK
FROM EMP;

집계 관련 함수

`SUM` 합계, `COUNT` 행 개수, `AVG` 평균, `MAX` 최댓값, `MIN` 최솟값

집계 함수를 사용하려면 어떤 값을 계산할 건지를 알려줘야 하기 때문에 함수에 인수는 필수로 넣어줘야 한다.
`OVER(ORDER BY )`는 누적 개념으로 사용되며, 동일한 값이 여러 번 나올 경우 한 번에 계산된다.

전체 합계, 누적합계, 그룹별합계, 그룹별 누적합계 결과

-- 집계 관련
-- SUM, COUNT, AVG, MAX, MIN

-- ENAME, EMPNO, SAL, 전체급여 합계
SELECT ENAME, EMPNO, SAL, (SELECT SUM(SAL) FROM EMP) SUM_ALL FROM EMP;  -- 서브쿼리로 구하기
SELECT ENAME, EMPNO, SAL, SUM(SAL)OVER() SUM_ALL FROM EMP;		-- 윈도우함수로 구하기


-- 전체 합계, 누적합계, 그룹별합계, 그룹별 누적합계 구하기
SELECT ENAME, EMPNO, SAL,
SUM(SAL)OVER() SUM_ALL,                                   -- 전체 합계
SUM(SAL)OVER(ORDER BY SAL) ACC_SUM,                       -- 누적합계
SUM(SAL)OVER(PARTITION BY DEPTNO) G_SUM,                  -- 그룹별 합계
SUM(SAL)OVER(PARTITION BY DEPTNO ORDER BY SAL) G_ACC_SUM  -- 그룹별 누적합계
FROM EMP;


-- COUNT
SELECT EMPNO, ENAME, SAL, COUNT(*)OVER() FROM EMP; -- 행 개수 세기

행 순서 관련 함수

`FIRST_VALUE()` : 해당 컬럼의 가장 앞에 있는 값을 가져온다.
`LAST_VALUE()` : 해당 컬럼의 가장 뒤에 있는 값을 가져온다.
`LAG()` : n번째 이전행(앞 행)을 가져온다. default = 1
`LEAD()` : n번째 이후행(다음 행)을 가져온다. default = 1

LAG와 LEAD는 이전 행과 이후 행이라는 기준이 필요하기 때문에 무조건 정렬해서 조회해야 한다. (ORDER BY 필수)

LAG 함수 사용 결과

-- 행 순서 관련

-- FIRST_VALUE : 가장 앞에 있는 값
SELECT EMPNO, ENAME, SAL, DEPTNO,
FIRST_VALUE(SAL)OVER(ORDER BY SAL) FIRST1,
FIRST_VALUE(SAL)OVER(PARTITION BY DEPTNO ORDER BY SAL) FIRST2
FROM EMP;

-- LAST_VALUE : 가장 뒤에 있는 값
SELECT EMPNO, ENAME, SAL,
LAST_VALUE(SAL)OVER() LAST1,  -- 컬럼 전체의 마지막 값
LAST_VALUE(SAL)OVER(ORDER BY SAL) LAST2,  -- 누적의 마지막 값
LAST_VALUE(SAL)OVER(PARTITION BY DEPTNO) LAST3,
LAST_VALUE(SAL)OVER(PARTITION BY DEPTNO ORDER BY SAL) LAST4
FROM EMP
ORDER BY DEPTNO;

-- LAG : n번째 이전행(앞 행)
SELECT EMPNO, ENAME, SAL, DEPTNO,
LAG(SAL)OVER(ORDER BY SAL) LAG,
LAG(ENAME)OVER(ORDER BY SAL) LAG_NAME,
LAG(SAL, 0)OVER(ORDER BY SAL) LAG0, -- 0개 앞, 그냥 나 자신
LAG(SAL, 1)OVER(ORDER BY SAL) LAG1, -- 1개 앞
LAG(SAL, 2)OVER(ORDER BY SAL) LAG2  -- 2개 앞
FROM EMP;

-- LEAD : 이후행(다음 행)
SELECT EMPNO, ENAME, SAL, DEPTNO,
LEAD(SAL)OVER(ORDER BY SAL) LEAD FROM EMP;

비율 관련 함수

`CUME_DIST` : 순위가 0~1 사이 중 어느 위치인지 파악
    - 순위와 관계가 있기 때문에 ORDER BY가 반드시 필요함, 인수는 필요 없음

`RATIO_TO_REPORT` : 값이 전체 중에 차지하는 비중
    - ORDER BY는 없어도 됨, 다만 인수는 필수 (어떤 값을 계산?)

`PERCENT_RANK` : 순서별 백분율 값을 0~1 사이 값으로 반환

`NTILE` : 내가 원하는 개수로 행을 묶어서 소그룹 분류

-- 비율 관련 함수
-- CUME_DIST, RATIO_TO_REPORT, PERCENT_RANK, NTILE

-- CUME_DIST : 순위가 0~1 사이 중 어느 위치인지 파악
SELECT EMPNO, ENAME, DEPTNO, SAL,
ROUND(CUME_DIST()OVER(ORDER BY SAL), 3) CUME1,
ROUND(CUME_DIST()OVER(ORDER BY SAL), 3)*100||'%' CUME2  -- 백분율
FROM EMP;

-- RATIO_TO_REPORT : 값이 전체 중에 차지하는 비중
SELECT EMPNO, ENAME, DEPTNO, SAL,
ROUND(RATIO_TO_REPORT(SAL)OVER(), 3) RATIO1,
ROUND(RATIO_TO_REPORT(SAL)OVER(), 3)*100||'%' RATIO2
FROM EMP;

-- PERCENT_RANK
SELECT EMPNO, ENAME, DEPTNO, SAL,
ROUND(CUME_DIST()OVER(ORDER BY SAL), 3) CUME1,
ROUND(PERCENT_RANK()OVER(ORDER BY SAL), 3)*100||'%' PERCENT1
FROM EMP;

-- NTILE : 소그룹 분류
-- 급여 오름차순을 기준으로 4개 그룹으로 분류 (정렬 후! 잘라서 분류)
SELECT EMPNO, ENAME, DEPTNO, SAL,
NTILE(4)OVER(ORDER BY SAL) NTILE1,
NTILE(4)OVER(PARTITION BY DEPTNO ORDER BY SAL) NTILE2
FROM EMP;

 

WINDOWING 절 문법

- `PRECEDING` : 이전행(LAG(컬럼명), n)
- `FOLLOWING` : 다음행(LEAD(컬럼명), n)
- `UNBOUNDED PRECEDING` : 첫 번째 행부터~
- `UNBOUNDED FOLLOWING` : ~마지막 행까지
- `CURRENT ROW` : 현재 행
- `ROWS` : 행 수
- `RANGE` : 값의 범위
- `BETWEEN` : 값의 범위. 비교연산자는 안 씀

-- 현재행과 앞, 뒤 1행의 합

-- 1) 세 개 값을 모두 더하기
SELECT LAG(SAL)OVER(ORDER BY SAL)+SAL+LEAD(SAL)OVER(ORDER BY SAL) SUM FROM EMP;
--> 맨 앞 또는 맨 뒤에 NULL이 포함되어 계산 결과를 알 수 없는 경우가 생김

-- 2) 윈도우 함수 사용 (NULL 무시)
SELECT EMPNO, ENAME,
LAG(SAL)OVER(ORDER BY SAL) LAG, -- 이전 행
SAL,
LEAD(SAL)OVER(ORDER BY SAL) LEAD, -- 이후 행

SUM(SAL)OVER(ORDER BY SAL ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) SUM, -- 앞에 하나 + 현재 행 + 뒤에 하나
SUM(SAL)OVER(ORDER BY SAL ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING) SUM0, -- 맨 앞부터 뒤에 하나
SUM(SAL)OVER(ORDER BY SAL ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) SUM1, -- 맨 앞부터 현재 행까지
SUM(SAL)OVER(ORDER BY SAL ROWS UNBOUNDED PRECEDING) SUM2,  -- 맨 앞부터 현재 행까지 (CURRENT ROW 생략 가능)
SUM(SAL)OVER(ORDER BY SAL ROWS 1 PRECEDING) SUM3,  -- 앞에 하나 + 현재 행
SUM(SAL)OVER(ORDER BY SAL ROWS BETWEEN UNBOUNDED PRECEDING AND 0 FOLLOWING) SUM5 -- 맨 앞부터 나까지
FROM EMP;

-- 급여의 합을 구하는 법
SELECT EMPNO, ENAME, SAL,
-- 1) 집계 윈도우 함수
SUM(SAL)OVER(),

-- 2) 집계 함수
(SELECT SUM(SAL) FROM EMP),

-- 3) 행을 처음부터 끝까지 선택한 후 정렬
SUM(SAL)OVER(ORDER BY SAL ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
FROM EMP;

-- 값의 범위 : RANGE, BETWEEN
SELECT EMPNO, ENAME, SAL,
SUM(SAL)OVER(ORDER BY SAL RANGE BETWEEN 100 PRECEDING AND 100 FOLLOWING) SUM, -- 현재 행의 값에서 +-100 범위에 해당하는 값들의 합
SUM(SAL)OVER(ORDER BY SAL RANGE UNBOUNDED PRECEDING) SUM1 -- 누적합계, 중복되는 값은 한 번에 계산
--SUM(SAL)OVER(ORDER BY SAL RANGE UNBOUNDED FOLLOWING) SUM2  -- 불가
FROM EMP;