[SQL]WINDOW함수

WINDOW함수란? : 행과 행간의 관계를 쉽게 정의하기 위해 만드는 함수

OVER()절과 함께 하용하며, 행을 그룹화(PARTIOTION), 정렬(ORDER BY), 범위 설정(ROW / RANGE)하여 다양한 함수들을 제공한다.

[쿼리 내 실행 순서]

FROM → ON → JOIN → WHERE → GROUP BY → HAVING → [윈도우 함수] → SELECT → DISTINCT → ORDER BY → LIMIT 

-- 윈도우 함수 기본문법
-- []는 생략가능
SELECT 윈도우함수(컬럼1) OVER (
    [PARTITION BY 컬럼2] -- 그룹화
    [ORDER BY 컬럼3 ASC|DESC] -- 정렬
    [ROWS|RANGE BETWEEN A AND B] -- 계산 범위
) AS 결과
FROM 테이블;

 

윈도우 함수의 진행 순서
1. 데이터를 파티션으로 나눔(그룹화)
2. 각 그룹별 데이터 정렬
3. 정렬된 데이터 내에서 연산범위 설정
4. 지정된 범위와 정렬에 따라 윈도우함수 값 계산

 

GROUP BY와의 차이? : 

GROUP BY에서 지정된 컬럼으로 데이터를 자르고, 집계함수를 이용해 집약시킨다. 따라서 결과값의 행의 수가 줄어든다.

 

원본 데이터

-- GROUP BY 예시
SELECT Country, 
	COUNT(Country) 
FROM Customer c 
GROUP BY Country;

GROUP BY 결과 값

 

 

반면 WINDOW함수를 사용하면 PARTITION BY구에 지정된 컬럼으로 데이터를 자르기만 하기 때문에 행의 수가 같다.

-- WINDOW 함수
SELECT CustomerID , 
	CustomerName ,
	Country , 
	COUNT(*) OVER(PARTITION BY Country) cnt_country
FROM Customer c 
ORDER BY 1;

WINDOW 함수 결과 값

윈도우 함수의 특징

1. 집계 함수의 확장: SUM, AVG 등의 집계 결과를 개별 행에 표시
   • 기존 집계 함수는 그룹 단위로 함수가 적용되지만, 윈도우 함수는 각 행의 데이터와 함께 집계 결과를 유지
2. 원본 데이터 유지: 원본 데이터와 함께 윈도우 함수의 결과를 동시에 확인 가능
3. 다양한 기능의 함수 제공: 순위 매기기, 누적합 계산, 특정 행 값 참조, 백분위 계산 등

---

순위 함수 :

- RANK(): 순위를 매기되, 동일한 값에 대해 같은 순위를 부여, 이후 순위를 건너뜀!

- DENSE_RANK(): 동일한 값에 대해 같은 순위를 부여, 다음 순위는 건너뛰지 않음

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

집계 함수 :

- sum(): 그룹화된 데이터(파티션 내)에 대해 누적합 계산

행(row) 참조 함수 :

• LAG(): 이전 행 값을 참조

  LAG(컬럼명, offset, default_value) OVER (PARTITION BY ... ORDER BY ...)
  -- offset: 이전행 간격(기본값은 1입니다!)
  -- default_value: 이전값이 없는 경우 넣을 기본값! (기본갑은 null)
  

  [My SQL] LAG 함수 / 현재값 vs 이전값 증감율(차이) 계산하기

 

• LEAD(): 다음 행 참조

비율 함수 :

• PERCENT_RANK(): 데이터의 백분위 순위를 계산

 

---

파티션 범위 지정

지정하는 이유: 분석을 수행할 데이터의 범위를 명확히 설정하여, 특정 행이 분석 결과에 어떤 방식으로 포함될지 결정하기 위해

 

옵션	설명
UNBOUNDED PRECEDING	윈도우의 맨 처음부터 현재 행까지 계산 (기본값)
CURRENT ROW	현재 행까지 계산
N PRECEDING	현재 행에서 N개의 이전 행까지 계산
N FOLLOWING	현재 행에서 N개의 이후 행까지 계산
BETWEEN A AND B	A에서 B까지의 범위를 지정하여 계산
UNBOUNDED FOLLOWING	윈도우의 끝(맨 마지막)까지 계산

 

예시)

SELECT 
    고객ID,
    결제ID,
    결제금액,
    AVG(결제금액) OVER (
        PARTITION BY 고객ID 
        ORDER BY 결제ID
        ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
    ) AS avg_rec_paid --최근(2가지) 평균 과금액 
FROM payments;

파티션 범위 지정 결과