OVER 함수에 대한 추상적 정의 보단 코드를 통해 이 녀석의 정체를 알아보자.
예제 테이블 준비
SELECT * FROM class_score
OVER() 가 없다면?
아래와 같이 단일컬럼(name)과 집계 함수(AVG)를 같이 쓰면 오류가 발생한다.
SELECT name, AVG(score) FROM class_score;
//열 'class_score.name'이(가) 집계 함수나 GROUP BY 절에 없으므로 SELECT 목록에서 사용할 수 없습니다
☞ 집계함수(Aggregate Function)란?
테이블 전체 행으로부터 하나의 결과값을 반환
| 설명 | |
| COUNT(*) | 복수 행의 ROW 개수 |
| SUM(*) | 복수 행의 해당 컬럼간 합계 |
| AVG(*) | 복수 행의 해당 컬럼간 평균 |
| MAX(*) | 복수 행의 해당 컬럼간 최대값 |
| MIN(*) | 복수 행의 해당 컬럼간 최소값 |
| STDDEV(*) | 복수 행의 해당 컬럼간 표준편차 |
| VARIAN(*) | 복수 행의 해당 컬럼간 분산 |
단일컬럼은 전체 행 갯수 만큼 값을 리턴 하지만 집계함수는 하나의 값만 리턴하기 때문이다.
즉 이 갯수 차이 때문에 둘은 같이 쓸 수 없는거다.
이때 over()를 사용하면 문제를 해결할 수 있다.
OVER 사용 예제
사용 방법은 간단하다. 집계 함수 뒤에 OVER()를 붙여주기만 하면 된다.
기본 스타일
SELECT name, AVG(score) over() FROM class_score;
집계 함수 결과가 행 갯수만큼 출력된다. 집계함수와 단일컬럼을 동시에 사용할 수 있게 된거다.
PARTITION BY 적용 예제
한단계 더 나아가 각 반 별로 집계를 구하고 싶다.(현재는 반 상관없이 전체를 기준)
SELECT class, name, AVG(score) over() FROM class_score;
OVER() 안에 PARTITION BY [그룹핑하고 싶은 컬럼명] 을 넣어주면 된다.(group by 랑 비슷)
예제에선 class를 기준으로 할거니까 PARTITION BY class 를 넣었다.
SELECT class, name, AVG(score) over(PARTITION BY class) FROM class_score;
class별 집계 결과를 확인할 수 있다.
ORDER BY 추가 적용
이외에도 order by를 추가해주면 그룹별 정렬도 가능하다.
아래 예시는 그룹 별 생년월일(birthdate)이 정렬된 결과다.
SELECT class, name, birthdate, AVG(score) over(PARTITION BY class ORDER BY birthdate) FROM class_score;
댓글