MSSQL OVER 함수는 뭘까?

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;