[오라클(Oracle)] WINDOWING 절

SQL-D 시험을 볼 때 WINDOWING절에 대한 지식이 부족해서 WINDOW 함수 문제를 결국 틀렸는데 아쉬운 점이 남아서 이에 대해 다시 정리를 해본다. WINDOWING절은 WINDOW 함수에 쓰이는 것으로 우선 WINDOW 함수에 대해서 알아보자.

1. WINDOW 함수

WINDOW 함수는 행과 행간의 관계를 정의하고, 행과 행을 비교, 연산하는 것을 도와주는 함수이다. 이러한 WINDOW 함수는 다음과 같은 형식으로 쓰인다.

SELECT WINDOW 함수 (ARGUMENTS) OVER ([PARTITION BY 컬럼], [ORDER BY 절], [WINDOWING 절]) FROM 테이블명;

간략하게 설명해보자면 ARGUMENTS에 들어가는 함수의 인수이며, PARTITION BY절은 GROUP BY와 비슷한 역할을 한다고 생각하면 된다.
ORDER BY절 또한 평소에 쓰던 ORDER BY절과 같이 순서를 정의해준다.

마지막으로 이 포스트에서 다루게 될 WINDOWING절은 함수의 대상이 되는 범위를 지정해주는 역할을 한다.

2. WINDOWING절

범위를 지정하는 두가지 방식이 있다. 하나는 앞에 ROWS를 쓰는 것이고 다른 하나는 RANGE를 쓰는 것이다.
ROWS의 경우 행을 기준으로 범위를 지정해주며 RANGE는 행이 가지고 있는값을 기준으로 범위를 지정해준다.

다음과 같은 테이블이 있고 밑줄친 부분을 현재 함수 작업이 이루어지는 행이라고 생각해보자.
여기서 현재 행을 가르치는 용어로 CURRENT ROW가 있다.
상위행을 다 지정해주는 용어로는 UNBOUNDED PRECEDING이 있으며 이 테이블의 경우 1행까지를 다 지정해주며, 하위행을 다 지정해주는 용어로는 UNBOUNDED FOLLOWING는 6행까지 지정해준다.
PRECEDING, FOLLOWING을 이용해서 2 PRECEDING이라고 하면 위의 두 행까지를 범위로 하며, 3 FOLLOWING이라고 하면 아래의 세 행까지를 범위로 한다.

ROWS UNBOUNDED PRECEDING, ROWS UNBOUNDED FOLLOWING과 같이 시작 범위나 종료 범위만 지정할 수도 있지만, ROWS BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING, RANGE BETWEEN 300 PRECEDING AND 300 FOLLOWING과 같이 시작 범위와 종료 범위를 함께 지정해줄 수도 있다.

3. WINDOWING절의 예시

CREATE TABLE TAB1 (
    JOB VARCHAR2(15),
    NAME VARCHAR(10),
    SALARY INTEGER );

INSERT INTO TAB1 VALUES ('디자이너',  '김미나',  2200);
INSERT INTO TAB1 VALUES ('기획자', '이수미', 1800);
INSERT INTO TAB1 VALUES ('개발자', '김지호', 1800);
INSERT INTO TAB1 VALUES ('디자이너', '김길수',  2000);
INSERT INTO TAB1 VALUES ('개발자',  '김동현',  2000);
INSERT INTO TAB1 VALUES ('디자이너',  '정아영',  1900); 
INSERT INTO TAB1 VALUES ('개발자',  '이영희', 1700);
INSERT INTO TAB1 VALUES ('기획자',  '최정한', 1200);
INSERT INTO TAB1 VALUES ('기획자',  '이수호', 2100);

SELECT * FROM TAB1;

우선 예시 테이블을 하나 만들어주자.

SELECT JOB, NAME, SALARY, 
SUM(SALARY) OVER (PARTITION BY JOB ORDER BY JOB ROWS UNBOUNDED PRECEDING) JOB_SUM
FROM TAB1;

UNBOUNDED PRECEDING은 맨 위의 행을 의미하므로 현재 행을 기준으로 맨 위의 행까지의 합을 출력하는 것을 확인할 수 있다.

SELECT JOB, NAME, SALARY, 
SUM(SALARY) OVER (PARTITION BY JOB ORDER BY JOB ROWS BETWEEN CURRENT ROW  AND 1 FOLLOWING) JOB_SUM
FROM TAB1;

다음과 같이 PRECEDING이나 FOLLOWING 앞에 표현식을 써서 행을 지정해줄 수도 있으며 AND를 통해 시작 범위와 종료 범위도 같이 지정해줄 수 있다.
위 sql문의 경우 현재 행을 기준으로 이전 행과 현재 행의 합을 계산해 출력한다.
(BETWEEN을 쓰지 않으면 자동으로 현재 행을 기준으로 계산해주기 때문에 ROWS 1 PRECEDING과 기능은 같다.)

SELECT JOB, NAME, SALARY, 
SUM(SALARY) OVER (PARTITION BY JOB ORDER BY SALARY RANGE BETWEEN 100 PRECEDING AND 300 FOLLOWING) JOB_SUM
FROM TAB1
ORDER BY JOB;

직접적으로 물리적인 행의 범위를 지정해주던 ROWS와 달리 RANGE는 행이 가지고 있는 값을 이용해 지정을 해준다.
위 sql문의 경우, 현재 행의 SALARY를 기준으로 -100 ~ +300 범위 내에 있는 행의 합을 계산해 출력해준다.

4. 참조

데이터 전문가 지식포털 DBGuide.net, http://www.dbguide.net/db.db?cmd=view&boardUid=148205&boardConfigUid=9&categoryUid=216&boardIdx=135&boardStep=1