[MySQL] Window 함수 기반 그룹 & 범위 계산 함수 정리

MySQL 8.0 이상에서 지원하는, 대표적인 함수들을 정리해보았다.

1. NTILE

정의: 그룹 내 데이터를 정렬 후 지정한 bucket 수로 나누고, 각 행에 bucket 번호를 부여한다.

사용 예시:

SELECT 
    id,
    size_of_colony,
    NTILE(4) OVER (ORDER BY size_of_colony) AS tile
FROM ECOLI_DATA
ORDER BY id;

결과 예시

id	size_of_colony	tile
1	10	1
2	23	2
3	41	3
4	58	4

• 1~4 숫자로 각 행이 속한 그룹을 나타낸다.
• 주로 분위수(quartile) 계산에 사용된다.
• NTILE은 마지막 단계에 그룹 이름 매핑할 때 적용한다.

2. CUME_DIST

정의: 그룹 내에서 현재 행의 누적 분포 비율을 계산한다.

사용 예시:

SELECT 
    id,
    size_of_colony,
    CUME_DIST() OVER (ORDER BY size_of_colony) AS cumulative_ratio
FROM ECOLI_DATA;

결과 예시

id	size_of_colony	cumulative_ratio
1	10	0.25
2	23	0.50
3	41	0.75
4	58	1.0

• 누적 비율을 계산하여 상위 몇 %에 속하는지 판단할 때 사용한다.
• NTILE과 함께 분위수 기준 그룹을 나눌 때 참고 자료로 활용 가능하다.

3. DENSE_RANK

정의: 값이 동일한 경우 같은 순위를 부여하며, 순위를 건너뛰지 않는다.

사용 예시:

SELECT 
    id,
    size_of_colony,
    DENSE_RANK() OVER (ORDER BY size_of_colony DESC) AS dense_rank
FROM ECOLI_DATA;

id	size_of_colony	dense_rank
4	58	1
3	41	2
2	41	2
1	10	3

• 순위가 연속적으로 부여되므로, 동일값 처리 후 그룹 배치에 유용하다.

4. RANK

정의: 값이 동일하면 같은 순위를 부여하며, 다음 순위는 건너뛴다.

사용 예시:

SELECT 
    id,
    size_of_colony,
    RANK() OVER (ORDER BY size_of_colony DESC) AS rank
FROM ECOLI_DATA;

결과 예시

id	size_of_colony	rank
4	58	1
3	41	2
2	41	2
1	10	4

• 동일값이 있을 경우 순위가 건너뛴다.
• 그룹화 시, 동일 순위 처리 방식이 NTILE 결과에 영향을 줄 수 있다.

5. ROW_NUMBER

정의: 중복 여부와 상관없이 각 행에 고유 순번을 부여한다.

사용 예시:

SELECT 
    id,
    size_of_colony,
    ROW_NUMBER() OVER (ORDER BY size_of_colony DESC) AS row_num
FROM ECOLI_DATA;

결과 예시

id	size_of_colony	row_num
4	30	1
2	20	2
3	20	3
1	10	4

• 고유 식별 순번을 만들 때 사용한다.
• 그룹 배치 후 순서 기준으로 활용 가능하다.

6. LAG / LEAD

정의: 이전 또는 다음 행 값에 접근할 수 있다.

사용 예시:

SELECT 
    id,
    size_of_colony,
    LAG(size_of_colony, 1) OVER (ORDER BY id) AS prev_colony,
    LEAD(size_of_colony, 1) OVER (ORDER BY id) AS next_colony
FROM ECOLI_DATA;

결과 예시

id	size_of_colony	prev_colony	next_colony
1	10	NULL	23
2	23	10	41
3	41	23	58
4	58	41	NULL

• 전후 값 비교나 증감율 계산에 사용된다.
• NTILE, 분위수 계산 후 연속 범위 체크에 활용 가능하다

7. PERCENT_RANK

정의: 그룹 내에서 행의 순위를 백분위 순위로 반환한다.

사용 예시:

SELECT 
    id,
    size_of_colony,
    PERCENT_RANK() OVER (ORDER BY size_of_colony) AS percent_rank
FROM ECOLI_DATA;

결과 예시:

id	size_of_colony	percent_rank
1	10	0.0
2	23	0.3333
3	41	0.6667
4	58	1.0

• 상위 몇 %에 속하는지 판단할 때 유용하다.
• NTILE과 함께 그룹 기준 참고자료로 활용 가능하다.

8. RATIO_TO_REPORT

정의: 그룹 내에서 값이 전체 합에서 차지하는 비율을 계산한다.

사용 예시:

SELECT 
    id,
    size_of_colony,
    size_of_colony / SUM(size_of_colony) OVER () AS ratio
FROM ECOLI_DATA;

결과 예시

id	size_of_colony	ratio
1	10	0.0714
2	23	0.1643
3	41	0.2929
4	58	0.4143

• 점유율, 구성비 계산에 사용된다.
• 그룹 배치 후 시각화에 활용 가능하다.

9. WIDTH_BUCKET

정의: 값을 균등한 간격의 버킷(구간)에 할당한다.

사용 예시:

SELECT 
    id,
    size_of_colony,
    WIDTH_BUCKET(size_of_colony, 0, 100, 4) AS bucket
FROM ECOLI_DATA;

결과 예시

id	size_of_colony	bucket
1	10	1
2	23	1
3	41	2
4	58	3

• 히스토그램, 구간 분석에 사용된다.
• NTILE과 달리 구간 너비를 직접 지정 가능하다.