[row/between] 프레임 정의

'row'와 'between'의 개념

• ROWS: 물리적인 행 단위로 윈도우 프레임을 정의합니다. 즉, 현재 행을 기준으로 몇 개의 이전 또는 이후 행을 포함할지 결정합니다.
• BETWEEN: 프레임의 시작과 끝을 명시적으로 지정하는 데 사용됩니다. BETWEEN 절을 사용하면 윈도우 내에서 어느 범위까지 데이터를 포함할지 설정할 수 있습니다.

프레임의 경계(Boundaries)

• UNBOUNDED PRECEDING: 파티션 내에서 첫 번째 행부터 현재 행까지를 포함합니다.
• CURRENT ROW: 현재 행만 포함합니다.
• n PRECEDING: 현재 행 이전의 n개의 행을 포함합니다.
• n FOLLOWING: 현재 행 이후의 n개의 행을 포함합니다.
• UNBOUNDED FOLLOWING: 현재 행부터 파티션 내 마지막 행까지 포함합니다.

rows between 사용 예시

• 기본적 rows between

SELECT
    time,
    subject,
    val,
    SUM(val) OVER (PARTITION BY subject 
    		  ORDER BY time 
                    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS running_total
FROM observations;

# 이 쿼리는 각 subject별로 데이터를 time 순서대로 정렬하고, 현재 행과 그 이전 두 개의 행에 대해 합계를 계산합니다

 

• 다양한 경계 사용

SELECT
    time,
    subject,
    val,
    AVG(val) OVER (PARTITION BY subject 
    		ORDER BY time 
            	ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_avg
FROM observations;

# 각 subject에 대해 첫 번째 행부터 현재 행까지의 평균을 계산합니다. UNBOUNDED PRECEDING은 파티션의 시작부터 현재까지 모든 행을 포함하는 것을 의미합니다

• 프레임 확장 없이 'rows'

SELECT
    product,
    date,
    revenue,
    AVG(revenue) OVER (PARTITION BY product 
    			ORDER BY date 
                	ROWS 2 PRECEDING) AS moving_avg
FROM sales;

# 각 제품별로 날짜 순서대로 정렬한 후, 현재 행과 그 이전 두 개의 행에 대해 이동 평균을 계산합니다

 

range between 사용 예시

• 기본적인 range between

SELECT
    visited_on,
    amount,
    SUM(amount) OVER (
        ORDER BY visited_on
        RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW
    ) AS running_total
FROM customer;

# 이 쿼리는 visited_on 날짜를 기준으로 현재 행과 그 이전 6일 동안의 amount 합계를 계산합니다.
# 논리적 범위인 날짜 간격(INTERVAL 6 DAY)을 사용하여 프레임을 정의하고, 그 범위 내에 있는 모든 값을 합산합니다.

• 누적 합계 계산

SELECT
    date,
    revenue,
    SUM(revenue) OVER (
        ORDER BY date
        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_revenue
FROM sales;

# 이 쿼리는 각 날짜별로 첫 번째 날짜부터 현재 날짜까지의 누적 매출(revenue)을 계산합니다.
# UNBOUNDED PRECEDING은 파티션 내에서 첫 번째 날짜부터 현재 행까지 모든 값을 포함하는 것을 의미합니다.

• 이동 평균 계산

SELECT
    date,
    price,
    AVG(price) OVER (
        ORDER BY date
        RANGE BETWEEN INTERVAL 3 DAY PRECEDING AND CURRENT ROW
    ) AS moving_avg
FROM stock_prices;

# 이 쿼리는 각 날짜별로 현재 날짜와 그 이전 3일 동안의 주가 평균을 계산합니다.
# 날짜를 기준으로 논리적인 범위를 설정하여, 해당 기간 내에 있는 모든 가격 데이터를 평균화합니다.

 

차이점 'rows between' vs 'range between'

특성	rows between	range between
기준	물리적인 행의 수	논리적인 값(숫자, 날짜 등)
사용 예시	이동합계, 이동평균 등	날짜 간격, 값 차이에 따른 집계
성능	일반적으로 더 빠름	논리적 비교가 필요하므로 상대적으로 느릴 수 있음
지원되는 경계	'n preceding' 'current row' 'n following' etc	동일한 경계를 지원

주의사항

• 정렬된 열 필수: RANGE BETWEEN은 반드시 정렬된 열(주로 숫자나 날짜)을 기준으로 작동해야 합니다. 그렇지 않으면 오류가 발생하거나 예상치 못한 결과를 반환할 수 있습니다.
• 데이터 타입 제한: RANGE BETWEEN은 정렬된 열이 숫자 또는 날짜/시간 데이터여야 하며, 문자열이나 기타 데이터 유형에서는 사용할 수 없습니다.
• 성능 고려: 논리적 비교를 수행해야 하기 때문에, 특히 큰 데이터셋에서는 성능이 저하될 수 있습니다. 성능 최적화를 위해 인덱스 설정 등을 고려해야 합니다.
• 프레임 경계 설정: BETWEEN 절에서 상한과 하한이 명확히 정의되어야 하며, 상한이 하한보다 작아서는 안 됩니다
• 물리적 vs 논리적 프레임: ROWS는 물리적인 행 단위로 프레임을 정의하지만, RANGE는 논리적인 값 범위를 기준으로 프레임을 정의합니다