[빅쿼리 #8] 윈도우함수를 알아보자(1) (순위함수, 행의 값 조회 함수)

SQL/Google Big Query

[빅쿼리 #8] 윈도우함수를 알아보자(1) (순위함수, 행의 값 조회 함수)

Delants 2023. 1. 30. 15:46

- Today's Topic

Window Function의 개요
순위 함수
행의 값 조회 함수
order by 구문

- Used Function

순위함수 ROW_NUMBER(), RANK(), DENSE_RANK()의 차이와 쓰임
행의 값 조회 함수 LAG(), LEAD()함수의 차이와 쓰임
ORDER BY구문 (OVER()함수 포함)

안녕하세요. 오늘은 윈도우함수에 대해 살펴보도록 하겠습니다.

윈도우 함수라는 개념이 생소하실 수 있으므로, 간단한 개념부터 보겠습니다.

1. 윈도우 함수 (Window Function) 개요

SQL에는 '순서'라는 개념이 존재하지 않기 때문에, 순서와 관련된 데이터를 처리하려면 매우 복잡한 과정이 동반됩니다.

이러한 과정을 줄이고자 등장한 것이 윈도우 함수입니다.

윈도우 함수란, 테이블 내부에 윈도우라는 범위를 지정하고, 해당 범위 내부에 포함된 특정 값을 특정 레코드에서 자유롭게 사용하게 하는 것입니다.

그래서 윈도우 함수를 사용하면, 사용하지 않았을 때 보다 훨씬 간결한 쿼리를 만들 수 있습니다.

윈도우 함수에는 기존에 사용했던 집계함수, 순위함수, 순서함수를 활용하여 다양하게 활용할 수 있습니다.

다만 이러한 값들을 활용하려면 위치를 명확히 지정해주어야 하는데 이때 사용되는 구문이 바로 order by구문입니다.

그렇다면 지금부터 윈도우 함수를 예제를 통해 살펴보도록 하겠습니다.

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

윈도우함수.csv

0.00MB

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

Class: 반을 나타냅니다.

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

필자는 여기서 추가로 전체 점수의 평균을 구한 'score'이라는 새로운 칼럼을 생성하였습니다.

이 경우 사용된 구문은 다음과 같습니다.

alter table inlaid-lane-373607.sqlr202301.lec7_adv add column score int64; -- 칼럼 생성
alter table inlaid-lane-373607.sqlr202301.lec7_adv ALTER COLUMN score set DATA TYPE float64; --칼럼 데이터 타입 변경

update inlaid-lane-373607.sqlr202301.lec7_adv set score = round((korean + english + math)/3,1) where score is null; -- 칼럼 안에 score 평균 데이터 값 넣기

처음에는 int64라는 숫자타입으로 지정했으나,

평균이므로 소수점으로 출력이 된 데이터가 대부분이었습니다.

그래서 평균 값을 삽입할 때 오류가 생겨, 데이터 타입을 float64타입으로 변경하고 다시 데이터를 삽입하였습니다.

2. 순위 함수

순위 함수는 ROW_NUMBER, RANK(), DENSE_RANK()함수로 구성되어 있습니다.

특정 테이블에서 순위를 구할 경우 주로 사용하게 되는데

이 세 함수가 비슷하지만 세부적으로 본다면 약간씩 다릅니다.

사용할 함수 및 설명

row_number() : 각 데이터의 순위를 유일하게 부여함.

rank() : 각 데이터의 값이 같을 경우, 같은 순위를 부여함. 다음 순위는 해당 개수만큼 건너뛴다.

dense_rank() : 각 데이터의 값이 같을 경우 같은 순위를 부여하되, 다음 순위는 계속 이어지도록 한다.

기본형은 위의 구문이며, 여기서 over()함수를 사용하게 되면, 칼럼별로 정렬(ORDER BY구문 사용)하거나 그루핑을 해줄 수 있게 됩니다.

over()함수를 사용하게 되면 다음과 같이 쓰일 수 있습니다.

select 칼럼1, 칼럼2, 
row_number over(order by 칼럼1 desc) as 별명,
rank over(order by 칼럼1 desc) as 별명,
dense_rank(order by 칼럼1 desc) as 별명
from 테이블명;

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

select id, score, row_number() over (order by score desc) as row
, rank() over (order by score desc) as rank
, dense_rank() over (order by score desc) as dense_rank
from inlaid-lane-373607.sqlr202301.lec7_adv
order by row;

위의 쿼리로 실습을 하다 보면 1위부터 8위까지는 중복되는 점수가 없어서 row_number(), rank(), dense_rank() 모두 같은 결과가 나왔습니다.

하지만 중복되는 값이 있을 경우 이야기가 달라집니다.

9위와 10위, 11위의 평균점수가 모두 69.7점으로 동일합니다. 이 때 각 함수는 어떻게 리턴해 줄까요?

먼저 row_number함수를 썼던 row칼럼에서는 행번호대로 숫자가 부여되는 것을 확인할 수 있습니다.

즉 순위는 고유하다는 것이 성립이 되죠.

다음으로 rank()함수를 썼던 rank칼럼에서는 동일한 점수라면 동일한 순위가 부여됩니다.

하지만 12행 id가 a001인 학생의 점수가 66점인데, 이 학생의 등수는 앞서 중복된 점수의 사람 수(3명)만큼 밀려서 12등이 되었습니다.

dense_rank()함수를 썼던 칼럼에서는 동일한 점수라면 동일한 순위가 부여됩니다.

하지만 rank함수와는 다르게, 앞서 중복된 사람이 몇 명이라는 것과 상관없이, 바로 그냥 다음 숫자의 순위가 부여된다는 것이 차이점입니다.

3. 행의 값 조회 함수

사용할 함수 및 설명

lead(기준칼럼,값을 가져올 행의 위치, 값이 없을 경우 기본값) : 현재 행보다 n행 뒤에 있는 값을 추출

lag(기준칼럼,값을 가져올 행의 위치, 값이 없을 경우 기본값) : 현재 행보다 n행 앞에 있는 값을 추출

행의 값 조회 함수는 lead()함수와 lag()함수로 나뉘어 집니다.

영어 뜻을 잘 아시는 분들이라면 짐작이 가셨겠지만,

lead는 '이끌다'는 의미이므로 현재 행이 이끄는 뒤의 값을 추출해 내는 것입니다.

반면 lag는 '끌리다'는 의미이므로 현재 행을 기준으로 선두의 값 즉, 앞의 값을 추출하는 것이라고 보면 됩니다.

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

select id, score
, row_number() over (order by score desc) as row
, lead(score, 2) over(order by score desc) as lead
, lag(score, 2) over(order by score desc) as lag
from inlaid-lane-373607.sqlr202301.lec7_adv
order by row;

위 쿼리와 결과를 보시면 어느 정도 이해가 갈 수 있습니다.

lead()함수를 사용하면 score칼럼을 기준으로 2행 뒤의 값이 추출된 것을 확인할 수 있습니다.

원래는 82.7점이 와야 하지만 2행 뒤의 79점이 반환되었지요.

lag()함수를 사용하면 score칼럼을 기준으로 2행 앞의 값이 추출되었습니다.

원래라면 3행에는 79.0점이 와야 하지만, 2행 앞의 값인 82.7점이 추출된 것을 확인할 수 있습니다.

또한, lag칼럼에서 1행과 2행의 2번째 전의 행이 존재하지 않아, null값으로 된 것도 보입니다.

오늘은 윈도우함수의 첫 번째, 윈도우함수의 개념과 순위함수와 행의 값 조회 함수에 대해 살펴보았습니다.

다음에는 ORDER BY구문을 조금 더 응용하여 윈도우 프레임 지정과 ORDER BY구문의 결합, PARTITION BY구문에 대해 살펴보겠습니다.

이상입니다.