[Oracle] 데이터 분석 - 순위의 비율 출력 (CUME_DIST)

데이터 분석 - 순위의 비율 출력 (CUME_DIST)

: 특정 데이터 순위의 비율 출력

 

SELECT column1, column2, RANK() OVER (ORDER BY column2 DESC) as RANK
                         DENSE_RANK() OVER (ORDER BY column2 DESC) as DENSE_RANK
                         CUME_DIST() OVER (ORDER BY column2 DESC) as CUME_DIST
    FROM tablename;

: 같은 등수가 있을 경우 마지막 등수로 계산함.

ex: 1,2,3,3,5,6,7,7,7,10

= 1등 1: 1/10

= 2등 1: 2/10

= 3등 2: 4/10

= 5등 1: 5/10

= 6등 1: 6/10

= 7등 3: 9/10

 

 

SELECT column1, column2, column3, RANK() OVER (PARTITION BY column1)
                                               ORDER BY column3 DESC) as RANK,
                                  CUME_DIST() OVER (PARTITION BY column1
                                                    ORDER BY column3 DESC) as COME_DIST
    FROM tablename;                                               

: column1 별 순위 비율