[Oracle] 오라클 COUNT() OVER() 사용법 (PARTITION BY)

오라클 SQL에서 COUNT 함수와 OVER 절을 함께 사용하면 COUNT 함수는 분석 함수라고 부른다. COUNT 함수와 GROUP BY 절과 함께 사용하면 COUNT 함수는 집계함수라고 한다. COUNT 함수를 OVER 절과 함께 사용하면 GROUP BY 절을 사용하지 않고 전체 건수, 그룹별 건수를 집계해서 각 행에 표시할 수 있다. 실무에서 유용하게 사용할 수 있는 구분이니 익혀두면 많은 도움이 될 것이다.

 

목차 전체 총 건수 구하기 순차적 누적 건수 구하기 그룹별 총 건수 구하기 그룹별 건수 + 총 건수 구하기

 

전체 총 건수 구하기

SELECT empno
     , ename
     , job
     , COUNT(*) OVER() AS total_cnt
  FROM emp
 WHERE job IN ('ANALYST', 'MANAGER', 'SALESMAN')

 

 

COUNT(*) OVER() 구문을 사용하면 조회된 데이터의 총건수를 구해서 각 행에 표시할 수 있다.

OVER 절은 데이터가 모두 조회된 후 ORDER BY 절이 실행되지 직전에 실행된다.

 

OVER 절은 GROUP BY 절을 사용하지 않고 집계를 할 때 사용한다. 

 

순차적 누적 건수 구하기

SELECT empno
     , ename
     , job
     , COUNT(*) OVER(ORDER BY empno) AS acc_cnt
  FROM emp
 WHERE job IN ('ANALYST', 'MANAGER', 'SALESMAN')

 

 

OVER 절 내부에 ORDER BY를 사용하면 해당 칼럼으로 조회된 데이터가 정렬이 되고, 정렬된 순서로 누적 건수를 구할 수 있다. ORDER BY의 칼럼의 조합이 유니크하지 않다면 1부터 순차적으로 건수가 표시되지 않을 수 있다.

 

아래의 예제는 ORDER BY 칼럼이 유니크하지 않은 예제이다.

 

 

위의 예제는 순차적으로 누적 건수를 표시하지만 ORDER BY 칼럼의 값이 동일한 경우 동일한 건수가 표시되는 것을 확인할 수 있다. 이 부분은 조금 헷갈릴 수 있으니 조금 유심히 봐야 이해가 될 것이다.

 

그룹별 총 건수 구하기

SELECT empno
     , ename
     , job
     , COUNT(*) OVER(PARTITION BY job) AS job_cnt
  FROM emp
 WHERE job IN ('ANALYST', 'MANAGER', 'SALESMAN') 
 ORDER BY job, empno

 

 

OVER 절 내부에 PARTITION BY를 사용하면 해당 칼럼의 그룹별로 총건수를 구해서 표시한다.

 

그룹별 건수 + 총 건수 구하기

SELECT empno
     , ename
     , job
     , COUNT(*) over(partition BY job) AS job_cnt
     , COUNT(*) over()                 AS total_cnt
  FROM emp
 WHERE job IN ('ANALYST', 'MANAGER', 'SALESMAN')  
 ORDER BY job, empno

 

 

위의 예제는 그룹별 총 건수와 전체 총건수를 함께 구하는 방법이다.

OVER 절은 SELECT 절에 여러 개 사용하여 집계를 할 수 있다. 

 

OVER 절과 다양한 집계함수를 사용하는 방법을 알고 싶으면 아래의 분석함수 사용법을 참고하기 바란다.

예제쿼리(테이블포함).txt

0.00MB

[Oracle] 오라클 분석함수 사용법 (OVER, PARTITION BY)

[Oracle] 오라클 COUNT 함수 사용법

[Oracle] 오라클 REGEXP_COUNT 함수 사용법