[빅쿼리 #9] 윈도우함수를 알아보자(2) (ROWS BETWEEN 구문, PARTITION BY 구문)

- Today's Topic

• 윈도우함수의 개요와 쓰임
• ROWS BETWEEN 구문
• PARTITION BY구문

- Used Function

• ROWS BETWEEN start AND end 구문
• FIRST_VALUE() / LAST_VALUE() 함수
• ORDER BY 구문
• PARTITION BY 구문

---

안녕하세요. 이번에는 저번 윈도우함수 (1)에 이어서 진행해 보도록 하겠습니다.

저번시간에 윈도우 함수의 기본적인 내용을 학습했다면, 

이번에는 윈도우 함수를 조금 더 응용한 내용을 학습한다고 보시면 될 것 같습니다.

오늘 사용할 테이블은 다음과 같습니다.

윈도우함수.csv

0.00MB

ID열: 고유 ID를 나타냅니다.

Class: 반을 나타냅니다.

korean, english, math : 국어, 영어, 수학 점수를 나타냅니다.

score: 국어, 영어, 수학 점수의 평균값입니다.

 

들어가기에 앞서, 분석함수(윈도우함수)의 개념

분석함수란, 테이블 내의 데이터를 분석하여 결과를 리턴해주는 함수입니다.

다중 행의 결과를 출력할 경우 사용되며,

over()절과 함께 사용됩니다.

 

p.s) 윈도우함수와 분석함수를 혼용해서 사용하고 있는 것을 볼 수 있습니다.

하지만 같은 말이므로 동일한 단어라고 생각해 주시면 되겠습니다.

본 글에서는 혼동방지를 위해, 윈도우함수로 통일하도록 하겠습니다.

 

---

1.  윈도우함수 (ROWS BETWEEN start AND end 구문)

이 구문은 프레임을 지정하는 구문으로, 범위를 지정한다고 생각하시면 됩니다.특정 행과 행까지의 범위를 지정하여, 그 범위에 한정해서 분석을 해야할 경우 유용합니다.

 

사용할 함수 및 설명

rows between start and end

---------------------------- keywords : start와 end에 들어갈 키워드를 지정합니다.
current row : 현재의 행
n preceding : n행 앞
n following : n행 뒤
unbounded preceding : 이전 행 전부 (only start)
unbounded following : 이후 행 전부 (only end)

ex1) rows between 1 preceding and 1 following
=> 1행 앞부터 1행 뒤

ex2) rows between unbounded preceding and unbounded following
=> 모든 행 (현재 행 기준, 이전 행 전부와 이후 행 전부)

ex3) rows between unbounded preceding and current row
=> 첫번째 행부터 현재 행까지.

 

예제 테이블을 활용한 쿼리와 결과해석

--3) rows between start and end 구문 예제
select id, score
, row_number() over (order by score desc) as row

-- 행을 기준으로, 첫 번째 행부터 현재 행까지의 누적합계 구하기
, round(sum(score) over(order by score desc rows between unbounded preceding and current row),1) as sum_score

-- 현재 행과 앞의 행을 기준으로의 평균 점수 구하기
, round(avg(score) over(order by score desc rows between 1 preceding and 1 following),1) as avg3_score

from inlaid-lane-373607.sqlr202301.lec7_adv
order by row;

이처럼 해당 구문은 집계함수과 함께 사용하면 더욱 효율적입니다.

특정한 이전의 어떤 행에서부터 현재 행까지의 합계를 구하고 싶을 때,

현재 행으로부터 전후 n개의 행의 합계 혹은 평균을 구하고 싶을 때,

또한 후술할 순위가 제일 높은/낮은 함수를 구하고자 할 경우 등등 

다양한 분석 상황에서 유용하게 쓰일 수 있는 구문이라고 보시면 됩니다. 

 

*참고. first_value()와 last_value()함수를 사용해서 점수가 제일 높은/낮은 학생의 id를 추출하기.

--평균점수가 가장 높은 / 가장 낮은 학생 추출하기

select id, score,
first_value(id) over(order by score desc rows between unbounded preceding and unbounded following) as first
, last_value(id) over(order by score desc rows between unbounded preceding and unbounded following) as last
from inlaid-lane-373607.sqlr202301.lec7_adv
order by score desc;

괄호 안의 구문부터 먼저 진행되므로, over함수 안에 있는 구문부터 먼저 처리됩니다.

먼저 order by구문을 활용하여 점수별로 내림차순으로 정렬하고,

rows between 구문을 활용하여 특정 행의 이전 행 전체, 이후 행 전체 범위를 정한 뒤,

first_value()함수를 사용해 id의 첫번째에 위치한 칼럼의 값을 반환해 줍니다.

이 경우, order by구문을 활용해 내림차순으로 정렬한 상태이므로 점수가 가장 높은 학생이 1행에 위치해 있으므로,

그 1행에 위치한 B008학생이 출력되는 것입니다.

 

반대로 LAST_VALUE()함수 역시 동일한 과정으로, 가장 점수가 낮은 학생이 가장 마지막 행에 위치하므로,가장 마지막 열에 위치한 A009학생이 출력됩니다.

 

---

2.  윈도우함수 (PARTITION BY구문)

이 구문 역시 분석함수의 일종으로, ORDER BY구문과 함께 사용할 수 있습니다.

Partition이 분할이라는 의미를 가지므로,

카테고리별로 분할하여 순위를 매긴다거나, 정렬할 경우 유용하게 사용할 수 있습니다.

즉 그룹화한다고 생각하면 좀 편하고, group by를 사용할 경우와 같은 결과를 얻을 수 있습니다.

 

 

사용할 함수 및 설명

partition by 구문

집계/순서/순위함수 등 over (partition by 분할할 칼럼명 [order by 정렬할 칼럼명 [desc]])

 

 

예제 테이블을 활용한 쿼리

select id, class, score,
dense_rank() over(partition by class order by score desc) as dense_score
from inlaid-lane-373607.sqlr202301.lec7_adv
order by class, score desc;

 

위의 쿼리를 해석해 보면 다음과 같습니다.

 

1행. 먼저 기본정보를 표시하는 id, class, score열을 출력합니다.

 

2행. dense_rank()함수를 사용하여 순위를 정합니다.

참고로 dense_rank()는 순위중복을 허용하며, 중복된 개수와 관련 없이 그 다음 순위는 그 다음 숫자에 해당하는 순위가 부여됩니다.

다만, 순위를 정하는데 조건이 부여됩니다.  바로 partition by입니다.

일단 분석함수를 쓰는 것이므로 over절을 써 주고, partition by절을 작성해 주는데,

이는 '~~별로 그룹화한다'고 생각해 주면 됩니다.

코드에서는 반(class)별로 그룹화하여 score을 내림차순을 정렬하여라고 되어 있네요.

이를 제가 별칭으로 dense_rank라고 주었습니다. 

 

3행. 데이터를 뽑을 테이블을 지정해 주었습니다.

 

4행. class는 오름차순으로 정렬하였고(기본값이므로 생략가능합니다.), score는 내림차순으로 정렬하였습니다.

 

 

쿼리 결과문

보시다시피 class별로 정렬되었고, score도 class별로 정렬된 것에 따라 그 안에서 내림차순으로 정렬된 것을 확인할 수 있습니다.

 

 

이상으로 윈도우함수의 두 번째 시간을 마치도록 하겠습니다.

감사합니다.

---