회사에서 여러 데이터들을 취합하는 통계 기능을 구현할 일이 있었는데, 그 과정에서 SQL의 윈도우 함수를 알게 되어 정리하고자 한다.
윈도우 함수란?
윈도우 함수는 행과 행 간의 관계를 정의하거나 비교, 연산을 수행할 수 있는 함수이다. 윈도우 함수를 통해 여러 서브 쿼리를 이용해서 만들어야 할 결과물을 간단하게 만들 수 있다.
윈도우 함수의 문법은 다음과 같다.
* 함수(컬럼) OVER (Partition By 컬럼 Order By 컬럼)
함수 : Min, Max, Sum, Count와 같이 기존에 있는 집계함수 및 윈도우 함수 (RANK, ROW_NUMBER, FIRST_VALUE, LEAD ...)
OVER : 윈도우 함수의 문법으로 Over절 내부에 Partition By, Order By 절이 삽입된다.
Partition By : 전체 집합을 어떤 기준에 따라 나눌지 명시
Order By : 전체 집햅을 어떤 기준에 따라 순위를 결정할지 명시 Group By와 비슷한 면이 있는 것 같다. Group By 절과의 차이는 Group By는 By에 명시된 컬럼을 기준으로 데이터를 자르고, 그 앞에 집계 함수를 오게 하여 집계 함수로 데이터를 집약시키지만, 윈도우 함수는 단순히 데이터를 Partition By 절에 지정된 컬럼을 기준으로 데이터를 자르기만 한다. 즉 쿼리 셋의 행의 수는 유지된다.
다음과 같이 student 테이블이 있다고 하자.
| id | name | department | score |
|---|---|---|---|
| 1 | 김훈키 | 영문학과 | 85 |
| 2 | 강훈키 | 컴퓨터공학과 | 90 |
| 3 | 채훈키 | 컴퓨터공학과 | 100 |
| 4 | 이훈키 | 국문학과 | 65 |
특정 과를 기준으로 합산 점수를 구하는 쿼리를 Group By, 윈도우 함수 문법로 구현했을 때 결과는 다음과 같다.
- Group By
SELECT s.department, SUM(s.score) FROM student s GROUP BY s.department; | department | score |
|---|---|
| 영문학과 | 85 |
| 컴퓨터 공학과 | 190 |
| 국문학과 | 65 |
- 윈도우 함수 문법 SUM + Over ~ Parition By
SELECT s.department, SUM(s.score) OVER(PARTITION BY s.department) FROM student s;| department | score |
|---|---|
| 영문학과 | 85 |
| 컴퓨터 공학과 | 190 |
| 컴퓨터 공학과 | 190 |
| 국문학과 | 65 |
문법을 자세히 보면 Group By는 FROM 절 뒤에 나오는 반면 윈도우 함수는 SELECT 쪽에서 사용할 수 있는 구문이다. 윈도우 함수의 최종 결과가 결과 집합의 하나의 컬럼으로 들어가는 것이기 때문에 전체 쿼리 셋의 행의 개수는 유지된다.
특정 그룹의 합은 취할 수 있으면서, 전체 쿼리셋 행에 영향을 미치지 않으므로, 행과 행간의 순번이나 다음 값 등을 자유롭게 조작할 수 있다.
윈도우 함수 사용 예시
- 순위 함수
순위 함수는 행과 행끼리의 순위를 매겨주는 윈도우 함수이다. RANK, DENSE_RANK, ROW_NUMBER가 있다. Over 절 안에 Order By 컬럼 명시를 통해 어떤 기준으로 순위를 매길지 지정할 수 있다.
RANK : 동일한 값에 대해서 같은 순위를 부여하고 중간 순위를 비우는 함수이다.
DENSE_RANK : 동일한 값에 대해서 같은 순위를 부여하고 중간 순위를 비우지 않는 함수이다.
ROW_NUMBER : 동일한 값이어도 고유한 순위를 부여한다. (말 그대로 행 넘버)
예시 1)
SELECT s.name, RANK(*) OVER(ORDER BY s.score DESC) FROM student;| name | rank |
|---|---|
| 채훈키 | 1 |
| 강훈키 | 2 |
| 김훈키 | 3 |
| 이훈키 | 4 |
Partition By 와 같이 쓰면 다음과 같이 쓸 수 있다.
예시 2)
select s.name, s.department, RANK(*) OVER(PARTITION BY s.department ORDER BY s.score DESC) FROM student s;| name | department | rank |
|---|---|---|
| 이훈키 | 국문학과 | 1 |
| 김훈키 | 영문학과 | 1 |
| 채훈키 | 컴퓨터공학과 | 1 |
| 강훈키 | 컴퓨터공학과 | 2 |
department를 Partition by절에 명시하면 RANK는 소그룹화된 “department” 안에서의 순위를 구해서 보여준다.
- 일반 집계 함수
결과를 집계하는 함수이다. 이 함수들은 주로 Group By 절에도 사용이 가능하다. SUM, MAX, MIN, AVG, COUNT 가 있다.
- 그룹 내 순서 함수
FIRST_VALUE : 파티션 별 윈도우에서 가장 먼저 나온 값을 구한다. 공동 등수를 인정하지 않고 처음 나온 행만 가져온다.
LAST_VALUE : 파티션 별 윈도우에서 가장 마지막에 나온 값을 구한다.
LAG : 이전 몇 번째 행의 값을 가져오는 함수이다. 3가지 인자를 받는다. LAG(score, 3, 0) 첫 번째 인자는 컬럼, 두 번째 인자는 몇 번째 앞의 행을 가져올지 결정하는 것이며, 세 번째 인자는 가져올 행이 없을 경우 Default 값을 지정하는 것이다.
LEAD : LAG와 비슷하나 이후 몇 번째 행의 값을 가져오는 함수이다.
SELECT s.name, LEAD(s.score) OVER(ORDER BY s.score DESC) as next_student_score FROM student;예시 1)
| name | next_student_score |
|---|---|
| 채훈키 | 90 |
| 강훈키 | 85 |
| 김훈키 | 65 |
| 이훈키 | null |
각 학생의 점수를 내림차순 했을 때 자신의 점수 다음으로 점수가 높은 학생의 점수를 불러올 수 있다.
그 밖의 윈도우 함수로는 그룹 내 비율을 구하는 함수들이 존재한다.
결론
서비스가 복잡해지고 고도화될 수록 복잡한 쿼리를 짜야하는 요구사항이 많아진다. 윈도우 함수를 연습해서 복잡한 요구사항에도 쉽게 대응할 수 있게 해야겠다.
