[프로그래머스 SQL 코딩테스트 연습] Lv3. 대장균의 크기에 따라 분류하기2 (MySQL)

🤔 문제

대장균들은 일정 주기로 분화하며, 분화를 시작한 개체를 부모 개체, 분화가 되어 나온 개체를 자식 개체라고 합니다.

다음은 실험실에서 배양한 대장균들의 정보를 담은 `ECOLI_DATA` 테이블입니다. `ECOLI_DATA` 테이블의 구조는 다음과 같으며, `ID`, `PARENT_ID`, `SIZE_OF_COLONY`, `DIFFERENTIATION_DATE`, `GENOTYPE` 은 각각 대장균 개체의 ID, 부모 개체의 ID, 개체의 크기, 분화되어 나온 날짜, 개체의 형질을 나타냅니다.

 

최초의 대장균 개체의 `PARENT_ID` 는 `NULL` 값입니다.

 

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

 

 

예시

예를 들어 `ECOLI_DATA` 테이블이 다음과 같다면

 

기준에 의해 분류된 대장균들의 ID는 다음과 같습니다.

• `CRITICAL` (상위 0% ~ 25%) : ID 6, ID 7
• `HIGH` (상위 26% ~ 50%) : ID 3, ID 5
• `MEDIUM` (상위 51% ~ 75%) : ID 1, ID 4
• `LOW` (상위 76% ~ 100%) : ID 2, ID 8

따라서 결과를 ID 에 대해 오름차순 정렬하면 다음과 같아야 합니다.

 

 

 

 

 

 

 

 

 

---

💻 나의 풀이

WITH RANK_SIZE AS(
    SELECT
        ID,
        ROW_NUMBER() OVER (ORDER BY SIZE_OF_COLONY DESC) AS SIZERANK,
        MAX(ID) AS N
    FROM ECOLI_DATA
)
SELECT
    ID,
    CASE
        WHEN (SIZERANK/N >= 0.75) THEN 'CRITICAL'
        WHEN (SIZERANK/N >= 0.50) THEN 'HIGH'
        WHEN (SIZERANK/N >= 0.25) THEN 'MEDIUM'
        ELSE 'LOW'
    END AS COLONY_NAME
FROM RANK_SIZE
ORDER BY 1;

 

 

 

 

결과

 

 

 

 

 

 

 

 

---

🖍 오답노트

틀린 이유

 

• MAX(ID) AS N 의 잘못된 사용
  • `MAX(ID)`를 사용하는 것은 ID의 최대 값을 의미합니다. 하지만 이 값을 총 개체 수로 사용하는 것은 적절하지 않습니다. 총 개체 수는 모든 개체의 수를 세어야 한다.

나는 처음에 예시에서 확인 가능하듯이, 어짜피 ID가 1부터 시작하니까 ID의 최댓값을 총 개체 수로 설정하면 될 것이라고 생각했다. 그래서 왜 `MAX()` 함수를 사용하면 안되는지 챗 지피티 선생님께 물어봤다.

<`MAX()` 함수가 아니라 `COUNT()`로 전체 개수를 세야 하는 이유>

ID 값이 연속적으로 증가하는지 또는 중간에 누락된 값이 없는지 확실하지 않기 때문에, ID의 최댓값을 전체 개체 수로 사용하는 것은 안전하지 않습니다. 예를 들어, ID가 1, 2, 4, 5인 경우 ID의 최댓값은 5이지만 전체 개체 수는 4입니다. 이런 상황을 피하기 위해, 전체 개체 수를 정확하게 계산하는 방법이 필요합니다.

ID의 최댓값이 실제 개체 수와 일치한다고 가정하더라도, SQL 쿼리에서는 정확하고 일반적으로 사용할 수 있는 접근 방식을 사용하는 것이 좋습니다.

 

 

 

• 개체 크기 분류 기준의 잘못된 계산
  • `SIZERANK/N >= 0.75` 등의 조건은 적절하지 않다.
    이는 `SIZERANK`가 `N`보다 작은 경우 항상 0에 가까운 값을 반환하여, 조건이 제대로 작동하지 않다.
    ► 나눗셈이 아니라 곱셈으로 해야함 (N을 부등호 오른쪽으로 넘겨서)
  • 내가 풀었던 풀이에서는 나눗셈(`/`)으로 상위 **%를 계산했지만, 그렇게 될 경우 SQL에서는 몫을 반환하기 때문에 0을 반환한다. (∵ `SIZERANK`는 `N`보다 작으므로)
  • 총 개체 수(`N`)를 기준으로 `SIZERANK`의 상대적 위치를 확인해야 합니다.

 

 

 

 

 

 

💡 정답 풀이

1. CTE를 통해 개체 크기별 순위를 매긴 후 메인 쿼리에서 분류하는 풀이

WITH RANK_SIZE AS (
    SELECT
        ID,
        SIZE_OF_COLONY,
        ROW_NUMBER() OVER (ORDER BY SIZE_OF_COLONY DESC) AS SIZERANK,
        COUNT(*) OVER () AS TOTAL_COUNT
    FROM ECOLI_DATA
)
SELECT
    ID,
    CASE
        WHEN SIZERANK <= TOTAL_COUNT * 0.25 THEN 'CRITICAL'
        WHEN SIZERANK <= TOTAL_COUNT * 0.50 THEN 'HIGH'
        WHEN SIZERANK <= TOTAL_COUNT * 0.75 THEN 'MEDIUM'
        ELSE 'LOW'
    END AS COLONY_NAME
FROM RANK_SIZE
ORDER BY ID;

 

• COUNT(*) OVER ()
  • `COUNT(*) OVER ()`는 전체 행 수를 계산하고 이를 통해 총 개체수를 반환한다.

 

 

 

 

 

2. FROM절 안에 서브쿼리를 통해 PERCENT_RANK()함수를 사용한 풀이

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

 

• 상대 순위 함수 PERCENT_RANK()
  • `PERCENT_RANK()` 함수는 현재 행 값에 대해 0~1 사이의 상대값을 반환한다.
  • `FROM`절 서브쿼리 내에서 `PERCENT_RANK()`에 의해 반환된 값에 대하여 개체 크기를 분류하면 된다.

 

 

 

 

 

 

 

3. FROM절 안에 서브쿼리를 통해 NTILE()함수를 사용한 풀이

SELECT
    B.ID,
    CASE
        WHEN B.COLONY_LEVEL = 1 THEN 'CRITICAL'
		WHEN B.COLONY_LEVEL = 2 THEN 'HIGH'
	    WHEN B.COLONY_LEVEL = 3 THEN 'MEDIUM'
		ELSE 'LOW'
	END AS COLONY_NAME
FROM(
    SELECT
        ID, 
		NTILE(4) OVER(ORDER BY SIZE_OF_COLONY DESC) AS COLONY_LEVEL
	FROM ECOLI_DATA
) AS B
ORDER BY 1;

 

• NTILE(4) OVER (ORDER BY SIZE_OF_COLONY DESC)
  • `NTILE(파티션 개수)` : 파티션을 지정된 수 만큼의 등급으로 나누어 각 등급 번호를 출력한다.
  • 풀이에서는 4개로 나누었으므로 (문제에서도 4등분으로 나눔) 개체 크기로 내림차순 정렬 후 매개변수로 4를 넣어 4등분 한다.
  • `SELECT` 절의 `CASE ~ WHEN ~ THEN`문에서 `COLONY_LEVEL`의 등급 번호에 따라 개체 크기를 분류한다.
  • `FROM`절에서 내림차순 정렬 후 4등분했으므로 상위 0~25%가 등급번호 1 (`'CRITICAL'`)이 된다.

 

 

 

 

 

 

 

 

---

🔗 References

[풀이2 참고]

https://velog.io/@lookin_min/%ED%94%84%EB%A1%9C%EA%B7%B8%EB%9E%98%EB%A8%B8%EC%8A%A4-%EB%8C%80%EC%9E%A5%EA%B7%A0%EC%9D%98-%ED%81%AC%EA%B8%B0%EC%97%90-%EB%94%B0%EB%9D%BC-%EB%B6%84%EB%A5%98%ED%95%98%EA%B8%B0-2MySQL

프로그래머스 - 대장균의 크기에 따라 분류하기 2 (MySQL)

 

 

[풀이3 참고]

https://seunggi92.tistory.com/138

프로그래머스SQL_대장균의 크기에 따라 분류하기 2

 

 

 

 

 

---

프로그래머스 SQL 코딩테스트 연습 문제 중 Lv3을 다 풀었다.
뭔가 뿌듯하기도 하면서, 아직까지 처음보는 개념들이 있어서 갈길이 멀게 느껴진다.
자주 나오지만 내가 매번 헷갈려 하는 개념이나, 이런 문제에서는 이런 함수를 사용해야한다는 것 등 조금 더 재정비를 하고서
바로 Lv4 문제 풀이로 넘어가야 겠다.