[Oracle] RATIO_TO_REPORT를 이용하여 비율 구하기

작성일자 : 2023-11-11

Ver 0.1.1

 

1. Intro

Oracle에서 쿼리를 작성할 때, 전체 합계 대비 비율 또는 백분율을 구하고 싶은 경우가 있을 수 있다.

이를 위해서 분모의 합을 확인 후에 컬럼에서 나눠 주면서 비율을 구할 수도 있겠지만, RATIO_TO_REPORT() OVER() 함수를 이용하면 그럴 필요 없이 바로 비율이 반환된다. 그 결과에 100을 곱하면 백분율(%)로 사용할 수도 있다.

SQL Language Reference

---

2. How to

 

2-1) 기본 사용법

Syntax에 맞게 EMP 테이블에 적용해보았을 때, 결과는 아래와 같다. 

 

아래 쿼리는 개벼행에 대한 비율로 expr 자리에 컬럼 값 그대로 SAL이 들어간다.

소수점에 대한 지정이 없다보니, 소수점 자리수가 굉장히 길게 나온다.

SELECT ENAME
      ,SAL
      ,RATIO_TO_REPORT(SAL) OVER() AS SAL_RATIO
  FROM SCOTT.EMP
 WHERE SAL > 1500

이에 소수점 두자리로 표현하기 위해 ROUND 함수를 적용하면 된다.

SELECT ENAME
      ,SAL
      ,ROUND(RATIO_TO_REPORT(SAL) OVER(),2) AS SAL_RATIO
  FROM SCOTT.EMP
 WHERE SAL > 1500

위 결과에서 백분율 표현을 위해 100을 곱하고 '%' 문구를 붙이면 100분율로 표현도 가능하다.

SELECT ENAME
      ,SAL
      ,ROUND(RATIO_TO_REPORT(SAL) OVER(),3) * 100 || '%' AS SAL_RATE
  FROM SCOTT.EMP
 WHERE SAL > 1500

2-2) GROUP BY 비율 구하기

집계함수를 통해 GROUP BY를 사용하고자 할때는 , 'expr' 자리에도 집계함수 구문을 입력해주어야한다.

SELECT JOB
      ,SUm(SAL)
      ,ROUND(RATIO_TO_REPORT(SUM(SAL)) OVER(),3) * 100 || '%' AS SAL_RATE
  FROM SCOTT.EMP
 GROUP BY JOB

2-3) ROLLUP 백분율 구하기

ROLLUP 함수를 사용해 합계를 표현하고 싶을 때는, 서브쿼리와 GROUPING 및 DECODE 를 활용해 결과를 구할 수 있다.

[Oracle] 소계 및 총계 구하기(ROLLUP, GROUPING_ID 함수)

SELECT DECODE(GROUPING(JOB), 1, 'TOTAL', JOB) AS JOB
     , SUM(TOTAL_SAL)                        AS TOTAL_SAL
     , ROUND(SUM(SAL_RATIO), 3) * 100 || '%' AS SAL_RATIO
  FROM (
         SELECT JOB
              , SUM(SAL) AS TOTAL_SAL
              , RATIO_TO_REPORT(SUM(SAL)) OVER() AS SAL_RATIO
           FROM SCOTT.EMP
          GROUP BY JOB
       )
 GROUP BY ROLLUP(JOB)

2-4) PARTITION BY를 사용하여 백분율 구하기

OVER 함수에서 PARTITION BY 를 사용한다면 할당한 그룹별로 구성비율을 구할수 있다.

SELECT ENAME
     , JOB
     , SAL
     , ROUND(RATIO_TO_REPORT(SAL) OVER(PARTITION BY JOB), 3) * 100 || '%' AS SAL_RATE
  FROM SCOTT.EMP
 WHERE JOB IN ('ANALYST', 'MANAGER')

---

3. Wrap up

업무를 하다보며 구성 비율을 구하는 Needs는 종종 있는 편이다.

Tableau 시트를 개발할 때도 총계 및 구성 비율을 보고자 하는 경우도 있다.

이럴 때마다 쿼리를 복잡하게 작성하거나 엑셀에서 계산하거나 하곤 했었는데, RATIO_TO_REPORT 함수를 활용하면 쉽게 구할 수 있으니 잘 활용해 보도록 해야겠다.