[SQL 문제 풀이] 프로그래머스 SQL 고득점 kit - SELECT Level 3 풀이

프로그래머스에서 제공하는 SQL 고득점 kit 중 SELECT Level 3 문제들에 대한 풀이입니다.

 

대장균들의 자식의 수 구하기

 

문제

 

 

풀이

SELECT A.ID, COUNT(CASE WHEN B.ID IS NOT NULL THEN 1 END) CHILD_COUNT
FROM ECOLI_DATA A
LEFT JOIN ECOLI_DATA B ON A.ID=B.PARENT_ID
GROUP BY 1
ORDER BY 1;

 

- COUNT 함수 안에 CASE WHEN 사용

 

 

ID 값이 NULL이면 자식의 수를 0으로 출력해야 한다. 따라서  LEFT JOIN을 한 B 테이블의 ID 값이 NULL 이면 COUNT를 하지 말아야 한다.

• 처음에는 CASE WHEN의 조건을 IS NULL THEN 0 ELSE 1 조건으로 했다.
  - 이 조건의 문제 : ID가 NULL이어도 0이 반환되기 때문에 COUNT 함수가 값으로 취급하고 결과에 포함한다.
• 따라서 CASE WHEN 조건을 IS NOT NULL THEN 1 END 로 변경했다. 이 조건을 통해 null이 아닌 경우만 count를 할 수 있다.

 

대장균의 크기에 따라 분류하기 1

 

문제

 

풀이

SELECT ID, 
CASE
    WHEN SIZE_OF_COLONY <= 100 THEN 'LOW'
    WHEN SIZE_OF_COLONY <= 1000 THEN 'MEDIUM'
    ELSE 'HIGH'
END AS SIZE
FROM ECOLI_DATA
ORDER BY 1;

 

- CASE WHEN 사용

 

시행착오

- 처음에는 두번째 조건을 100 < SIZE_OF_COLONY ≤ 1000 으로 해서 문제가 발생했다.

- 문제가 발생한 이유 : 연속된 비교를 저렇게( 100 < SIZE_OF_COLONY ≤ 1000 ) 사용하면 안된다. 따라서 두번째 조건을 해석하는 과정에서 100 < SIZE_OF_COLONY 조건으로 인식하고 뒤에 있는 조건들이 정의되지 않는 문제가 발생한다.
- 만약 연속 비교를 하고 싶다면 BETWEEN 연산자를 사용해야 한다.

 

풀이에서 앞에 이미 100이하 조건은 처리가 되었기 때문에 이후 조건은 100을 초과하는 조건이라는 것이 자동으로 적용된다. 따라서 뒤의 조건을 BETWEEN 연산자를 사용할 필요가 없다.

 

 

대장균의 크기에 따라 분류하기 2

 

문제

풀이

WITH TMP AS(
    SELECT ROW_NUMBER() OVER (ORDER BY SIZE_OF_COLONY DESC) AS ROW_NUM, ID,
    (SELECT COUNT(*) FROM ECOLI_DATA) TOTAL_RECORDS
    FROM ECOLI_DATA
)
SELECT A.ID,
CASE
    WHEN ROW_NUM <= (TOTAL_RECORDS)*0.25 THEN 'CRITICAL'
    WHEN ROW_NUM <= (TOTAL_RECORDS)*0.5 THEN 'HIGH'
    WHEN ROW_NUM <= (TOTAL_RECORDS)*0.75 THEN 'MEDIUM'
    ELSE 'LOW'
END COLONY_NAME
FROM ECOLI_DATA A
JOIN TMP B ON A.ID=B.ID
ORDER BY 1;

 

🔹임시 테이블(TMP)

- ROW_NUMBER()를 SIZE_OF_COLONY를 기준으로 내림차순으로 정렬해 사이즈가 큰 순서대로 1부터 순차적으로 번호를 가지게 된다.

- 이후에 ROW_NUM을 기준으로 상위 몇 퍼센트에 속하는지 계산하기 위해 총 레코드 수 컬럼을 미리 추가했다.

 

🔹메인 SQL문

- 임시 테이블 TMP와 ID가 같은 값을 기준으로 JOIN을 했다.

- CASE WHEN의 조건으로 ROW_NUM의 범위를 25%, 50%, 75% 범위로 나눠서 계산했다.

 

 

시행 착오

SELECT ROW_NUMBER() OVER (ORDER BY SIZE_OF_COLONY DESC) AS ROW_NUM, ID, 
COUNT(*) TOTAL_RECORDS
FROM ECOLI_DATA

 

- 이 쿼리는 처음에 임시 테이블을 만들기 위해 실행한 쿼리문이다. 이 쿼리를 실행하면 아래와 같은 오류 메시지가 발생한다.

 

그래서 ID 컬럼으로 GROUP BY하게 추가를 했더니 이번에는 SIZE_OF_COLONY에 대해 GROUP BY를 하지 않아서 오류가 발생했다.

 

- 오류가 발생한 이유 : mysql의 only_full_group_by 모드가 활성화되어 있어서

=> 이 모드는 sql에서 집계 함수를 사용할 때 group by 절에 명시된 열 이외에도 반드시 그룹화해야 한다. group by 절 이외의 열에 대해 집계 함수를 사용하거나 group by 하는 열에 대해 함수적 종속성을 가져야 한다. (= group by하는 열의 값이 달라짐에 따라 그 열의 값도 결정되는 것)

 

이 문제를 해결하기 위해 서브쿼리를 사용해 COUNT 함수를 실행해 전체 행 수를 구했다.