[SQL] 23. range(rows) between .. and ..

22. range between .. and ..

오늘은 ORACLE SQL의 window function 중 하나인 range between .. and .. 를 알아보자.

본인은 최근 자격증 공부를 하며 알게된 기능인데,
그룹별 누적 수치를 보여주는데 있어 상당히 유용하다.

range between과 and 사이에는
1. UNBOUNDED PRECEDING: 최종 출력될 값의 맨 처음 row의 값(Partition by 고려)
2. CURRENT ROW: 현재 row의 값
3. UNBOUNDED FOLLOWING: 최종 출력될 값의 맨 마지막 row의 값(Partition by 고려)
을 각각 넣어서 사용할 수 있다.

각 정의에 따라 UNBOUNDED PRECEDING은 range between 다음에만,
UNBOUNDED FOLLOWING은 and 다음에만 사용될 수 있다.

range between .. and .. 를 아에 적지 않았을 때의 default setting은
range between UNBOUNDED PRECEDING and CURRENT ROW 이다.

예제 문제로 살펴보자.

예제) 사원(emp)테이블 내 사원 이름, 직업, 월급을 출력하고
직업별 사원들의 월급 합계를 순차적으로 보여주시오!
select ename, job, sal,
      sum(sal) over (partition by job order by sal
            range between UNBOUNDED PRECEDING and CURRENT ROW) as "직업별 합계"
from emp ;

동일한 수치가 있을 때에는 두 수치의 대한 함수 결과값의 마지막 값을 보여준다.
→ ANALYST인 직원 모두 월급 3,000 동일 → sum 함수 결과값인 6,000 연속 출력

위의 ANALYST의 합계가 3,000 / 6,000 순차적으로 출력되게 하려면?
조금 더 디테일한 방법으로 rows between .. and .. 를 사용하면 된다.

이는 출력될 값의 RANGE가 아닌 ROW 기준으로 출력하므로 sal이 3,000이어서 중복임을 인지하지 않고 ROW에 해당하는 값으로 인지하여 함수에 적용한다.

select ename, job, sal,
      sum(sal) over (partition by job order by sal
            rows between UNBOUNDED PRECEDING and CURRENT ROW) as "직업별 합계"
from emp ;

추가적으로, UNBOUNDED가 아닌 1, 2 등의 숫자를 사용하여 원하는 값을 출력할 수 있다.

예제) 사원 테이블을 월급이 낮은 순서부터 나열한 후,
이름과 월급, 그리고 본인 기준 바로 위, 아래의 월급과 합계를 내시오!
select ename, sal,
      sum(sal) over (order by sal
          rows between 1 PRECEDING and 1 FOLLOWING) as "위아래합계"
from emp;

sum 이외의 다른 함수를 사용하여 다양한 값을 출력할 수 있다.

=================이 글은 Markdown을 활용해 작성하였습니다=================