[SQL2017] 누적분포(Cumulative Distribution) 구하기 - CUME_DIST()

2021. 2. 27. 21:18

/*
-- Title : [SQL2017] 누적분포(Cumulative Distribution) 구하기 - CUME_DIST()
-- Reference : docs.microsoft.com
*/

■ CUME_DIST()

주어진 그룹에서의 '상대적인 누적분포'를 계산
전체를 한 줄로 나열했을 때 '0에서 1사이의 값'으로 표현
쉽게 말해 그룹 내에서 '상대적 위치를 계산'

■ 데이터 셋 만들기

ㅁ 스크립트

-------------------------------------------------
-- 데이터 생성
-------------------------------------------------
DROP TABLE #ttt;

CREATE TABLE #ttt
( akey VARCHAR(10)
, ksic VARCHAR(10)
);

INSERT INTO #ttt
VALUES ('KR01', 'C111')
     , ('KR02', 'C222')
     , ('KR03', 'C333')
     , ('KR04', 'C333')
     , ('KR05', 'C333')
     , ('KR06', 'C444')
     , ('KR07', 'C555')
     , ('KR08', 'C666')
     , ('KR09', 'C666')
     , ('KR10', 'C666')
     , ('KR11', 'C666')
     , ('KR12', 'C777')
     , ('KR13', 'C777');

-------------------------------------------------
-- 데이터 조회
-------------------------------------------------
--▶ 기본 조회
SELECT row_number() OVER(ORDER BY akey) "no"
     , akey, ksic
FROM #ttt;

ㅁ 결과셋

■ 1. akey 정렬을 통한 누적분포 구하기

ㅁ 스크립트

SELECT row_number() OVER(ORDER BY akey) "no"
     , akey, ksic
     , CUME_DIST() OVER (ORDER BY akey) "cume_akey"
FROM #ttt;

ㅁ 결과셋

ㅁ 해석

정렬기준인 akey는 중복이 없기에 13개 행이 일렬로 나열됨
각 akey가 1/13의 비율을 가짐
각 비율을 누적하여 계산
각 akey가 상대적으로 위치하는 % 확인 가능

■ 2. ksic 정렬을 통한 누적분포 구하기

ㅁ 스크립트

SELECT *
     , CUME_DIST() OVER (ORDER BY ksic건수, ksic) "cume_ksic"  
FROM
(
    SELECT row_number() OVER(ORDER BY ksic) "no"
         , akey, ksic
         , COUNT(*) OVER (PARTITION BY ksic) "ksic건수"
    FROM #ttt
) a
ORDER BY ksic건수, ksic;

ㅁ 결과셋

ㅁ 계산과정

ksic의 그룹별 건수 계산하고 정렬
ksic의 전체건수(=ksic 그룹별 건수의 합) 계산
1번의 그룹별 건수를 2번의 전체건수로 나눠 비율 계산
계산된 비율을 정렬순대로 누적(=Cume_Dist)

ㅁ 해석

60% 이상의 KSIC가 KR03, KR05, KR08, KR09, KR10, KR11 6개가 포함됨을 확인

■ 3. ksic 정렬을 통한 누적분포 - 단, ksic건수가 2 이상인 것만

ㅁ 스크립트

SELECT *
     , CUME_DIST() OVER (ORDER BY ksic건수, ksic) "cume_ksic"  
FROM 
(
    SELECT row_number() OVER(ORDER BY ksic) "no"
         , akey, ksic
         , COUNT(*) OVER (PARTITION BY ksic) "ksic건수"
    FROM #ttt 
) a
WHERE ksic건수 > 1
ORDER BY ksic건수, ksic;

ㅁ 결과셋