[WINDOW FUNCTION] 프로그래머스 - 대장균의 크기에 따라 분류하기 2

cleanUrl: /WINDOW-FUCTION-프로그래머스-대장균의-크기에-따라-분류하기-2

문제 설명

• 대장균 개체의 크기를 내림차순으로 정렬했을 때 4분위로 나눠 개체의 ID와 해당 개체가 속하는 분류를 출력하는 문제

• 분류 기준:
• CRITICAL: 상위 0% ~ 25%
• HIGH: 상위 26% ~ 50%
• MEDIUM: 상위 51% ~ 75%
• LOW: 상위 76% ~ 100%

예제 입력/출력

ECOLI_DATA

출력

문제 풀이

• 접근1 윈도우 함수 - RANK()
• 대장균 개체의 크기(SIZE_OF_COLONY)를 내림차순 정렬한 후, 각 개체의 순위를 매긴다.
• 개체의 순위를 기준으로 상위 25%씩 4개 그룹(CRITICAL, HIGH, MEDIUM, LOW)으로 분류한다.
• 같은 크기의 개체는 같은 순위를 가져야 하므로 RANK() 또는 DENSE_RANK()를 사용할 수 있다.
• RANK(): 동일한 값이면 같은 순위를 부여하지만, 다음 순위를 건너뜀 (ex. 1, 2, 2, 4)
• DENSE_RANK(): 동일한 값이면 같은 순위를 부여하고, 다음 순위를 건너뛰지 않음 (ex. 1, 2, 2, 3)
• ROW_NUMBER(): 동일한 값이라도 무조건 개별 순위를 부여함 (ex. 1, 2, 3, 4)
 
• RANK()와 COUNT() 활용하여 4분위 구하는 서브 쿼리

WITH RANKED AS (
    SELECT ID,
    RANK() OVER (ORDER BY SIZE_OF_COLONY DESC) AS RN,
    COUNT(*) OVER () AS TOTAL_COUNT
    FROM ECOLI_DATA
)

ID	RN	TOTAL_COUNT
6	1	8
7	2	8
3	3	8

• CASE 문을 활용하여 분위별로 그룹화하는 메인 쿼리

SELECT ID,
       CASE
            WHEN RN <= TOTAL_COUNT * 0.25 THEN 'CRITICAL'
            WHEN RN <= TOTAL_COUNT * 0.5 THEN 'HIGH'
            WHEN RN <= TOTAL_COUNT * 0.75 THEN 'MEDIUM'
            ELSE 'LOW'
        END AS COLONY_NAME
FROM RANKED

ID	COLONY_NAME
1	MEDIUM
2	LOW
3	HIGH

• 접근2 윈도우 함수 - PERCENT_RANK()
• PERCENT_RANK()는 현재 행의 값보다 작은 파티션 값의 백분율(0~1)을 반환한다.
• 따라서, TOTAL_COUNT * 0.25 같은 추가 계산이 필요없기 때문에 쿼리문이 더 간결해진다.

WITH RANKED AS (
    SELECT ID,
				   RANK() OVER (ORDER BY SIZE_OF_COLONY DESC) AS RN,
				   COUNT(*) OVER () AS TOTAL_COUNT
    FROM ECOLI_DATA
)

풀이 코드

• 접근1 윈도우 함수 - RANK()

WITH RANKED AS (
    SELECT ID,
				   RANK() OVER (ORDER BY SIZE_OF_COLONY DESC) AS RN,
				   COUNT(*) OVER () AS TOTAL_COUNT
    FROM ECOLI_DATA
)
SELECT ID,
       CASE
            WHEN RN <= TOTAL_COUNT * 0.25 THEN 'CRITICAL'
            WHEN RN <= TOTAL_COUNT * 0.5 THEN 'HIGH'
            WHEN RN <= TOTAL_COUNT * 0.75 THEN 'MEDIUM'
            ELSE 'LOW'
        END AS COLONY_NAME
FROM RANKED
ORDER BY ID

• 접근2 윈도우 함수 - PERCENT_RANK()

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

참고 자료

MySQL :: MySQL 8.4 Reference Manual :: 14.20.1 Window Function Descriptions

MySQL :: MySQL 8.4 Reference Manual :: 14.20.1 Window Function Descriptions

This section describes nonaggregate window functions that, for each row from a query, perform a calculation using rows related to that row. Most aggregate functions also can be used as window functions; see Section 14.19.1, “Aggregate Function Descriptions”.

TISTORY[프로그래머스] LV3. 대장균의 크기에 따라 분류하기 2 - MySQL

[프로그래머스] LV3. 대장균의 크기에 따라 분류하기 2 - MySQL

https://school.programmers.co.kr/learn/courses/30/lessons/301649 프로그래머스코드 중심의 개발자 채용. 스택 기반의 포지션 매칭. 프로그래머스의 개발자 맞춤형 프로필을 등록하고, 나와 기술 궁합이 잘 맞는 기업들을 매칭 받으세요.programmers.co.kr문제대장균 개체의 크기를 내름차순으로 정렬했을 때 상위 0% ~ 25% 를 'CRITICAL', 26% ~ 50% 를 'HIGH', 51% ~ 75% 를 'MEDIUM', 76% ~ 100% 를 'LOW' 라고 분류합니다. 대장균 개체의 ID(ID) 와 분류된 이름(COLONY_NAME)을 출력하는 SQL 문을 작성 풀이1윈도우 함수인 ROW_NUMBER()를 활용하여 순위를 매기기 (이 순위는 오름..

TISTORY