본문 바로가기

별걸다하는 IT/데이터베이스 Database

[SQL] 오라클 over절에 대해 알아보자 - 개념 및 사용법 정리

반응형

[데이터베이스 완전정복 목차]

안녕하세요 양햄찌 블로그 주인장입니다.

오늘은 SQL구문 중에서 OVER절에 대해 다뤄보려고 해요~

 

OVER절이란 무엇인가?

누적이라던가, 순위, 퍼센테이지, 평균, 총합 등 데이터를 통계나 집계할 때

단일함수랑 집계함수랑 같이 올 수 없기 때문에, 서브쿼리를 사용하게 되는데요

특히 다수의 집계결과가 필요할 때 여러 서브쿼리와 그룹바이로 인해 쿼리가 지저분해져요.  

이를 마법처럼 간단하게 만들어주는 절이 OVER절입니다.

 

OVER절 유용한 상황

샘플 테이블 EMPLOYEES 에서 

직원연봉과 직원전체평균연봉, 그리고 직원전체연봉총합을 알려주는 통계를 작성한다고 가정해봅시다.

표는 요런식으로 나와야겠죠?? 아직 우리는 OVER를 배우지 않았으니~~

우리가 알고 있는 기본적인 SELECT, FROM만 사용해서 작성해봅시다. 

주의할점은 집계함수는 단일함수, 집계되지 않은 컬럼과 같이 사용할 수 없습니다!! 

총합이랑 평균이 필요한대... 위처럼 간단하게 작성하는건 불가능해요.

결국 서브쿼리로 평균과 합계 데이터를 뽑아줘야 하는 것 ~

SELECT 
	EMPLOYEE_ID 직원번호, 
	SALARY 연봉, 
	ROUND((
		SELECT AVG(SALARY) 
		FROM EMPLOYEES
	)) AS 직원평균연봉, 
	(
		SELECT SUM(SALARY) 
		FROM EMPLOYEES
	) AS 전체연봉
FROM EMPLOYEES

서브쿼리가 SELECT절에 두개나 들어가 복잡하게 작성됐어요.

SELECT EMPLOYEE_ID 직원번호, 
		SALARY 연봉, 
		ROUND(AVG(SALARY) OVER()) 직원평균연봉, 
		SUM(SALARY) OVER() 전체연봉
FROM EMPLOYEES

그런데 over절을 사용하면 매번 서브쿼리를 만들 필요 없이 간단하게 줄일 수 있습니다.

노란색 부분이 이렇게 바뀐거!

짠! 대략적으로 보기만 해도 짧아졌죠?? 

예제가 간단해서 큰 차이가 없네 생각하실 수도 있지만,

OVER는 서브쿼리뿐만 아니라 GROUP BY, ORDER BY 가 복잡하게 섞인 쿼리도 간단하게 표현할 수 있게 해줍니다.

또 RANK(), DENSE_RANK() 등 다양한 집계함수를 지원해줘서 편리하게 데이터를 표현할 수 있어요.

반응형

OVER절 문법/사용법

물론 집계함수(또는 분석함수)는 count, sum, avg말고도 종류가 매우 많아요.

OVER와 함께 사용할 수 있는 함수들이 궁금하다면 '오라클 OVER절의 집계및분석 함수 목록' 페이지를 참고해줍시다.

 

SUM(SCORE) OVER() 하면 전체 점수의 합계가 될거고

AVG(SCORE) OVER()하면 평균 점수가 되겠죠?ㅎㅎ

 

주의해야할 점은,

OVER절은 FROM, WHERE, GROUP BY, HAVING절 이후에 계산됩니다.

SELECT EMPLOYEE_ID, count(*) OVER() 직원수, sum(SALARY) over() 총연봉
FROM EMPLOYEES e 
WHERE JOB_ID = 'IT_PROG'

만약 위와 같이 코드를 짠다면, 직업이 IT_PROG인 직원이라는 전제가 먼저 깔립니다.

즉 전체직원수가 아닌 IT_PROG직원수가 되고, 전체연봉이 아닌 IT_PROG 연봉의 총합이 결과가 되는거죠!

결과

OVER절 괄호안의 구문 

여지껏 작성했던대로 'OVER()' 이렇게 괄호안에 아무것도 없이 사용할 수도 있지만,

OVER(PARTITION BY 컬럼) 이런식으로 괄호안에 조건을 주기위한 절을 추가로 더할 수 있습니다.

 

PARITION BY는 GROUP BY를 OVER절에서 사용하고 싶을 때

ORDER BY는 말그대로 정렬조건을 주고 싶을 때

세부분할기준(windowing clause)은 이 외에 추가적인 분할조건을 주고 싶을 떄 사용합니다. 

 

예를 들어 SUM(salary) OVER()하면 전체연봉인대~~

SUM(SALARY) OVER(PARITION BY JOB_ID) 하면 JOB_ID별 연봉총합이 되는거죠~~!

--##### OVER절 없이 #####
SELECT e1.EMPLOYEE_ID , e1.JOB_ID, 
	 (
	 	SELECT SUM(SALARY) 
	 	FROM EMPLOYEES
	 ) 전체총합, 직무별총합 
FROM EMPLOYEES e1, 	
	(
		SELECT JOB_ID, SUM(SALARY) AS 직무별총합 
		FROM EMPLOYEES 
		GROUP BY JOB_ID 
		ORDER BY JOB_ID
	) e2 
WHERE  e1.JOB_ID  = e2.JOB_ID;

--##### OVER절 사용! #####
SELECT EMPLOYEE_ID, JOB_ID ,
		SUM(SALARY) OVER() 전체총합,
		SUM(SALARY) OVER(PARTITION BY JOB_ID ORDER BY JOB_ID) 직무별총합
FROM EMPLOYEES e ;

첫번째 쿼리랑 두 번째 쿼리랑 똑같은 결과를 보이는데 OVER절을 사용한 두 번째가 훨씬 깔끔하죠?

전체총합은 691,416으로 모든 행에 대해 값이 같다면 직무별총합은 직무별로 값이 같은 것을 확인할 수 있습니다.

 

오늘은 간단히 over절 기초에 대해 알아봤는대요

다음 포스팅에서는 OVER안에 들어가는 WINDOWING CLAUSE에 대해 좀 더 자세히 알아보려해요.

공감은 정보공유에 큰 힘이 됩니다~!

반응형