[TIL] 차근차근 데이터 (1) - 윈도우 함수

Today I Learned

윈도우 함수

행과 행 간 관계를 정의해 결과 집합을 계산하는 SQL 함수. 일반적인 집계 함수(SUM, AVG)가 GROUP BY를 통해 그룹으로 묶고 행 수를 줄인다면, 윈도우 함수는 기존 행의 형태와 개수를 유지하면서 주변 데이터를 참조해 계산 결과를 추가한다.

 

윈도우 함수를 이용해 복잡한 SQL 쿼리를 단순화할 수 있다. 예를 들어 일반적으로 '전체 대비 비율'이나 '누적합'을 구하려면 서브쿼리, 셀프 조인을 작성하지만 가독성이 떨어지고 쿼리도 길어지는데, 윈도우 함수를 사용하면 간단히 해결이 가능한 사례가 많다.

OVER 절을 필수 동반한다. 이를 쓰면 지금부터 윈도우 함수를 쓰겠다고 선포하는 것과 마찬가지.

SELECT 윈도우함수 OVER (
    [PARTITION BY 그룹화할칼럼]
    [ORDER BY 정렬할컬럼]
    [ROWS|RANGE 윈도우프레임지정]
) FROM 테이블명;

순위 함수

소그룹 내 특정 기준에 따라 각 행의 순위를 부여한다. 이 때 여러 옵션이 있는데, 동일 값이라고 중복 없이 순위를 부여하는 ROW_NUMBER(), 동일 값은 공동 순위를 부여하고 다음 순위를 건너뛰는 RANK(), 다음 순위를 건너뛰지 않는 DENSE_RANK()가 있다.

집계 함수

기존 집계 함수와 흡사하다. 누적합 등을 계산할 때 사용. 예를 들어 부서 별 급여가 있다고 가정해보자. SUM(급여) OVER (PARTITION BY 부서) AS 부서_급여총합을 작성하면 각 행마다 부서의 급여 총합 값이 추가로 기재된다.

SELECT 
    일자,
    당일매출,
    -- 누적 합계
    SUM(당일매출) OVER (ORDER BY 일자) AS 누적매출,
    -- 최근 3일간의 이동 평균 (현재 행 포함 직전 2행)
    AVG(당일매출) OVER (ORDER BY 일자 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS 삼일_이동평균
FROM 일별매출;

 

※ 만약 ROWS|RANGE를 생략한다면 기본 값으로 첫 행부터 현재 행까지(RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)가 자동 적용된다. 그래서 SUM 구문의 경우 ORDER BY 일자로 설정해 일자 별로 계속 더해지는 누적합을 구현할 수 있던 것. AVG 구문의 경우 현재 행 기준 앞선 2개의 행부터 현재 행까지 범위를 제한한다고 작성했으므로 3일 간의 당일 매출 평균 값을 구하는 셈이다.

행 순서 함수

현재 행을 기준으로 앞뒤 행의 값을 참조한다. 현재 행의 이전 위치는 LAG, 이후 위치는 LEAD. 파티션 내 첫 번째 행의 값은 FIRST_VALUE, 마지막 행은 LAST_VALUE다. 실무에서 LAG나 LEAD는 '전일 대비 매출 증감율' 등에 사용하고, FIRST_VALUE나 LAST_VALUE는 '사용자 행동 로그 분석'에 사용한다. 최초 클릭 상품, 이탈 직전 마지막으로 본 상품 등.

 

---

Today I Listend to