SQL 코테 연습 프로그래머스 Lv.5 문제풀이 9

Lv4 문제는 이제 모두 풀어봐서 Lv5 문제 시작!!

 

 

1. 멸종위기의 대장균 찾기(SELECT)  정답률 : 18%

프로그래머스

문제

각 세대별 자식이 없는 개체의 수(COUNT)와 세대(GENERATION)를 출력하는 SQL문을 작성해주세요. 이때 결과는 세대에 대해 오름차순 정렬해주세요. 단, 모든 세대에는 자식이 없는 개체가 적어도 1개체는 존재합니다.

 

우선 계속 SELF-JOIN을 통해서 예시와 맞게 세대별 자식이 없는 개체가 맞게 되는지 확인했다.

-- 0.모두 JOIN해서 확인
SELECT FST.ID AS FST_ID, SND.ID AS SND_ID, TRD.ID AS TRD_ID
	, FTH.ID AS FTH_ID, FFTH.ID AS FFTH_ID
FROM ECOLI_DATA FST
LEFT JOIN ECOLI_DATA SND
	ON FST.ID=SND.PARENT_ID
LEFT JOIN ECOLI_DATA TRD
	ON SND.ID=TRD.PARENT_ID
LEFT JOIN ECOLI_DATA FTH
	ON TRD.ID=FTH.PARENT_ID
LEFT JOIN ECOLI_DATA FFTH
	ON FTH.ID=FFTH.PARENT_ID
WHERE FST.PARENT_ID IS NULL

 

예시와 맞게 진행되는것을 보고 이를 어떻게 결과에 맞는 형식으로 정리할 수 있을까 고민했다. 첫번째로 생각한 방법은 각 세대마다 자식이 없는 형질의 수를 구하는 임시 테이블을 만들어서 마지막에 UNION하는 것이었다. 코드실행에서는 정답으로 나왔으나 채점은 모두 정답이 아니었다. 해서 임시는 4세대까지만 나왔으나 혹시 채점에서는 그 이후 세대까지 표현되는지 확인해보기 위해 여섯번째 형질까지 확인했고, 채점의 6문제중 한문제는 맞는것을 확인했다. 따라서 내가 작성한 코드는 4세대까지만 확인이 가능하지만, 정답을 맞히기 위해서는 마지막 세대까지 모두 표현되어야하는것을 확인했다. 또한 5세대를 붙였더니 4세대까지만 존재하는 예시에서는 오답 처리되었다. 결국 마지막 세대까지 모두 표현되면서, 문제에따라 존재하는 마지막 세대까지만 나타내야하는 문제이다. 한 7-8세대까지 하려다 몇세대나 있을지 몰라서 다른 방법을 생각해보기로 했다.(6문제중에서 5세대까지 있는게 1문제 밖에 없었다; 다른 문제들은 더 있다는 의미겠죠..)

-- 1. 각 GENERATION마다 테이블을 구해서 UNION
WITH FST AS (
SELECT COUNT(*) AS COUNT, 1 AS GENERATION
FROM (SELECT FST.ID AS FST_ID, SND.ID AS SND_ID
        FROM ECOLI_DATA FST
        LEFT JOIN ECOLI_DATA SND
            ON FST.ID=SND.PARENT_ID
        WHERE FST.PARENT_ID IS NULL AND SND.ID IS NULL) A
), SND AS(
SELECT COUNT(*) AS COUNT, 2 AS GENERATION
FROM (SELECT SND.ID AS SND_ID, TRD.ID AS TRD_ID
        FROM ECOLI_DATA FST
        LEFT JOIN ECOLI_DATA SND
            ON FST.ID=SND.PARENT_ID
        LEFT JOIN ECOLI_DATA TRD
            ON SND.ID=TRD.PARENT_ID
        WHERE FST.PARENT_ID IS NULL AND SND.ID IS NOT NULL AND TRD.ID IS NULL) A
), TRD AS(
SELECT COUNT(*) AS COUNT, 3 AS GENERATION
FROM (SELECT TRD.ID AS TRD_ID, FTH.ID AS FTH_ID
        FROM ECOLI_DATA FST
        LEFT JOIN ECOLI_DATA SND
            ON FST.ID=SND.PARENT_ID
        LEFT JOIN ECOLI_DATA TRD
            ON SND.ID=TRD.PARENT_ID
        LEFT JOIN ECOLI_DATA FTH
            ON TRD.ID=FTH.PARENT_ID
        WHERE FST.PARENT_ID IS NULL AND SND.ID IS NOT NULL AND TRD.ID IS NOT NULL AND FTH.ID IS NULL) A
), FTH AS(
SELECT COUNT(*) AS COUNT, 4 AS GENERATION
FROM (SELECT FTH.ID AS FTH_ID, FFTH.ID AS FFTH_ID
        FROM ECOLI_DATA FST
        LEFT JOIN ECOLI_DATA SND
            ON FST.ID=SND.PARENT_ID
        LEFT JOIN ECOLI_DATA TRD
            ON SND.ID=TRD.PARENT_ID
        LEFT JOIN ECOLI_DATA FTH
            ON TRD.ID=FTH.PARENT_ID
        LEFT JOIN ECOLI_DATA FFTH
            ON FTH.ID=FFTH.PARENT_ID
        WHERE FST.PARENT_ID IS NULL AND SND.ID IS NOT NULL AND TRD.ID IS NOT NULL AND FTH.ID IS NOT NULL
                AND FFTH.ID IS NULL)A
)
(SELECT * 
FROM FST) 
UNION 
(SELECT * 
FROM SND) 
UNION
(SELECT * 
FROM TRD) 
UNION
(SELECT * 
FROM FTH) -- 예시는 정답처리되나 다른 문제들은 이후 세대 존재하여 오답처리됨.

 

 

결국 이렇게 SELF-JOIN을 계속 진행해야한다는 소리인데, 이를 매크로처럼 자동적으로 실행할 수 있는 방법을 찾아보니 '재귀함수'를 사용하면 해결되는 문제였다. 재귀함수는 ' WITH RECURSIVE' 구문으로, 이 구문은 자기 자신을 참조해서 반복적으로 데이터를 가져오는 SQL문이다.

 

재귀함수를 사용하여 부모가 없는 첫 세대에 계속 다음 세대를 붙이는 테이블을 만들고, 이 테이블에 자식을 붙여서 자식이 없는 형질만 추출하여 각 세대별 건수를 구하면된다.

-- 2. 재귀함수 사용
WITH RECURSIVE GEN AS(
(SELECT ID, PARENT_ID, 1 AS GENERATION
FROM ECOLI_DATA
WHERE PARENT_ID IS NULL)
UNION ALL
(SELECT T1.ID, T1.PARENT_ID, T2.GENERATION + 1 -- 세대를 하나씩 늘려가며 테이블 붙이기
FROM ECOLI_DATA T1
JOIN GEN T2 ON T1.PARENT_ID = T2.ID)
)SELECT COUNT(DISTINCT T1.ID) AS COUNT, GENERATION
FROM GEN T1
LEFT JOIN ECOLI_DATA T2 
	ON T1.ID=T2.PARENT_ID -- GEN 테이블에 자식 붙이기
WHERE T2.ID IS NULL -- 자식 없는 형질만 추출
GROUP BY GENERATION