[프로그래머스]대장균 크기에 따라 분류하기2 (PERCENT_RANK), 특정 조건을 만족하는 물고기별 수와 최대 길이 구하기

 

문제 - 대장균 크기에 따라 분류하기2
대장균 개체의 크기를 내름차순으로 정렬했을 때 상위 0% ~ 25% 를 'CRITICAL', 26% ~ 50% 를 'HIGH', 51% ~ 75% 를 'MEDIUM', 76% ~ 100% 를 'LOW' 라고 분류합니다. 대장균 개체의 ID(ID) 와 분류된 이름(COLONY_NAME)을 출력하는 SQL 문을 작성해주세요. 이때 결과는 개체의 ID 에 대해 오름차순 정렬해주세요 . 단, 총 데이터의 수는 4의 배수이며 같은 사이즈의 대장균 개체가 서로 다른 이름으로 분류되는 경우는 없습니다.

WITH RANKED_ECOLI AS (
    SELECT 
        ID, 
        PERCENT_RANK() OVER (ORDER BY SIZE_OF_COLONY DESC) AS SIZE_RANK
    FROM ECOLI_DATA
)

SELECT 
    ID, 
    CASE 
        WHEN SIZE_RANK <= 0.25 THEN 'CRITICAL'
        WHEN SIZE_RANK <= 0.50 THEN 'HIGH'
        WHEN SIZE_RANK <= 0.75 THEN 'MEDIUM'
        ELSE 'LOW'
    END AS COLONY_NAME
FROM RANKED_ECOLI
ORDER BY ID;

PERCENT_RANK()

윈도우 함수로, 각 행이 전체 데이터에서 어느 위치에 있는지를 백분율(0~1)로 나타냄

가장 낮은 값은 항상 0, 가장 높은 값은 1에 가까움

-- PERCENT_RANK 문법
PERCENT_RANK() OVER (ORDER BY column_name [ASC|DESC])

-- PERCENT_RANK 지원이 안되는 언어라면
PERCENT_RANK = (rank - 1) / (total_rows - 1)

 PERCNETILE도 함께 알아두면 좋을듯. MYSQL에선 지원 안하지만 PostgreSQL에서는 지원함

📊 PERCENT_RANK() vs PERCENTILE_*() 차이

항목	PERCENT_RANK()	PERCENTILE_CONT, DISC()
역할	각 행이 전체에서 어느 위치인지 비율로 알려줌	특정 분위(percentile, 예: 상위 90%)의 값을 계산
반환 대상	각 행에 대해 비율(0~1) 반환	전체 집합에 대해 분위값 1개 반환
출력 수	여러 행 (입력 행 수와 동일)	1개의 분위값 (또는 PARTITION BY마다 1개)
출력 값	0 ~ 1 사이의 실수	실제 데이터 값 (수치형 or 이산형)
주 용도	행별 순위 기반 그룹 나누기, 등급 태깅 등	특정 분위수(중앙값, 상위 10% 등) 계산
예시 상황	상위 25% = 'HIGH' 등 라벨링	상위 90%의 기준 점수가 몇인지 알고 싶을 때
MySQL 지원 여부	✅ MySQL 8.0 이상부터 지원	❌ MySQL은 아직 PERCENTILE_CONT/DISC 미지원 (PostgreSQL만)

 

문제 - 특정 조건을 만족하는 물고기별 수와 최대 길이 구하기
FISH_INFO에서 평균 길이가 33cm 이상인 물고기들을 종류별로 분류하여 잡은 수, 최대 길이, 물고기의 종류를 출력하는 SQL문을 작성해주세요. 결과는 물고기 종류에 대해 오름차순으로 정렬해주시고, 10cm이하의 물고기들은 10cm로 취급하여 평균 길이를 구해주세요.
컬럼명은 물고기의 종류 'FISH_TYPE', 잡은 수 'FISH_COUNT', 최대 길이 'MAX_LENGTH'로 해주세요.
단, 잡은 물고기의 길이가 10cm 이하일 경우에는 LENGTH 가 NULL 이며, LENGTH 에 NULL 만 있는 경우는 없습니다.

SELECT 
    COUNT(ID) AS FISH_COUNT, 
    MAX(LENGTH) AS MAX_LENGTH, 
    FISH_TYPE
FROM FISH_INFO 
WHERE FISH_TYPE IN (SELECT  FISH_TYPE
                    FROM FISH_INFO 
                    group by 1
                    HAVING AVG(IFNULL(LENGTH,10)) >= 33)
GROUP BY FISH_TYPE
ORDER BY FISH_TYPE

 

조건을 제대로 확인 안 해서 10분 넘게 걸림.. 그리고 where 절에서 집계 함수 못써도 having에서 쓸 수 있는 거 이제 알았음 ;;