Spring Boot | Window 함수로 쿼리 성능 개선 하기

1. Window 함수

Window 함수는 쿼리에서 OVER() 절과 함께 사용된다. 이 OVER() 절은 함수가 어떻게 적용될지에 대한 범위(윈도우)를 정의한다. 윈도우는 PARTITION BY, ORDER BY 등을 사용하여 특정 그룹이나 순서대로 계산할 수 있게 한다.

2. 기본 구조

<window_function> OVER (PARTITION BY <column> ORDER BY <column>)

• <window_function>: 집계 함수나 순위 함수 등.
• PARTITION BY <column>: 데이터를 그룹으로 나누어 계산. 이 부분을 생략하면 전체 테이블을 하나의 그룹으로 간주한다.
• ORDER BY <column>: 각 파티션 내에서 데이터를 정렬하여 계산.

3. 자주 사용되는 함수

ROW_NUMBER(): 각 행에 대해 순차적인 번호를 매긴다.

SELECT id, ROW_NUMBER() OVER (PARTITION BY id ORDER BY created_at) AS row_num
FROM orders;

PARTITION BY 를 써서 특정 컬럼으로 조건을 정하고 rank를 분리할 수도 있다.

 

RANK(): 순위 번호를 매기고, 동점인 경우 동일 순위를 부여한다.

SELECT id, amount, RANK() OVER (ORDER BY amount DESC) AS rank
FROM sales;

 

DENSE_RANK(): RANK()와 비슷하지만 동점인 경우에도 순위를 건너뛰지 않는다.

SELECT id, amount, DENSE_RANK() OVER (ORDER BY amount DESC) AS dense_rank
FROM sales;

 

NTILE(n): 데이터를 n개의 구간으로 나누어 각 행에 구간 번호를 부여 한다. .

SELECT id, amount, NTILE(4) OVER (ORDER BY amount) AS quartile
FROM sales;

 

SUM(), AVG(), MIN(), MAX() 등 집계 함수: 특정 윈도우 내에서 값을 집계한다.

SELECT id, amount, SUM(amount) OVER (PARTITION BY region ORDER BY date) AS running_total
FROM sales;

4. 예시

판매 데이터에서 지역별로 매출의 누적 합계를 계산하는 예시이다.

SELECT region, amount, 
       SUM(amount) OVER (PARTITION BY region ORDER BY sale_date) AS running_total
FROM sales;

• PARTITION BY region: 지역별로 데이터를 그룹화한다.
• ORDER BY sale_date: 각 지역 내에서 날짜별로 정렬하여 누적 합계를 계산한다.

5. ROWS BETWEEN 절

ROWS BETWEEN을 사용하면 윈도우 범위를 더 세밀하게 지정할 수 있다. 예를 들어, 현재 행을 기준으로 이전 3개의 행까지의 데이터를 윈도우로 설정할 수 있다.

SELECT id, amount, 
       SUM(amount) OVER (ORDER BY sale_date ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS moving_avg
FROM sales;

ROWS BETWEEN 3 PRECEDING AND CURRENT ROW: 현재 행과 이전 3개의 행을 포함하는 윈도우 범위를 설정한다.

6. 주의사항

• Window 함수는 집계 함수와는 다르게 GROUP BY와 함께 사용할 수 없다. 대신 PARTITION BY를 사용하여 그룹화한다.
• 쿼리 성능에 영향을 미칠 수 있으므로 적절한 인덱스와 함께 사용하는 것이 좋다.

7. JPQL 적용

Window 함수의 JPQL 적용 시 단점은 nativeQuery로만 동작한다는 것이다.

예를들어 아래와 같이 함수를 구성할 수 있다.

    @Query(value = """
        SELECT sub.id, sub.name, sub.height
        FROM (
            SELECT m.id, m.name, m.height
                   ROW_NUMBER() OVER (PARTITION BY m.id ORDER BY m.created_at DESC) AS row_num
            FROM member m
            WHERE m.created_at BETWEEN :start AND :end
        ) sub
        WHERE sub.row_num = 1
                """, nativeQuery = true)
    List<Object[]> getMemberData(
        @Param("start") LocalDateTime start,
        @Param("end") LocalDateTime end
    );

nativeQuery=true 옵션을 주어야 하며, 반환 값은 Object[]로 사용하여야 한다.

 

        List<Object[]> memberData = memberRepository.getMemberData(
            start, end);
        for (Object[] data : memberData) {
            result.add(new Member(
                (Integer) data[0],
                (String) data[1],
                ((Double) data[2]).floatValue()
            ));
        }

 

explain (ANALYZE) 쿼리로 성능을 비교해서 더 나은 쿼리를 사용해보자.