MySQL(MariaDB) 사분위수 구하기

특정 데이터 분포를 쉽게 확인할 수 있는 수치로는 최소값, 중간값, 평균값, 최대값 그리고 사분위수가 있다.

사분위수는 데이터의 균등한 분할로 25%, 50%, 75%, 100%를 나타내며 1사분위수(1Q)는 25% 값에 해당한다.

사분위수를 알아내면 BOXPLOT과 같은 Chart를 통해 데이터 분포를 시각화 할 수 있을 것이다.

아쉽게도 R과 같은 통계언어의 경우는 쉽게 사분위수를 구할 수 있으나 MySQL의 경우는 지원하는 내장함수가 존재하지

않는다. 그래서 SQL를 통해 사분위수를 구할 수 있는 방법을 알아 보고자 한다.

boxplot 해석을 돕기 위한 그림(출처)

 
SET GROUP_CONCAT_MAX_LEN = 10485760;
SELECT
MIN(COLUMN_NAME) AS 'MIN',
SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(COLUMN_NAME ORDER BY COLUMN_NAME SEPARATOR ','),',', 5/100 * COUNT(*) + 1), ',', -1)  AS `5TH PER`,
SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(COLUMN_NAME ORDER BY COLUMN_NAME SEPARATOR ','),',', 25/100 * COUNT(*) + 1), ',', -1)  AS `1ST QU`,
SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(COLUMN_NAME ORDER BY COLUMN_NAME SEPARATOR ','),',', 50/100 * COUNT(*) + 1), ',', -1)  AS `MEDIAN`,
AVG(COLUMN_NAME) AS 'MEAN',
SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(COLUMN_NAME ORDER BY COLUMN_NAME SEPARATOR ','),',', 75/100 * COUNT(*) + 1), ',', -1)  AS `3RD QU`,
SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(COLUMN_NAME ORDER BY COLUMN_NAME SEPARATOR ','),',', 95/100 * COUNT(*) + 1), ',', -1)  AS `95TH PER`,
MAX(COLUMN_NAME) AS 'MAX'
FROM TABLE_NAME

원리는 간단하다. GROUP_CONCAT 함수로 데이터를 합쳐서 분위수에 해당하는 수치를 구해 SUBSTRING_INDEX함수로 데이터를 추출하는 방법이다. 참 쉽죠? 

아! 그리고  합쳐지는 데이터가 많을 경우 꼭 SET GROUP_CONCAT_MAX_LEN = 10485760; 와 같은 설정이 필요함

끝.

참고 : http://web.performancerasta.com/metrics-tips-calculating-95th-99th-or-any-percentile-with-single-mysql-query/