WITH 절과 NTILE 함수로 SQL 분위(Percentile) 분석하기

초심을 찾자 2025. 2. 12. 21:11

SMALL

포스팅 계기

최근 프로그래머스에서 SQL 문제를 풀다가 ECOLI_DATA 테이블을 분석하는 문제를 접했다. 이 문제의 핵심 요구사항은 대장균 개체의 크기(SIZE_OF_COLONY)를 기준으로 4개의 그룹으로 나누는 것이었다. 문제를 풀기 위해 먼저 어떤 SQL 기능을 활용할 수 있을지 고민해 봤다.

단순한 ORDER BY와 LIMIT으로 해결할 수 있을까? → 어려움
개별 서브쿼리로 구간을 나누면? → 비효율적이고 가독성이 떨어짐

SELECT 
    ID, SIZE_OF_COLONY,
    CASE 
        WHEN SIZE_OF_COLONY >= (SELECT SIZE_OF_COLONY FROM ECOLI_DATA 
                                ORDER BY SIZE_OF_COLONY DESC 
                                LIMIT 1 OFFSET (COUNT(*) * 1 / 4)) 
        THEN 'CRITICAL'
        WHEN SIZE_OF_COLONY >= (SELECT SIZE_OF_COLONY FROM ECOLI_DATA 
                                ORDER BY SIZE_OF_COLONY DESC 
                                LIMIT 1 OFFSET (COUNT(*) * 2 / 4)) 
        THEN 'HIGH'
        WHEN SIZE_OF_COLONY >= (SELECT SIZE_OF_COLONY FROM ECOLI_DATA 
                                ORDER BY SIZE_OF_COLONY DESC 
                                LIMIT 1 OFFSET (COUNT(*) * 3 / 4)) 
        THEN 'MEDIUM'
        ELSE 'LOW'
    END AS COLONY_NAME
FROM ECOLI_DATA
ORDER BY ID;

윈도우 함수(Window Function)와 CTE(Common Table Expression, WITH 절)의 필요성을 알게 됨

결국 WITH 절을 이용해 데이터를 정리하고, NTILE 함수를 활용해 데이터를 동일한 4개 그룹(분위)으로 나누는 방식으로 문제를 해결할 수 있었다. 이 경험을 바탕으로, SQL에서 데이터를 그룹으로 나누고 분석하는 방법을 정리해 보고자 한다.

WITH 절 (Common Table Expression, CTE)

개념
- WITH 절은 임시 테이블을 생성하는 역할
- 복잡한 서브쿼리를 단순화하고, 가독성과 성능을 개선.
- 재귀 쿼리(Recursive Query)를 사용할 때도 필수적으로 활용
예제

WITH EmployeeSalary AS (
    SELECT EmployeeID, Name, Salary
    FROM Employees
    WHERE Salary > 50000
)
SELECT * FROM EmployeeSalary;

설명
- WITH EmployeeSalary AS (...) 부분에서 먼저 급여가 50,000 이상인 직원들을 필터링한 임시 테이블을 생성
- 이후 메인 쿼리에서 이 테이블을 활용해 데이터를 조회하는 방식

NTILE 함수

개념
- NTILE(n) 함수는 데이터를 n개의 그룹(구간)으로 균등하게 나누는 윈도우 함수(Window Function)야
- 순위(Ranking) 기반 분석에 자주 사용되며, 데이터를 4 분위(Quartile)나 10 분위(Decile)로 구분할 때 유용
예제

SELECT 
    ID, Name, Salary,
    NTILE(4) OVER (ORDER BY Salary DESC) AS Salary_Quartile
FROM Employees;

설명
- NTILE(4) OVER (ORDER BY Salary DESC)는 급여를 기준으로 상위 25%, 50%, 75%, 100%의 그룹으로 데이터를 나눠줌
- 결과적으로 각 직원은 Salary_Quartile 열을 통해 1~4 중 하나의 값을 갖게 됨

WITH 절과 NTILE 함수 결합 예제

예제

WITH Salary_Ranking AS (
    SELECT 
        ID, Name, Salary,
        NTILE(4) OVER (ORDER BY Salary DESC) AS Quartile
    FROM Employees
)
SELECT ID, Name, Salary,
    CASE Quartile
        WHEN 1 THEN 'Top 25%'
        WHEN 2 THEN '50-75%'
        WHEN 3 THEN '25-50%'
        WHEN 4 THEN 'Bottom 25%'
    END AS Salary_Category
FROM Salary_Ranking;

설명
- WITH Salary_Ranking로 직원 급여를 4개 구간으로 나누는 임시 테이블 생성
- CASE 문을 활용해 해당 직원이 어느 분위(Percentile)에 속하는지 구분

결론

오늘 포스팅에서는 WITH 절과 NTILE 함수를 활용하여 SQL에서 데이터를 효과적으로 구간별로 나누는 방법을 살펴보았다. 이러한 예시들을 이해하고 직접 활용해 본다면, 실전에서 발생할 수 있는 다양한 데이터 분석 문제들을 보다 쉽고 효율적으로 해결할 수 있을 거라 생각한다. 독자들도 이번 포스팅을 통해 배운 내용을 바탕으로, 자신의 데이터셋에 맞는 최적의 쿼리를 작성해 보기를 권장한다. 이처럼 실전 예제를 통해 개념을 확실하게 다지는 경험은, 앞으로의 SQL 활용 능력을 한층 더 높여줄 것이다.