OVER
통계나 집계를 구할 때 집계함수(sum 등)는 집계되지 않은 컬럼과 같이 사용할 수 없다.
고로 서브쿼리를 통해 집계데이터를 구하게 되는데, 이때 서브쿼리대신 OVER절을 사용하면 간단하게 집계데이터를 구하는게 가능함.
또한 GROUP BY, ORDER BY가 복잡하게 섞인 쿼리까지 간단히 표현가능.
PARTITON BY
특정 열을 기준으로 데이터를 나누는 함수.
GROUP BY절을 사용하지 않고 조회된 각 행에 집계된 값을 표현할때 OVER절과 함께 사용됨.
이때 PARTITON BY를 사용하지 않으면 모든 행이 단일행으로 취급된다.
GROUP BY와의 차이점
GROUP BY절을 사용하면 데이터를 분류하여 집계하는 부분은 같으나, 각 분류에 따라 하나의 행만 출력하게 된다.
PARTITON BY의 경우 각 분류에 따라 하나의 행으로 압축하여 출력하지 않고 모든 row를 그대로 출력하되 집계컬럼이 추가로 출력됨.
-PARTITON BY 사용-
~생략~
, ROW_NUMBER() OVER (ORDER BY emp.c_직종번호, emp.직원명) AS 행번호
, ROW_NUMBER() OVER (PARTITION BY emp.직종번호 ORDER BY emp.직종번호, emp.직원명) AS rownumber
, COUNT(sal.직원명) OVER (PARTITION BY emp.직종번호 ORDER BY emp.직종번호) AS rowcount
, SUM(sal.`월급여`) OVER (PARTITION BY emp.직종번호 ORDER BY emp.직종번호) AS 직종별월급여
, SUM(sal.`기본급`) OVER (PARTITION BY emp.직종번호 ORDER BY emp.직종번호) AS 직종별기본급
, SUM(sal.`지급합`) OVER (PARTITION BY emp.직종번호 ORDER BY emp.직종번호) AS 직종별지급합
~생략~
-GROUP BY 사용-
~생략~
, ROW_NUMBER() OVER (ORDER BY emp.c_직종번호, emp.직원명) AS 행번호
, SUM(sal.`월급여`) AS 직종별월급여
, SUM(sal.`기본급`) AS 직종별월급여
, SUM(sal.`지급합`) AS 직종별지급합
~생략~
GROUP BY emp.직종번호
~생략~
=> 각 컬럼이 모두 다른 직원의 데이터이나 GROUP BY사용시에는 직종당 한명의 직원만 출력된다.
(앞부분 컬럼은 생략함. 원래는 모두 다른사람의 데이터임.)
아래 SUM OVER PARTITON BY 사용예시의 sql문을 사용하였으니 설명참고.
SUM OVER PARTITON BY
사용
SUM() OVER(PARTITION BY [그룹핑할컬럼] ORDER BY [정렬할컬럼])
사용예시
아래는 직종별 월급여, 기본급, 지급금액합을 구하는 sql문의 select절의 일부.
rownumber는 각 직종별 row_number, rowcount는 해당 직종의 rownumber의 최대값(직종의 마지막 행인지를 체크할때 사용함), 나머지 세 값은 직종별 합계를 구한 값이다.
※ sal은 직원의 월급여에 데이터가 저장된 테이블, emp는 직원에 대한 정보가 저장된 테이블임.
-PARTITON BY 사용-
, ROW_NUMBER() OVER (PARTITION BY emp.직종번호 ORDER BY emp.직종번호, emp.직원명) AS rownumber
, COUNT(sal.직원명) OVER (PARTITION BY emp.직종번호 ORDER BY emp.직종번호) AS rowcount
, SUM(sal.`월급여`) OVER (PARTITION BY emp.직종번호 ORDER BY emp.직종번호) AS 직종별월급여
, SUM(sal.`기본급`) OVER (PARTITION BY emp.직종번호 ORDER BY emp.직종번호) AS 직종별기본급
, SUM(sal.`지급합`) OVER (PARTITION BY emp.직종번호 ORDER BY emp.직종번호) AS 직종별지급합참고 링크
오라클 over절에 대해 알아보자
SQL OVER 절
오라클 PARTITION BY 사용법 정리 (분석함수)
