[Oracle] ROWS와 RANGE 차이 및 사용법 (WINDOWING 절)

젠트
데이터베이스/오라클
2022. 1. 25.

오라클에서 OVER 절에 WINDOWING 절을 처음 사용할 때 ROWS와 RANGE가 어떤 차이점이 있는지 많이 헷갈릴 수 있다. 간단히 설명하면 ROWS는 각 행의 위치고, RANGE는 값의 범위라고 생각하면 된다. 아래의 예제를 여러 번 반복해서 보면 많이 어렵지 않게 이해할 수 있을 것이다.

아래의 키워드 의미부터 숙지를 하고 예제를 보면 더욱 이해하기 쉬울 것이다.

키워드	설명
ROWS	물리적인 행 단위
RANGE	논리적인 행 집합
CURRENT ROW	현재 행
UNBOUNDED PRECEDING	첫 번째 행
UNBOUNDED FOLLOWING	마지막 행
[위치] PRECEDING	[위치] 이전 행
[위치] FOLLOWING	[위치] 다음 행

ROWS와 RANGE 차이점

ROWS : 조회된 데이터를 물리적 위치(ROWNUM)로 구분하여 모든 행이 1개의 행으로 인식한다.

RANGE: ORDER BY 절에 명시된 칼럼으로 논리적인 행 집합을 구성하며, 집합으로 묶인 그룹이 1개의 행으로 인식한다. 위의 예제는 SALES_QUARTER 칼럼의 값으로 행 집합을 지정하였다. ORDER BY에 명시된 칼럼에 따라서 행 집합이 바뀔 수 있다.

아래의 ROWS와 RANGE 예제를 비교하면 조금 더 이해하기 쉬울 것이다.

ROWS 사용법

WITH sales AS (
  SELECT '01' AS sales_month, 1 AS sales_quarter, 200 AS sales_amt FROM dual UNION ALL
  SELECT '02' AS sales_month, 1 AS sales_quarter, 250 AS sales_amt FROM dual UNION ALL
  SELECT '03' AS sales_month, 1 AS sales_quarter, 300 AS sales_amt FROM dual UNION ALL
  SELECT '04' AS sales_month, 2 AS sales_quarter, 350 AS sales_amt FROM dual UNION ALL
  SELECT '05' AS sales_month, 2 AS sales_quarter, 300 AS sales_amt FROM dual UNION ALL
  SELECT '06' AS sales_month, 2 AS sales_quarter, 310 AS sales_amt FROM dual UNION ALL
  SELECT '07' AS sales_month, 3 AS sales_quarter, 400 AS sales_amt FROM dual UNION ALL
  SELECT '08' AS sales_month, 3 AS sales_quarter, 450 AS sales_amt FROM dual UNION ALL
  SELECT '09' AS sales_month, 3 AS sales_quarter, 350 AS sales_amt FROM dual UNION ALL
  SELECT '10' AS sales_month, 4 AS sales_quarter, 300 AS sales_amt FROM dual UNION ALL
  SELECT '11' AS sales_month, 4 AS sales_quarter, 250 AS sales_amt FROM dual UNION ALL
  SELECT '12' AS sales_month, 4 AS sales_quarter, 200 AS sales_amt FROM dual
)

SELECT sales_month
     , sales_quarter
     , sales_amt
     , SUM(sales_amt) OVER(ORDER BY sales_quarter 
                            ROWS BETWEEN UNBOUNDED PRECEDING 
                                     AND CURRENT ROW) AS sum_rows
  FROM sales

이전 행(PRECEDING)과 현재 행(CURRENT ROW)을 물리적인 위치 값으로 지정하여 연산을 할 수 있다.

UNBOUNDED PRECEDING는 첫 번째 행을 뜻한다. (BETWEEN 첫 번째 행 AND 현재 행)

이전 행의 모든 값과 현재 행의 값이 순차적으로 합산(SUM)이 되는 것을 확인할 수 있다.

RANGE 사용법

WITH sales AS (
  SELECT '01' AS sales_month, 1 AS sales_quarter, 200 AS sales_amt FROM dual UNION ALL
  SELECT '02' AS sales_month, 1 AS sales_quarter, 250 AS sales_amt FROM dual UNION ALL
  SELECT '03' AS sales_month, 1 AS sales_quarter, 300 AS sales_amt FROM dual UNION ALL
  SELECT '04' AS sales_month, 2 AS sales_quarter, 350 AS sales_amt FROM dual UNION ALL
  SELECT '05' AS sales_month, 2 AS sales_quarter, 300 AS sales_amt FROM dual UNION ALL
  SELECT '06' AS sales_month, 2 AS sales_quarter, 310 AS sales_amt FROM dual UNION ALL
  SELECT '07' AS sales_month, 3 AS sales_quarter, 400 AS sales_amt FROM dual UNION ALL
  SELECT '08' AS sales_month, 3 AS sales_quarter, 450 AS sales_amt FROM dual UNION ALL
  SELECT '09' AS sales_month, 3 AS sales_quarter, 350 AS sales_amt FROM dual UNION ALL
  SELECT '10' AS sales_month, 4 AS sales_quarter, 300 AS sales_amt FROM dual UNION ALL
  SELECT '11' AS sales_month, 4 AS sales_quarter, 250 AS sales_amt FROM dual UNION ALL
  SELECT '12' AS sales_month, 4 AS sales_quarter, 200 AS sales_amt FROM dual
)

SELECT sales_month
     , sales_quarter
     , sales_amt
     , SUM(sales_amt) OVER(ORDER BY sales_quarter 
                           RANGE BETWEEN UNBOUNDED PRECEDING 
                                     AND CURRENT ROW) AS sum_range
  FROM sales
 ORDER BY sales_month