[SQL문제풀기] 대장균의 크기에 따라 분류하기2 - CTE & PERCENT_RANK()

문제

CTE와 PERCENT_RANK() 공부 후 작성한 답

WITH SORTED_TB AS (
    SELECT ID, SIZE_OF_COLONY,
             PERCENT_RANK() OVER (ORDER BY SIZE_OF_COLONY DESC) AS SORT
    FROM ECOLI_DATA
)
SELECT 
    ID,
    CASE
        WHEN SORT <= 0.25 THEN 'CRITICAL'
        WHEN SORT <= 0.50 THEN 'HIGH'
        WHEN SORT <= 0.75 THEN 'MEDIUM'
        ELSE 'LOW'
    END AS COLONY_NAME
FROM SORTED_TB
ORDER BY ID;

CTE(Common Table Expressions)

cte와 서브쿼리의 차이점

// cte
WITH RECENT_SALES AS (
    SELECT ITEM_ID, SALES_AMOUNT
    FROM ITEM_SALES
    WHERE SALE_DATE > '2023-01-01'
)
SELECT ITEM_ID, SUM(SALES_AMOUNT)
FROM RECENT_SALES
GROUP BY ITEM_ID;

// subquery
SELECT ITEM_ID, SUM(SALES_AMOUNT)
FROM (
    SELECT ITEM_ID, SALES_AMOUNT
    FROM ITEM_SALES
    WHERE SALE_DATE > '2023-01-01'
) AS TEMP
GROUP BY ITEM_ID;

cte의 형태

// 기본 형태의 cte 예시
// WITH 절에서 임시 테이블을 정의하고, 이후 메인 쿼리에서 재사용.
WITH RECENT_SALES AS (
    SELECT ITEM_ID, SALES_AMOUNT
    FROM ITEM_SALES
    WHERE SALE_DATE > '2023-01-01'
)
SELECT ITEM_ID, SUM(SALES_AMOUNT)
FROM RECENT_SALES
GROUP BY ITEM_ID;

// 재귀 형태의 cte 예시 
// 조직도를 계층 구조로 탐색할 때 사용.
WITH RECURSIVE EMP_HIERARCHY AS (
    SELECT EMP_ID, EMP_NAME, MANAGER_ID
    FROM EMPLOYEES
    WHERE MANAGER_ID IS NULL
    UNION ALL
    SELECT E.EMP_ID, E.EMP_NAME, E.MANAGER_ID
    FROM EMPLOYEES E
    JOIN EMP_HIERARCHY H ON E.MANAGER_ID = H.EMP_ID
)
SELECT * FROM EMP_HIERARCHY;

PERCENT_RANK

💡

PERCENT_RANK() 함수

특정 행이 차지하는 상대적 위치를 백분율로 표현 PERCENT_RANK() OVER (PARTITION BY column1 ORDER BY column2)

PARTITION BY: 데이터 집합을 나누는 기준이 되는 열을 지정. 이 부분이 없으면 전체 집합에 대해 계산된다. ORDER BY: 순위를 매길 기준이 되는 열을 지정. 이 열의 값에 따라 순위가 결정된다.

// 각 직원의 급여에 대한 상대적 순위를 계산
SELECT 
    id,
    name,
    salary,
    PERCENT_RANK() OVER (ORDER BY salary) AS salary_percent_rank
FROM 
    employees;

// 부서별로 직원의 급여 순위를 계산 -> PARTITION BY를 사용
SELECT 
    id,
    name,
    department_id,
    salary,
    PERCENT_RANK() OVER (PARTITION BY department_id ORDER BY salary) AS salary_percent_rank
FROM 
    employees;