[MySQL] 백분위 연산

💎 PERCENT_RANK()

각 행의 값을 0에서 1까지의 비율로 계산한다.

SELECT 
	[컬럼명],
    PERCENT_RANK() OVER (ORDER BY [정렬기준 컬럼명] DESC) AS PERCENT_RANK
FROM
	[테이블명]
WHERE
	[조건];

• OVER (ORDER BY [정렬기준 컬럼명] DESC) 해당 부분을 통해 상위인지 하위인지 설정한다.
  현재의 경우 상위 순위를 구할 때 사용하면 된다.

상위 25프로의 데이터를 구하고 싶다면 WHERE 절에 PERCENT_RANK <= 25로 조건을 주면 된다.

---

💎 NTILE()

데이터를 n개 그룹으로 나눠 각 행에 그룹 번호를 할당한다.

SELECT *
FROM (
    SELECT 
        [컬럼명],
        NTILE(4) OVER (ORDER BY [컬럼명] DESC) AS QUARTILE
    FROM 
        [테이블명]
) RANKED_DATA
WHERE 
	[조건]; 

• NTILE() 안에 넣은 숫자는 몇 개의 그룹으로 나눌지를 설정하는 것이다.
  4로 설정하면 흔히 아는 사분위 (25, 50, 75, 100)가 생성된다.

상위 25프로의 데이터를 구하고 싶다면 WHERE 절에 QUARTILE = 1로 조건을 주면 된다.

하지만 이는 보간법으로 계산하기에 상위 25프로에 정확히 해당되는 값을 추출할 경우,
실제값이 아닌 두 값 사이의 평균이 추출될 수도 있다.

---

💎 직접 계산

1. 백분위 계산 없이 구하기

SELECT *
FROM 
	[테이블명]
ORDER BY 
	[컬럼명] DESC
LIMIT FLOOR((SELECT COUNT(*) FROM [테이블명]) *[비율]);

LIMIT 함수는 여기 참고!!

정렬한 상태에서 전체 수를 세고, 원하는 비율의 인덱스(FLOOR로 정수 반환)까지 행들을 선택하는 쿼리이다.

2. 실제 값 기준으로 필터링하기

SELECT 
	[컬럼명]
FROM 
	[테이블명]
WHERE 
	[컬럼명] >= (
        SELECT MIN([컬럼명])
        FROM (
            SELECT [컬럼명]
            FROM [테이블명]
            ORDER BY [컬럼명] DESC
            LIMIT FLOOR((SELECT COUNT(*) FROM [테이블명]) * 0.25)
        ) SUBQUERY
);

이 방법은 좀 복잡해보일 수 있지만, 단순히 생각했을 때의 방식인 것 같다!

일단 내림차순으로 정렬 후 상위 25프로에 해당하는 값들 중 최소 값을 뽑은 뒤,
해당 값으로 상위 25프로의 값을 다시 산출하게 되는 흐름이다.

어쩌면 굳이 뽑은 결과값을 이용해 다시 추출하는? 방식이다.

---

💎 PERCENTILE_CONT 와 PERCENTILE_DISC

해당 함수는 8.0.2 이후 버전에서 사용가능!!!

1. PERCENTILE_CONT

SELECT PERCENTILE_CONT(0.75) 
	WITHIN GROUP 
    (ORDER BY [컬럼명]) AS PERCENTILE_75
FROM 
	[테이블명];

이 부분은 오름차순으로 정렬을 했기 때문에, 75번째 값을 추출함으로써 상위 25프로의 값을 얻어냈다.

2. PERCENTILE_DISC

SELECT PERCENTILE_DISC(0.75) 
	WITHIN GROUP 
    (ORDER BY [컬럼명]) AS PERCENTILE_75
FROM 
	[테이블명];

위와 동일한 방식이지만... 이건 실제 값을 추출한다고 한다.

🚨차이를 보자면!!
PERCENTILE_CONT는 보간법을 사용하여 실제 데이터 40과 50 사이가 상위 25프로에 해당하는 경우 45를 추출하게 된다.
하지만 PERCENTILE_DISC는 40과 50 사이에서 상위 값이자 실제 값인 50을 추출하게 된다.