Window Function 범위 지정

Window function

보통 테이블에서 특정 값을 집계해야할 때, GROUP BY 절을 이용해 집계를 합니다.
하지만, GROUP BY 절로 쿼리를 조회하게 되면 결과값의 ROW수가 줄어들며 여러 컬럼을 조회하고 싶을 때에도 제약이 많습니다.
윈도우 함수를 이용하면 SELECT로 조회된 데이터들을 가지고 집계 함수 사용할 수 있습니다.

윈도우 함수는 집계 함수 말고도 여러 함수가 존재합니다.

RANK, DENSE_RANK, ROW_NUMBER : 순위 관련 함수
AVG, SUM, MAX, MIN, COUNT : 집계 함수
FIRST_VALUE, LAST_VALUE, LAG, LEAD : 순서 관련 함수

위에서 나열한 함수 말고도 더 많은 함수가 존재하지만, 빈번하게 사용되는 함수 위주로 적었습니다.

다음은 윈도우 함수를 사용하는 방법입니다.

윈도우함수(args) OVER (
[ PARTITION BY column ]
[ ORDER BY column [ASC | DESC] ]
[ [ROWS|RANGE] BETWEEN UNBOUNDED PRECEDING[CURRENT ROW]
AND UNBOUNDED FOLLOWING[CURRENT ROW] ]
)

윈도우 함수로 사용 되는 파라미터(args)는 함수마다 다르며 0~N개가 지정될 수 있습니다.

OVER 괄호 안의 내용은 전부 생략 가능한 내용입니다.
하나씩 살펴보면 PARTIONTION BY 는 입력한 컬럼별로 프레임을 나누고, 나눠진 프레임별로 윈도우 함수를 적용합니다.
GROUP BY 절을 이용해 컬럼별로 묶는 것과 같다고 생각하시면 됩니다.
어떤 함수를 적용하기전 정렬이 필요하다면 ORDER BY 절을 이용하면 됩니다.

윈도우 함수를 적용할 때, 조회된 모든 데이터가 아닌 특정 행의 범위를 지정하고 싶다면 BETWEEN-AND를 사용하면 됩니다.

ROWS : 물리적인 단위로 행 집합을 지정
RANGE : 논리적인 단위로 의해 행 집합을 지정
BETWEEN ~ AND : 윈도우의 시작과 끝 위치를 지정
UNBOUNDED PRECEDING : 윈도우 시작 위치가 첫 번째 로우임을 의미
UNBOUNDED FOLLOWING : 윈도우 마지막 위치가 마지막 로우임을 의미
[ROW수] PRECEDING : 윈도우 시작 위치가 ROW수만큼 이전이 시작 로우임을 의미
[ROW수] FOLLOWING : 윈도우 마지막 위치가 ROW수만큼 다음이 마지막 로우임을 의미
CURRENT ROW : 현재 로우까지를 의미

ROWS와 RANGE의 차이는 ROWS는 조회된 ROW 하나하나를 대상으로 연산하며,
RANGE는 ORDER BY 를 통해 정렬된 컬럼에 같은 값이 존재하는 ROW가 여러 개일 경우, 동일한 컬럼값을 가지는 모든 ROW를 묶어서 연산을 합니다.

아래는 ROWS를 이용한 예제입니다.

SELECT dept, id, salary,
       SUM(salary) OVER (PARTITION BY dept ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) col
  FROM (
          SELECT 20 dept, 100 id, 20000 salary UNION ALL
          SELECT 20 dept, 101 id, 30000 salary UNION ALL
          SELECT 20 dept, 101 id, 10000 salary UNION ALL
          SELECT 20 dept, 102 id,  9000 salary UNION ALL
          SELECT 20 dept, 103 id, 17000 salary
       ) D

ROWS를 사용해 조회한 쿼리 결과

dept	id	salary	col
20	100	20000	20000
20	101	30000	50000
20	101	10000	60000
20	102	9000	69000
20	103	17000	86000

col에 사용한 ROWS 범위는 첫 번째 로우부터 현재 로우까지의 합을 표시합니다.
물리적인 ROW로 연산하기 때문에 ORDER BY 컬럼으로 사용된 id 값이 같아도 col의 내용은 다릅니다.

이제 RANGE를 이용한 쿼리와 비교해보겠습니다.
위에서 사용한 쿼리와 비교했을 때, 바뀐건 ROWS에서 RANGE로 바뀌었을 뿐 입니다.

SELECT dept, id, salary,
       SUM(salary) OVER (PARTITION BY dept ORDER BY id RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) col2
  FROM (
          SELECT 20 dept, 100 id, 20000 salary UNION ALL
          SELECT 20 dept, 101 id, 30000 salary UNION ALL
          SELECT 20 dept, 101 id, 10000 salary UNION ALL
          SELECT 20 dept, 102 id,  9000 salary UNION ALL
          SELECT 20 dept, 103 id, 17000 salary
       ) D

RANGE로 조회한 쿼리 결과

dept	id	salary	col
20	100	20000	20000
20	101	30000	60000
20	101	10000	60000
20	102	9000	69000
20	103	17000	86000

RANGE로 조회된 결과를 보면 ORDER BY 컬럼으로 사용된 id 값이 같은 컬럼을 묶어서 연산을 합니다.
그래서 id 값이 같은 경우는 col의 결과 값이 동일하게 나오는 것을 알 수 있습니다.