Window함수 정리하기

Jun 22, 2023

Contents

윈도우 함수 톺아보기

1. 어떻게 생겼어용?

📌 SELECT WINDOW_FUNCTION (ARGUMENTS) OVER([PARTITION BY 컬럼] [ORDER BY 컬럼] [WINDOWING 절]) FROM 테이블명;

어떤 함수를 쓸 수 있나요?

집계 함수


AVG()
BIT_AND()
BIT_OR()
BIT_XOR()
COUNT()
JSON_ARRAYAGG()
JSON_OBJECTAGG()
MAX()
MIN()
STDDEV_POP(), STDDEV(), STD()
STDDEV_SAMP()
SUM()
VAR_POP(), VARIANCE()
VAR_SAMP()

Window에서만 사용가능한 함수 (순위, 그룹 내 행 순서, 그룹 내 비율)


CUME_DIST()
DENSE_RANK()
FIRST_VALUE()
LAG()
LAST_VALUE()
LEAD()
NTH_VALUE()
NTILE()
PERCENT_RANK()
RANK()
ROW_NUMBER()

2. 어떤 케이스에 사용해용?

2.1. 순위 함수

RANK : ORDER BY를 포함한 쿼리문에서 특정 컬럼의 순위를 구하는 함수

DENSE_RANK : 동일한 값에 대해서는 같은 순위를 부여하고 중간 순위를 비우지 않음 (ex 1,1,2,3,3,4)

ROW_NUMBER : 동일한 값에도 고유한 순위를 부여

예시 - 순위 정하기


SELECT 
	val,
	ROW_NUMBER() OVER (ORDER BY val) AS 'row_number',
	RANK() OVER (ORDER BY val) AS 'rank',
	DENSE_RANK() OVER (ORDER BY val) AS 'dense_rank'
FROM sample

2.2. 일반 집계 함수

예시 - 누적합 구하기

Window 함수 사용ver


SELECT SUM(kg) OVER (ORDER BY Line) AS CumSum

JOIN 활용

서브쿼리 ⭐️

2.3. 그룹 내 행 순서 함수

FIRST_VALUE : 파티션별 윈도우에서 가장 먼저 나온 값을 구함. 공동 등수 인정안함. MIN함수 쓰는 것과 결과 동일

LAST_VALUE

LEAD

예시 - 데이터 위치 바꾸기


SELECT LAG(Temperature) OVER (ORDER BY RecordDate) AS 'lag',
LEAD(Temperature) OVER (ORDER BY RecordDate) AS 'lead'
FROM sample

# 2칸씩 밀고, null값은 0으로 채워줘
LAG(Temperature, 2, 0)

2.4. 그룹 내 비율 함수

RATIO_TO_REPORT : 전체 SUM값에 대한 행별 컬럼 값의 백분율을 소수점으로 출력

PERCENT_RANK : 파티션별로 가장 먼저 나오는 값을 0, 가장 마지막에 나오는 값을 1로 해서 행 순서별 백분율 출력


SELECT DEPTNO, ENAME, SAL
     , PERCENT_RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) as P_R 
  FROM EMP; 

    DEPTNO ENAME             SAL        P_R
--------- ---------- ---------- ----------
10 KING             5000          0
        10 CLARK            2450         .5
        10 MILLER           1300          1
        20 SCOTT            3000          0
        20 FORD             3000          0
        20 JONES            2975         .5
        20 ADAMS            1100        .75
        20 SMITH             800          1
        30 BLAKE            2850          0
        30 ALLEN            1600         .2
        30 TURNER           1500         .4
        30 MARTIN           1250         .6
        30 WARD             1250         .6
        30 JAMES             950          1

CUME_DIST : 현재 행보다 작거나 같은 건수에 대한 누적백분율