Analytic SQL 4. Window 절 심화

window 절이 다른 절과 다르게 알아야 하는 것이 조금 있기 때문에 추가적인 장을 만들어 공부해보도록 한다.

Analytic SQL의 기본 구문을 다시 한번 상기하고 시작해보도록 하자.

<Analytic 함수> (인자)
over(
-- partition 절
-- sorting 절
-- window 절
)

 

상기 코드에서 window 절에 해당하는 구문을 더 자세히 뜯어보면 다음과 같다고 할 수 있다.

--window 절의 사용
{rows | range}
{between
	{unbounded preceding | current row | value_expr {preceding | following}
}
and
{{unbounded preceding | current row | value_expr {preceding | following}}
	|{unbounded preceding | current row | value_expr preceding}
}

window 절을 올바르게 이해하여 어떤 시작 포인트와 종료 포인트를 가지게 될 것인가에 대한 것을 아는 게 해당 장의 핵심이라고 할 수 있겠다.

 

 각각의 중괄호로 적혀있는 키워드를 상세히 뜯어보자

 

rows | range

window의 개별 row를 정의한다. rows는 물리적인 row를, range는 논리적인 row를 의미한다.
order by 절이 없다면 해당 키워드는 기술할 수 없다  

 

아래는 rows | range 키워드를 사용하는 SQL의 예시이다.

select *,
sum(stu_money) over(partition by class order by stu_money rows between unbounded preceding and current row)
-- 학급으로 나누고 학급별 소지 돈으로 정렬. 이때의 열로 각 돈이 누적합되는 열을 새로이 생성
, sum(buy_date) over(partition by stu_id order by buy_date range between interval '2' day preceding and current row)
-- 학번으로 나누고 학번별 구매날짜를 기준으로 정렬. 이 때, interval이라는 논리적 row를 생성하였기에 range 키워드를 사용함
from a_table;

 

between... and

window 절의 시작과 종료를 정의한다. between 다음이 시작 지점, and 다음이 종료지점을 의미
between 절이 없다면 row | range 다음이 시작점, 현재 row(current row)가 종료점으로 설정됨 

직관적으로 이해할 수 있는 키워드이기에 간단히 예시만 짚고 넘어가자.

select *,
sum(money) over(partition by stu_id order by money rows between rows 2 preceding and current row)
-- unbounded preceding 이 아닌 2를 사용함
from a_table;

상기 코드에서 unbounded precding이 아닌 숫자도 사용이 가능하다. 이는 '직전 2개 이전의 열부터'를 의미함을 참고

상기의 코드는 아래의 코드로도 간략하게 간소화가 가능하다.

 

select *,
sum(money) over(partition by stu_id order by money rows between rows 2 preceding)
from a_table;

위 두 개의 코드는 완전히 동일하게 작동함을 알 수 있다.

 

unbounded preceding

window 절의 시작이 partition의 첫 번째 row부터 시작한다는 것을 정의한다.
window 절의 종료점으로는 사용될 수 없다.

 

unbounded following

window 절의 종료가 partition의 마지막 row에서 끝난다는 것을 정의한다.
window 절의 시작점으로는 사용될 수 없다. 

 

current row

window 절의 시작점/종료점 모두 사용 가능하나 보통은 종료점으로 사용된다.
종료점으로 사용된다면 window 절의 종료가 현재 row에서 종료됨을 정의한다.
시작점으로 사용된다면 window 절의 시작이 현재 row에서 시작됨을 정의한다.

우리는 지금껏 예시로 종료점으로  current row를 사용하였지만, 하나의 예시로 현재의 열부터 마지막 열까지를 의미하는 SQL도 작성해보도록 하자.

select *,
sum(sut_money) over(partition by class order by stu_money rows between current row and unbounded following)
from a_table;

 

rows + 정수 + preceding | following

정수형 표현과 preceding | following 표현을 같이 쓰게 되면 현재 열로부터 특정 지점 떨어진 곳을 시작점 | 종료점으로 두겠다는 것과 같다.

rows나 range 키워드 모두에 사용이 가능하며, rows와 사용될 때에는 물리적인 rows를 지정한다.

예시는 이미 여러 예시를 통해 간접적으로 설명했기에 패스

 

range + 정수 + preceding | following

상기 rows+정수형과 같은 방식으로 작동하나, 논리적인 값을 적용해야 한다는 차이를 가짐

크게 숫자값과 interval을 이용한 값이 사용됨. 또한 order by 절의 열로 숫자형 또는 date/timestamp(대부분의 형태) 형이 되어있어야 한다. 

아래는 range 와 정수의 사용이 혼용된 SQL의 예시이다.

select *,
sum(buy_date) over(partition by stu_id order by buy_date range between interval '2' day preceding and current row)
from a_table;

 

이를 응용하여 중앙합 또는 중앙평균을 도출해낼 수 있다.

중앙합/평균 : 현재 열에서 바로 위/아래 총 3개의 열의 합/평균

아래는 중앙합을 구하는 SQL의 예시이다.

select *,
sum(sut_money) over(partition by class order by stu_money rows between 1 preceding and 1 following)
from a_table;

 

rows와 range의 차이?

솔직히 말해서 물리적인 rows와 논리적인 range의 차이를 말로만 들으면 이해할 수 없다.

아래와 같은 테이블이 있다고 가정을 해보자.

학번	날짜	소유돈
101	2023-01-01	100
101	2023-01-02	150
101	2023-01-03	130
101	2023-01-05	180
101	2023-01-08	200

 

해당 테이블에서 눈여겨볼 사항은 바로 모든 날짜가 존재하지 않는다는 점. 이를 숙지하고 아래와 같은 코드를 추가하여 결과를 알아보도록 하자.

select *,
sum(소유돈) over(partition 학번 order by 날짜 rows between 2 preceding and current row) as rows_사용,
sum(소유돈) over(partition 학번 order by 날짜 range between interval '2' day preceding and current row) as range_사용
from a_table;

 

이를 실행한 결과는 아래와 같다.

학번	날짜	소유돈	rows_사용	range_사용
101	2023-01-01	100	100	100
101	2023-01-02	150	250	250
101	2023-01-03	130	380	380
101	2023-01-05	180	460	310
101	2023-01-08	200	510	200

 

'논리적인'이라는 의미를 이해했는가? 물리적인 열이라 함은 논리적인 순서를 고려하지 않고 현재 테이블에 존재하는 열을 기준으로 누적합이 계산되는 것을 알 수 있다.

하지만 range를 사용함으로 현재 테이블에서는 존재하지 않는 1월 6일과 1월 7일의 데이터를 0으로 취급하여 누적합이 계산된 것을 확인할 수 있다.