통계에 대해 알아보자 (feat. NTILE, LAG, LEAD, CUME_DIST)

안녕하세요, 끙정입니다.

 

소계, 순위에 이어 오늘은 통계에 대해서 알아보겠습니다.

활용도에 따라서 많은 작업이 가능한 함수들입니다.

천천히 살펴보겠습니다.

 

1. NTILE

NTILE(n) 함수는 레코드의 집합을 n개의 영역으로 구분하고 소속 영역을 구합니다.

인수 n은 나눌 영역의 개수를 지정합니다.

 

SELECT NTILE(4) OVER (ORDER BY salary DESC) AS 구간, name, salary FROM tStaff;

SELECT NTILE(4) OVER (PARTITION BY gender ORDER BY salary DESC)
AS 구간, name, gender, salary FROM tStaff;

 

 

첫 번째 쿼리는 월급 순으로 정렬한 뒤 4개의 그룹으로 나눈 것입니다.

통계적으로 사분위수를 확인할 수 있습니다.

두 번째 쿼리는 성별을 따로 나누고 월급 순으로 4개의 그룹으로 나눈 것입니다.

동일한 월급이라도 정렬 순서에 따라 다른 그룹에 속할 수 있으며,

전체 레코드의 숫자가 나누고자 하는 그룹의 수로 딱 떨어지지 않으면 일부 그룹의 숫자가 적습니다.

 

SELECT name, score FROM
(SELECT NTILE(5) OVER (ORDER BY score DESC) AS 구간, tStaff.* FROM tStaff) S
WHERE S.구간 = 3;

 

 

성과를 기준으로 5개의 분위수로 나눈 뒤 가운데 그룹만 출력하였습니다.

 

2. LAG, LEAD

LAG은 이전 행의 필드를 읽고 LEAD는 다음 행의 필드를 읽습니다.

SELECT문은 한 행씩 읽는 것이 보편적이지만 이 함수를 사용하면 결과셋의 이전, 이후행을 읽을 수 있습니다.

 

LAG(필드, [offset, default]) OVER(PARTITION BY ORDER BY)

 

인수로 읽을 필드를 지정하며 단일값을 리턴하는 수식도 가능합니다.

offset은 앞뒤로 몇 행 거리를 읽을 것인지 지정하며 생략시 1을 적용하여 바로 앞 뒤의 행을 읽습니다.

default는 행이 없을 때 읽을 값이며 생략시 NULL입니다.

앞뒤행이 없을 때 0으로 간주하려면 인수에 0을 지정합니다.

MariaDB는 default 옵션을 지원하지 않아 IFNULL(LAG(), 0) 구문을 대신 사용합니다.

 

다음 쿼리는 이전월과 이후월의 매출을 같이 출력합니다.

 

SELECT year, month, sales,
	LAG(sales) OVER(ORDER BY year, month) AS priorMonth,
	LEAD(sales) OVER(ORDER BY year, month) AS nextMonth
FROM tMonthSale;

 

 

이를 활용하면 이전 달과 비교해 이번 달 매출이 얼마나 증가했는지도 파악할 수 있습니다.

 

SELECT year, month, sales,
	sales - LAG(sales) OVER(ORDER BY year, month) AS incsales
FROM tMonthSale;

 

 

연도로 그룹핑을 하면 같은 년도내에서만 이전, 이후 행을 찾습니다.

2021년과 2022년은 연결되어 있지만 년도별로 나누고 싶다면 OVER(PARTITION BY year) 를 추가합니다.

 

SELECT year, month, sales,
	LAG(sales) OVER(PARTITION BY year ORDER BY year, month) AS priorMonth,
	LEAD(sales) OVER(PARTITION BY year ORDER BY year, month) AS nextMonth
FROM tMonthSale;

 

 

3. 누적 비율 CUME_DIST, PERCENT_RANK

CUME_DIST 함수는 행의 누적 비율을 구하여 0~1사이의 실수로 리턴합니다.

PERCENT_RANK 함수도 비슷한 방식으로 동작하되 첫 행을 빼고 계산한다는 점만 다릅니다.

 

SELECT year, month, sales,
	ROUND(CUME_DIST() OVER(ORDER BY year, month) *100, 2) AS cume,
	ROUND(PERCENT_RANK() OVER(ORDER BY year, month) *100, 2) AS rank
FROM tMonthSale;

 

 

CUME_DIST는 첫 행을 포함하여 한 행당 14.29를 누적시켜 나갑니다.

PERCENT_RANK는 첫 행은 0으로 계산하며, 다음 행 부터 16.67을 누적시켜 나갑니다.

 

이 함수를 월급에 적용하면 특정 지원의 월급이 상위 어느 정도 범위에 있는지 알 수 있습니다.

 

SELECT name, salary,
	ROUND(CUME_DIST() OVER(ORDER BY salary) * 100, 2) AS cume,
	ROUND(PERCENT_RANK() OVER(ORDER BY salary) * 100, 2) AS rank
FROM tStaff;

 

허난설헌은 상위 20% 안에 든다.

 

4. 첫 값, 마지막 값 FIRST_VALUE, LAST_VALUE

FIRST_VALUE는 그룹 내의 첫 값을 구하고 LAST_VALUE는 마지막 값을 구합니다.

단 LAST_VALUE가 인식하는 그룹은 지금까지 읽은 행 집합을 의미하기 때문에 항상 자기 자신입니다.

전체 그룹에 대한 마지막 값을 구하려면 ROWS 옵션을 주어야 합니다.

 

SELECT name, salary,
	FIRST_VALUE(salary) OVER(ORDER BY salary) AS first,
	LAST_VALUE(salary) OVER(ORDER BY salary) AS midlast,
	LAST_VALUE(salary) OVER(ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND
	UNBOUNDED FOLLOWING) AS last
FROM tStaff;

 

 

이 값을 통해 최소 월급보다 얼마나 더 받는지, 최대 월급에는 얼마나 모자란지를 확인할 수 있습니다.

 

SELECT name, salary,
	salary - FIRST_VALUE(salary) OVER(ORDER BY salary) AS 최저월급기준,
	salary - LAST_VALUE(salary) OVER(ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND
	UNBOUNDED FOLLOWING) AS 최고월급기준
FROM tStaff ORDER BY name;

 

 

5. 백분위 수 PERCENTILE_CONT, PERCENTILE_DISC

PERCENTILE_CONT는 연속적인 백분위수를, PERCENTILE_DISC는 이산적인 백분위수를 구합니다.

괄호 안에 백분위를 0 ~ 1 사이의 실수로 지정합니다.

WITHIN GROUP절에 정렬 순서를 명시하여 백분위수를 구할 기준을 지정하고,

OVER 절의 PARTITION BY로 그룹핑 기준을 지정합니다.

그룹핑도 필수 구문이어서 생략할 수 없되 전체를 하나의 그룹으로 취급하려면 PARTITION BY NULL로 지정합니다.

 

SELECT year, month, sales,
	PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY sales) OVER(PARTITION BY year) AS cont,
	PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY sales) OVER(PARTITION BY year) AS disc
FROM tMonthSale;

 

 

년도별로 연속 중위값과 이산 중위값을 출력했습니다.

2022년은 행이 3개이므로 두 값다 4150이 출력되었지만,

2021년은 행이 4개 이므로 연속 중위값은 임시의 중위값 4270을 만들었습니다.

그리고 이산 중위값은 중위값에 더 가까운 4120이 출력되었습니다.

 

이를 활용하여 개인별로 부서별 월급 중위값 대비 차액를 출력해보겠습니다.

 

SELECT name, depart, salary,
	PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY salary) OVER(PARTITION BY depart) AS disc,
	salary - PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY salary) OVER(PARTITION BY depart) AS diff
FROM tStaff;

 

 

 

그만 알아보겠습니다.

 

 

출처

http://www.yes24.com/Product/Goods/101637633

김상형의 SQL 정복 : 소문난 명강의 (무료특별판) - YES24