OVER(), PARTITION BY - Oracle

■ OVER()

    윈도우 함수중 하나로 집계 함수와 함께 사용하며, group by, order by절을 이용한 복잡한 서브쿼리를 개선할 수 있      는 함수.

    또한, OVER() 함수를 사용함으로써 출력되는 칼럼과 그룹화할 칼럼을 일치시켜야 하는 문제점도 개선할 수 있다.

    이는 데이터 분석 및 보고서 생성 시 더 유연하고 효율적인 쿼리를 작성하는 데 도움을 준다.

 

 

 

■ PARTITION BY

    OVER() 함수 안에 사용할 수 있는 값(분할 기준 절)중 하나로 데이터를 특정 칼럼 값에 따라 그룹으로 나누는 역할.

    이를 통해 각 그룹 내에서 집계 함수가 개별적으로 계산되므로, 복잡한 서브쿼리를 사용하지 않고도 다양한 분석을        수행할 수 있다.

 

 

 

■ 예시

※ 예시 하단에 테이블 정보 표기.

 

1) 직원들의 월급 데이터를 분석하여 각 나이별로 평균 월급을 조회하는 쿼리.

SELECT NAME
	, AGE
	, SALARY
	, DEPT
	, ROUND(AVG(SALARY) OVER(PARTITION BY AGE), 0) AS AVG
FROM TB_EMP;

 

→ 자기 나이에 해당하는 평균 월급을 AVG 칼럼에서 확인할 수 있다.

 

 

2) 직원들의 월급 데이터를 분석하여 같은 부서와 등록 날짜 내에서 나이 순서대로 최대 급여를 조회하는 쿼리.

SELECT NAME
	, AGE
	, SALARY
	, DEPT
	, REG_DATE 
	, ROUND(MAX(SALARY) OVER(PARTITION BY DEPT, REG_DATE ORDER BY AGE), 0) AS MAX
FROM TB_EMP;

 

→  DEPT와 REG_DATE로 그룹화하고, 각 그룹 내에서 나이 순으로 정렬한다.

각 행에의 최대 급여를 MAX 칼럼에서 확인할 수 있다.

 

 

※ TB_EMP 테이블 정보

SELECT * FROM TB_EMP ORDER BY SEQ;

 

 

■ 마무리

 

실무에서는 정산 데이터를 다뤘었다. 전일 거래 데이터를 종합하여 매일 정산 데이터가 생기는 배치가 돌고, 월 초에 전월의 최종 정산 데이터를 확인하여 정산하는 구조였는데, 요청 사항은 전월 정산 데이터를 그 다음 달로 이월할 수 있게 하는 것이었다. 요청 사항을 들었을 때 업체별, 정산일별 그룹화 하여 SUM된 금액을 보여주면 된다고 생각하여 간단한 작업이라고 생각했지만 막상 쿼리를 짜는 동안 원하는 쿼리를 짤 수 없었고, 쿼리가 점점 복잡해져 갔다. 여러 삽질 끝에 다행히 이를 해결해 줄 문법이 찾았고 결국 원하는 결과를 얻을 수 있었다. 실무에서 사용한 쿼리를 보여줄 수 없지만... TB_EMP를 이용하여 최대한 비슷하게 예시를 들어보겠다.

 

SELECT AGE
	, DEPT
	, SUM(SALARY)
	, REG_DATE
	, RN
FROM 
	(SELECT AGE
		, DEPT
		, SALARY
		, REG_DATE
		, ROW_NUMBER() OVER(PARTITION BY AGE, DEPT ORDER BY REG_DATE DESC) AS RN
	FROM TB_EMP)
WHERE RN = 1
GROUP BY AGE
	, DEPT
	, REG_DATE
	, RN;

 

→  위 쿼리는 각 연령대와 부서 내에서 가장 최근 등록 날짜를 가진 직원들이 필터링되고, 그 직원들의 급여가 합산되어 출력된다.

 

정산금액이 확정된 월말의 데이터만 사용하기 때문에 배치가 실행된 날짜를 기준으로 정렬을 했고, 업체별, 정산일별 금액이 계산되게끔 쿼리를 짰다.

처음부터 위 내용을 인지한 상태로 쿼리를 짰으면 원하는 결과의 쿼리를 구현하는데 조금 덜한 노력과 시간이 들었을 것이다. 모르는 상태로 원하는 결과를 얻겠다고 여러 번 삽질하고 내 능력의 부족함에 스트레스를 받았다.

어려분은 이 문법을 통해 더 간략하고 좋은 쿼리를 짜길 바란다.