[SQL] 윈도우 함수

 

개념

각 행을 그대로 유지하면서, 여러 행을 함께 비교하거나 계산하는 함수

 

비교 : GROUP BY

구분	GROUP BY	윈도우 함수
행 개수	여러 행이 하나로 합쳐질 수 있음	원래 행을 유지
개별 행 정보	사라질 수 있음	유지됨
계산 결과	그룹별 한 행	각 행 옆에 표시
주요 용도	그룹별 요약	순위, 누적 합계, 행 비교

 

---

 

문법

윈도우_함수() OVER ( -- 원래 행을 유지하면서 계산 결과를 각 행 옆에 표시
-- 어떤 행들을 기준으로 계산할지 정하는 부분 
    [PARTITION BY 그룹_컬럼] -- 같은 그룹_컬럼끼리 나누어 계산
    [ORDER BY 정렬_컬럼] -- 정렬_컬럼 순서에 따라 누적해서 계산 
)

 

EX] 회원별 주문 금액을 주문 순서대로 누적

/**
orders)
| id | user_id | total_price |
| -: | ------: | ----------: |
|  1 |       1 |       30000 |
|  2 |       1 |       50000 |
|  3 |       2 |       20000 |
|  4 |       2 |       70000 |
*/

SELECT
    id,
    user_id,
    total_price,
    SUM(total_price) OVER (
        PARTITION BY user_id
        ORDER BY id
    ) AS user_accumulated_price
FROM orders
ORDER BY user_id, id;

/** 결과
| id | user_id | total_price | user_accumulated_price |
| -: | ------: | ----------: | ---------------------: |
|  1 |       1 |       30000 |                  30000 |
|  2 |       1 |       50000 |                  80000 |
|  3 |       2 |       20000 |                  20000 |
|  4 |       2 |       70000 |                  90000 |

*/

 

---

 

주요 윈도우 함수

무엇을 계산하는지에 따라 

종류	주요 함수	역할
순위 함수	ROW_NUMBER(), RANK(), DENSE_RANK()	행에 순번이나 순위 부여
집계 함수	SUM(), AVG(), COUNT(), MIN(), MAX()	합계, 평균, 개수 등 계산
이전·다음 행 함수	LAG(), LEAD()	현재 행을 기준으로 이전·다음 행의 값 조회
위치 기준 값 함수	FIRST_VALUE(), LAST_VALUE()	계산 범위에서 첫 번째·마지막 값 조회

 

순위 함수

ORDER BY로 정한 순서에 따라 각 행에 번호나 순위를 부여

ORDER BY score ASC|DESC

함수	같은 값이 있을 때
ROW_NUMBER()	같은 값이어도 서로 다른 번호 부여
RANK()	같은 순위 부여, 다음 순위는 건너뜀
DENSE_RANK()	같은 순위 부여, 다음 순위를 이어서 부여

 

EX] 점수에 따른 순위 비교

/* 
scores)

| id | name | score |
| -: | ---- | ----: |
|  1 | 김개발  |   100 |
|  2 | 이개발  |    90 |
|  3 | 박개발  |    90 |
|  4 | 최개발  |    80 |
|  5 | 정개발  |    70 |
*/
SELECT
    id,
    name,
    score,

    ROW_NUMBER() OVER (
        ORDER BY score DESC
    ) AS row_number_result,

    RANK() OVER (
        ORDER BY score DESC
    ) AS rank_result,

    DENSE_RANK() OVER (
        ORDER BY score DESC
    ) AS dense_rank_result

FROM scores
ORDER BY score DESC, id;

/** 결과
| name | score | `ROW_NUMBER()` | `RANK()` | `DENSE_RANK()` |
| ---- | ----: | -------------: | -------: | -------------: |
| 김개발  |   100 |              1 |        1 |              1 |
| 이개발  |    90 |              2 |        2 |              2 |
| 박개발  |    90 |              3 |        2 |              2 |
| 최개발  |    80 |              4 |        4 |              3 |
| 정개발  |    70 |              5 |        5 |              4 |
*/

 

집계 윈도우 함수

원래 행을 유지하면서 여러 행의 합계, 평균, 개수, 최솟값, 최댓값을 계산하는 함수

함수	의미
SUM()	합계
AVG()	평균
COUNT()	개수
MIN()	최솟값
MAX()	최댓값

 

EX] 회원별 주문 통계 조회

/**
orders)
| id | user_id | total_price |
| -: | ------: | ----------: |
|  1 |       1 |       30000 |
|  2 |       1 |       50000 |
|  3 |       2 |       20000 |
|  4 |       2 |       70000 |
|  5 |       2 |       60000 |
*/
SELECT
    id,
    user_id,
    total_price,

    SUM(total_price) OVER (
        PARTITION BY user_id
    ) AS user_total_price,

    AVG(total_price) OVER (
        PARTITION BY user_id
    ) AS user_average_price,

    COUNT(*) OVER (
        PARTITION BY user_id
    ) AS user_order_count,

    MIN(total_price) OVER (
        PARTITION BY user_id
    ) AS user_min_price,

    MAX(total_price) OVER (
        PARTITION BY user_id
    ) AS user_max_price

FROM orders
ORDER BY user_id, id;

/** 결과
| id | user_id | total_price | user_total_price | user_average_price | user_order_count | user_min_price | user_max_price |
| -: | ------: | ----------: | ---------------: | -----------------: | ---------------: | -------------: | -------------: |
|  1 |       1 |       30000 |            80000 |              40000 |                2 |          30000 |          50000 |
|  2 |       1 |       50000 |            80000 |              40000 |                2 |          30000 |          50000 |
|  3 |       2 |       20000 |           150000 |              50000 |                3 |          20000 |          70000 |
|  4 |       2 |       70000 |           150000 |              50000 |                3 |          20000 |          70000 |
|  5 |       2 |       60000 |           150000 |              50000 |                3 |          20000 |          70000 |
*/

SELECT
    id,
    user_id,
    total_price,
    SUM(total_price) OVER (
        PARTITION BY user_id
        ORDER BY id
    ) AS user_accumulated_price
FROM orders
ORDER BY user_id, id;
/** 결과
| id | user_id | total_price | user_accumulated_price |
| -: | ------: | ----------: | ---------------------: |
|  1 |       1 |       30000 |                  30000 |
|  2 |       1 |       50000 |                  80000 |
|  3 |       2 |       20000 |                  20000 |
|  4 |       2 |       70000 |                  90000 |
|  5 |       2 |       60000 |                 150000 |
*/

 

이전 · 다음 행 함수

현재 행을 기준으로 앞이나 뒤에 있는 행의 값을 가져오는 함수

ORDER BY 부분이 중요함

함수	역할
LAG(조회컬럼, 이동할_행_수, 기본값)	이전 행의 값 조회
LEAD( 조회컬럼, 이동할_행_수, 기본값)	다음 행의 값 조회

 

기본값 = 이전 또는 다음 행이 없을 때 반환할 값 지정 

 

EX]

/**
orders
| id | user_id | total_price |
| -: | ------: | ----------: |
|  1 |       1 |       30000 |
|  2 |       1 |       50000 |
|  3 |       1 |       40000 |
|  4 |       2 |       20000 |
|  5 |       2 |       70000 |

-- 회원별 이전/다음 주문 금액 조회
SELECT
    id,
    user_id,
    total_price,

    LAG(total_price) OVER (
        PARTITION BY user_id
        ORDER BY id
    ) AS previous_price,

    LEAD(total_price) OVER (
        PARTITION BY user_id
        ORDER BY id
    ) AS next_price

FROM orders
ORDER BY user_id, id;

결과)
| id | user_id | total_price | previous_price | next_price |
| -: | ------: | ----------: | -------------: | ---------: |
|  1 |       1 |       30000 |         `NULL` |      50000 |
|  2 |       1 |       50000 |          30000 |      40000 |
|  3 |       1 |       40000 |          50000 |     `NULL` |
|  4 |       2 |       20000 |         `NULL` |      70000 |
|  5 |       2 |       70000 |          20000 |     `NULL` |
*/

-- 이전 행과 현재 행의 차이 계산
SELECT
    id,
    user_id,
    total_price,

    LAG(total_price) OVER (
        PARTITION BY user_id
        ORDER BY id
    ) AS previous_price,

    total_price
        - LAG(total_price) OVER (
            PARTITION BY user_id
            ORDER BY id
        ) AS price_difference

FROM orders
ORDER BY user_id, id;
/** 결과
| id | user_id | total_price | previous_price | price_difference |
| -: | ------: | ----------: | -------------: | ---------------: |
|  1 |       1 |       30000 |         `NULL` |           `NULL` |
|  2 |       1 |       50000 |          30000 |            20000 |
|  3 |       1 |       40000 |          50000 |           -10000 |
|  4 |       2 |       20000 |         `NULL` |           `NULL` |
|  5 |       2 |       70000 |          20000 |            50000 |
*/

 

위치 기준 값 함수

정렬된 계산 범위에서 특정 위치에 있는 값을 가져오는 함수

함수	역할
FIRST_VALUE()	계산 범위의 첫 번째 행 값 조회
LAST_VALUE()	계산 범위의 마지막 행 값 조회

 

FIRST|LAST_VALUE(조회_컬럼) OVER (
    [PARTITION BY 그룹_컬럼]
    ORDER BY 정렬_컬럼
    [ROWS BETWEEN 시작_위치 AND 종료_위치]
)

 

범위 지정 표현

표현	의미
UNBOUNDED PRECEDING	그룹의 첫 번째 행
n PRECEDING	현재 행보다 앞에 있는 n개 행
CURRENT ROW	현재 행
n FOLLOWING	현재 행보다 뒤에 있는 n개 행
UNBOUNDED FOLLOWING	그룹의 마지막 행

 

자주 사용하는 프레임

프레임	계산 범위	주요 용도
UNBOUNDED PRECEDING → CURRENT ROW	첫 행부터 현재 행	누적 합계
UNBOUNDED PRECEDING → UNBOUNDED FOLLOWING	그룹 전체	첫 번째·마지막 값
n PRECEDING → CURRENT ROW	앞의 일정 행부터 현재 행	이동 평균
n PRECEDING → n FOLLOWING	현재 행의 앞뒤 일정 범위	주변 행 계산

 

EX]

-- EX] 회원별 첫 주문 금액과 마지막 주문 금액

/**
원본 테이블)

orders)
| id | user_id | total_price |
| -: | ------: | ----------: |
|  1 |       1 |       30000 |
|  2 |       1 |       50000 |
|  3 |       1 |       40000 |
|  4 |       2 |       20000 |
|  5 |       2 |       70000 |
*/

SELECT
    id,
    user_id,
    total_price,

    FIRST_VALUE(total_price) OVER (
        PARTITION BY user_id
        ORDER BY id
    ) AS first_order_price,

    LAST_VALUE(total_price) OVER (
        PARTITION BY user_id -- 회원별로 주문을 나눔
        ORDER BY id -- 각 회원의 주문을 id 순서로 정렬 
        ROWS BETWEEN UNBOUNDED PRECEDING -- 그룹의 첫 번째 행부터
                 AND UNBOUNDED FOLLOWING -- 그룹의 마지막 행까지 
         	-- 범위를 생략하면 기대와 다른 결과가 나올 수 있음
            -- => 그룹의 첫 번째 행 ~ 현재 행까지 (현재 행 자신의 값)
    ) AS last_order_price

FROM orders
ORDER BY user_id, id;

/** 결과
| id | user_id | total_price | first_order_price | last_order_price |
| -: | ------: | ----------: | ----------------: | ---------------: |
|  1 |       1 |       30000 |             30000 |            40000 |
|  2 |       1 |       50000 |             30000 |            40000 |
|  3 |       1 |       40000 |             30000 |            40000 |
|  4 |       2 |       20000 |             20000 |            70000 |
|  5 |       2 |       70000 |             20000 |            70000 |

*/

 

 

---

 

사용 시 주의사항

 

윈도우 함수 결과를 WHERE에서 바로 사용할 수 없음

SQL의 논리적인 처리 순서에서 WHERE가 윈도우 함수보다 먼저 처리되기 때문

윈도우 함수 결과를 조건으로 사용하려면 서브쿼리는 CTE로 한 번 감싸야 함. 

 

EX]

WITH ranked_orders AS (
    SELECT
        id,
        user_id,
        total_price,
        ROW_NUMBER() OVER (
            PARTITION BY user_id
            ORDER BY id DESC
        ) AS row_number
    FROM orders
)
SELECT
    id,
    user_id,
    total_price
FROM ranked_orders
WHERE row_number = 1;

 

같은 정렬값이 있으면 결과 순서가 달라질 수 있음

항상 같은 순서를 원한다면 고유한 컬럼을 추가한다. 

특히 ROW_NUMBER()로 그룹별 한 행을 선택할 때 중요함

 

EX]

-- 다음 쿼리는 점수가 같은 행의 순서를 확실하게 정하지 않음
ROW_NUMBER() OVER (
    ORDER BY score DESC
)

/**
원본 데이터가 다음과 같다면

| id | name | score |
| -: | ---- | ----: |
|  1 | 김개발  |   100 |
|  2 | 이개발  |    90 |
|  3 | 박개발  |    90 |

90점인 두 행 중 어느 행이 먼저 처리될지 명확하지 않음
*/

-- 고유한 컬럼을 추가
ROW_NUMBER() OVER (
    ORDER BY score DESC, id ASC
)

 

윈도우 함수 내부 정렬은 최종 출력 순서를 보장하지 않음

윈도우 함수의 계산 순서만 정하는 것. 

출력 순서까지 지정하려면 쿼리 마지막에 ORDER BY 별도 작성

 

EX]

SELECT
    id,
    total_price,
    SUM(total_price) OVER (
        ORDER BY id
    ) AS accumulated_price
FROM orders
ORDER BY id;

 

윈도우 함수 내부에 ORDER BY작성시, 기본 프레임을 확인해야 함

집계함수는 일반적으로 전체 합계가 아니라 첫 행부터 현재 행까지의 누적 결과를 반환함. 

의도를 명확하게 하려면 프레임을 직접 작성하는 게 좋음

 

EX]

-- 누적계산
SUM(계산_컬럼) OVER (
    ORDER BY 정렬_컬럼
    ROWS BETWEEN UNBOUNDED PRECEDING
             AND CURRENT ROW
)

-- 전체 범위 계산
SUM(계산_컬럼) OVER (
    ORDER BY 정렬_컬럼
    ROWS BETWEEN UNBOUNDED PRECEDING
             AND UNBOUNDED FOLLOWING
)

 

 

데이터를 언제 필터링하는지에 따라 결과가 달라짐

윈도우 함수를 계산하기 전에 WHERE로 행을 제외하면, 제외된 행은 윈도우 계산에도 포함되지 않는다. 

전체 데이터를 계산한 뒤 결과를 필터링하려면 CTE나 서브쿼리 사용

 

EX]

WITH order_totals AS ( -- 전체 주문을 대상으로 회원별 합계 계산
    SELECT
        id,
        user_id,
        total_price,
        SUM(total_price) OVER (
            PARTITION BY user_id
        ) AS user_total_price
    FROM orders
)
-- 계산이 긑난 결과에서 필요한 행만 조회
SELECT
    id,
    user_id,
    total_price,
    user_total_price
FROM order_totals
WHERE total_price >= 50000;

 

 

윈도우 함수가 많으면 정렬 비용이 커질 수 있음

윈도우 함수의 PARTITION BY와 ORDER BY는 데이터를 나누고 정렬해야 하므로 데이터가 많으면 비용이 커질 수 있다. 

특히 서로 다른 정렬 기준을 여러 번 사용하면 각각 별도의 정렬 작업이 필요할 수 있음.

 

필요하지 않은 윈도우 함수를 한꺼번에 추가하지 않고, 조회 범위와 정렬 기준을 명학하게 사용하는 것이 좋음.