ROWS & RANGE Criterion

Coddy SQL 여정의 기초 섹션에 포함된 레슨 — 72개 중 69번째.

지금까지는 고려할 행의 앞뒤 개수를 선택하는 데 유연하게 대응할 수 없었습니다. 이제 ROWS & RANGE 기준으로 가능합니다. 이를 사용하려면 다음과 같이 작성합니다:

OVER (ROWS BETWEEN --START-- AND --END--)

OVER (RANGE BETWEEN --START-- AND --END--)

다음 옵션을 지정할 수 있습니다:

CURRENT ROW - 현재 행
n PRECEDING - 현재 행 이전의 행들
n FOLLOWING - 현재 행 이후의 행들

ROWS & RANGE의 차이점은 ROWS 기준이 값은 신경 쓰지 않고 위치만 고려하는 반면, RANGE는 행 위치가 아닌 값 범위로 윈도우를 정의한다는 점입니다.

RANGE의 경우 반드시 ORDER BY --column_name--을 지정해야 합니다. 그렇지 않으면 창을 선택하는 방법을 알 수 없기 때문입니다.

예를 들어:

ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING

여기서는 현재 행, 이전 행, 그리고 다음 행을 포함하는 윈도우를 생성합니다.

RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING ORDER BY levels

여기서는 각 레벨(오름차순으로 정렬)에 대해 현재 레벨, 이전 레벨 하나, 그리고 다음 레벨 하나를 포함하는 창을 생성합니다. 현재 레벨이 5라면 레벨 4, 5, 6을 포함합니다.

참고: RANGE BETWEEN를 사용하면 윈도우에 더 많은 행이 포함될 수 있습니다. 왜냐하면 범위 내 값들과 동일한 값을 가진 모든 행을 포함하기 때문입니다. 반면 ROWS BETWEEN은 데이터 세트에 사용 가능한 한 항상 동일한 수의 행을 포함합니다.

또한 RANGE는 날짜 열을 지원하지 않습니다.

ROWS와 RANGE를 구별하기 위한 간단한 예제입니다:

ROWS 사용:

SELECT employee_name, salary,
    AVG(salary) OVER (
        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
    ) as avg_salary_rows

RANGE 사용:

SELECT employee_name, salary,
    AVG(salary) OVER (
        ORDER BY salary
        RANGE BETWEEN 1000 PRECEDING AND 1000 FOLLOWING
    ) as avg_salary_range

챌린지

쉬움

사용 가능한 테이블 및 열:
newspapers: date, num_newspapers

이 챌린지에서는 생산용 신문 데이터가 있습니다. 현재 행의 2일 전부터 1일 후까지 인쇄된 신문의 평균을 알고 싶습니다. 또한, 현재 날짜와 그로부터 3일 전까지 인쇄된 신문 수의 최대값과 최소값의 차이를 알고 싶습니다. 이 컬럼들을 각각 avg_newspapers와 diff_newspapers라고 지정하세요.

치트 시트

ROWS와 RANGE를 사용하여 윈도우 함수에서 유연한 윈도우 프레임을 정의하세요:

ROWS - 행 위치로 윈도우를 정의합니다:

OVER (ROWS BETWEEN --START-- AND --END--)

RANGE - 값 범위로 윈도우를 정의합니다 (ORDER BY 필요):

OVER (ORDER BY column_name RANGE BETWEEN --START-- AND --END--)

윈도우 경계 옵션:

CURRENT ROW - 현재 행
n PRECEDING - 현재보다 n 행/값 앞
n FOLLOWING - 현재보다 n 행/값 뒤

예제:

-- ROWS: 현재 행 + 앞 1개 + 뒤 1개 포함 (위치 기준)
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING

-- RANGE: 범위 내 값 포함 (값 기준)
ORDER BY levels
RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING

주요 차이점: