[Oracle] 오라클 COUNT OVER 사용 방법 / 그룹별 중복 행(값) 찾기

COUNT와 OVER를 같이 사용하면 GROUP BY 로 그룹화하지 않아도 건수 집계 및 분석이 가능합니다.

• COUNT만 사용하는 경우 GROUP BY 사용
• COUNT() OVER() 로 사용하는 경우 GROUP BY 없이 사용

전체 행의 개수 구하기 / 조건 부여

SELECT EMPNO
     , ENAME
     , JOB
     , SAL
     , DEPTNO
     , COUNT(*) OVER() AS TOT_CNT
     , COUNT(CASE WHEN SAL >= 2000 THEN EMPNO END) OVER() AS CNT_2000
    FROM EMP
    WHERE JOB IN ('MANAGER','CLERK','ANALYST')

COUNT OVER 전체 행 개수

• OVER() 안을 공란으로 두면 전체 행의 개수 집계
• COUNT() 안에 CASE WHEN 등으로 조건을 설정 가능

그룹별 최대 순번 (유니크 값 지정)

SELECT EMPNO
     , ENAME
     , JOB
     , DEPTNO
     , COUNT(*) OVER(ORDER BY EMPNO) AS ASC_CNT_PK
    FROM EMP
    WHERE JOB IN ('MANAGER','CLERK','ANALYST')
    ORDER BY ASC_CNT_PK

COUNT OVER 유니크 값 정렬

• OVER() 내 ORDER BY 옵션에 다른 행과 중복되지 않는 유니크한 값(보통 PK) 지정 시 +1씩 증가하는 순번 적용
• (PARTITION BY 옵션을 사용하지 않은 경우)

그룹별 최대 순번 (중복 값 지정)

SELECT EMPNO
     , ENAME
     , JOB
     , DEPTNO
     , COUNT(*) OVER(ORDER BY DEPTNO) AS ASC_CNT_DOUBLE
    FROM EMP
    WHERE JOB IN ('MANAGER','CLERK','ANALYST')
    ORDER BY DEPTNO

COUNT OVER 중복 값 정렬

• OVER() 내 ORDER BY 옵션에 다른 행과 중복되는 값 지정 시 중복되는 행 개수 표시
• 그룹별 누적된 행 개수의 최대값을 중복되는 행에 표시
• 위 예시는 PARTITION BY를 사용하지 않아서 전체 행에 대해 그룹 지정 

그룹별 중복되는 행(값) 집계

SELECT EMPNO
     , ENAME
     , JOB
     , DEPTNO
     , COUNT(*) OVER(PARTITION BY DEPTNO) AS DEPT_CNT
    FROM EMP
    WHERE JOB IN ('MANAGER','CLERK','ANALYST')
    ORDER BY DEPTNO

COUNT OVER 중복 행 집계

• OVER() 내 PARTITION BY 를 사용하면 해당 컬럼으로 그룹 지정 후 개수 집계
• 그룹 지정한 컬럼이 중복되는 값이면 중복되는 행의 개수만큼 카운트되어 표시

그룹별 중복 값 집계 시 조건 설정

SELECT EMPNO
     , ENAME
     , JOB
     , SAL
     , DEPTNO
     , COUNT(*) OVER(PARTITION BY DEPTNO) AS DEPT_CNT
     , COUNT(CASE WHEN SAL >= 2000 THEN EMPNO END) OVER(PARTITION BY DEPTNO) AS CNT_2000
    FROM EMP
    WHERE JOB IN ('MANAGER','CLERK','ANALYST')
    ORDER BY DEPTNO

COUNT OVER 집계 조건 설정

• COUNT() 내에 CASE WHEN 을 사용하여 중복 값 집계 시 조건 설정 가능
• CASE WHEN 으로 조건 설정 시 PARTITION BY에 사용하지 않은 컬럼 사용 권장

응용 예제 - 그룹별 중복 행 개수 및 순번

SELECT EMPNO
     , ENAME
     , JOB
     , DEPTNO
     , COUNT(*) OVER(PARTITION BY DEPTNO) AS DEPT_CNT
     , ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY EMPNO) AS DEPT_NUM
     , COUNT(*) OVER() AS TOT_CNT
    FROM EMP
    WHERE JOB IN ('MANAGER','CLERK','ANALYST')
    ORDER BY DEPTNO

ROW_NUMBER() 조합 예시

• ROW_NUMBER() 을 사용해 COUNT OVER와 동일한 그룹을 지정하면 해당 그룹별 순번 부여 가능