SQL/프로그래머스 - 재귀 SQL

보호소에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다. 0시부터 23시까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해주세요. 이때 결과는 시간대 순으로 정렬해야 합니다.

WITH RECURSIVE RC AS (
    SELECT 0 AS HOUR
    UNION ALL
    SELECT HOUR + 1 FROM RC WHERE HOUR < 23
)
SELECT RC.HOUR, COUNT(HOUR(O.DATETIME)) AS COUNT
FROM RC
    LEFT JOIN ANIMAL_OUTS O
    ON RC.HOUR = HOUR(O.DATETIME)
GROUP BY RC.HOUR
ORDER BY RC.HOUR ASC;

---

문제
3세대의 대장균의 ID(ID) 를 출력하는 SQL 문을 작성해주세요. 이때 결과는 대장균의 ID 에 대해 오름차순 정렬해주세요.

그냥풀기
SELECT ID FROM ECOLI_DATA   -- 3ND GEN
WHERE PARENT_ID IN (
        SELECT ID FROM ECOLI_DATA   -- 2ND GEN
        WHERE PARENT_ID IN (
            SELECT ID FROM ECOLI_DATA WHERE PARENT_ID IS NULL))-- 1ST GENERATION
ORDER BY ID ASC;

재귀 사용
WITH RECURSIVE RC AS(
    SELECT ID, PARENT_ID, 1 AS GEN FROM ECOLI_DATA WHERE PARENT_ID IS NULL
    UNION ALL
    
    (SELECT E.ID, E.PARENT_ID, R.GEN+1
    FROM ECOLI_DATA E
        JOIN RC R
        ON E.PARENT_ID = R.ID)
)
SELECT ID
FROM RC
WHERE GEN = 3

이게 되네;; 재귀 사용할때 UNION ALL 아래에 SELF JOIN 처럼 해서 넣는게 포인트.

 

이거 심화해서 해보자

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

재귀 없이
WITH CTE AS (
    SELECT ID, IFNULL(PARENT_ID,0) AS PARENT_ID,
        CASE
            WHEN PARENT_ID IS NULL THEN 1
            WHEN PARENT_ID IN (SELECT ID FROM ECOLI_DATA WHERE PARENT_ID IS NULL) THEN 2
            WHEN PARENT_ID IN (SELECT ID FROM ECOLI_DATA WHERE PARENT_ID IN 
                               (SELECT ID FROM ECOLI_DATA WHERE PARENT_ID IS NULL)) THEN 3
            WHEN PARENT_ID IN(SELECT ID FROM ECOLI_DATA WHERE PARENT_ID IN(
                            SELECT ID FROM ECOLI_DATA WHERE PARENT_ID IN (
                            SELECT ID FROM ECOLI_DATA WHERE PARENT_ID IS NULL))) THEN 4
            END AS GENERATION
    FROM ECOLI_DATA)
SELECT COUNT(ID) AS COUNT, GENERATION
FROM CTE
WHERE ID NOT IN (SELECT DISTINCT PARENT_ID FROM CTE)
GROUP BY GENERATION
ORDER BY GENERATION ASC;

성공은 하는데 채점은 왕창 틀린 ㅎㅎ

 

질문에서 보면:

- 세대별로, 자식이 없는 개체(ID)의 수(COUNT)를 구하라는 것.

- 그렇다면, 세대를 뽑고 + 자식이 있는지 없는지 (혹은 자식이 몇명 있는지)를 뽑고 > 세대별로 그룹핑 해서 개수 구하기.

WITH RECURSIVE RC AS(
    SELECT ID, PARENT_ID, 1 AS GEN FROM ECOLI_DATA WHERE PARENT_ID IS NULL
    UNION ALL
    SELECT E.ID, E.PARENT_ID, R.GEN+1
    FROM ECOLI_DATA E
        JOIN RC R
        ON E.PARENT_ID = R.ID
),
PARENT_ID_CNT AS (
    SELECT PARENT_ID, COUNT(*) AS CNT
    FROM RC
    WHERE PARENT_ID IS NOT NULL
    GROUP BY PARENT_ID
)
SELECT COUNT(RC.ID) AS COUNT, RC.GEN AS GENERATION
FROM RC
    LEFT JOIN PARENT_ID_CNT P
    ON RC.ID = P.PARENT_ID
WHERE P.CNT IS NULL
GROUP BY RC.GEN
ORDER BY RC.GEN ASC

 

VS

WITH RECURSIVE RC AS(
    SELECT ID, PARENT_ID, 1 AS GEN FROM ECOLI_DATA WHERE PARENT_ID IS NULL
    UNION ALL
    SELECT E.ID, E.PARENT_ID, R.GEN+1
    FROM ECOLI_DATA E
        JOIN RC R
        ON E.PARENT_ID = R.ID
),
PARENT_ID_CNT AS (
    SELECT PARENT_ID, COUNT(*) AS CNT
    FROM RC
    WHERE PARENT_ID IS NOT NULL
    GROUP BY PARENT_ID
),
FINAL_TABLE AS (
    SELECT RC.ID, RC.GEN, P.CNT
    FROM RC
        LEFT JOIN PARENT_ID_CNT P
        ON RC.ID = P.PARENT_ID
    WHERE P.CNT IS NULL
)
SELECT COUNT(ID) AS COUNT, GEN AS GENERATION
FROM FINAL_TABLE
GROUP BY GEN
ORDER BY GENERATION ASC

 

바로 위 코드는 세번 CTE 거쳐서 결과물 도달, 위위 코드는 2번 CTE 거쳐서 도달.

바로 위 코드가 더 가독성이 좋을 것 같아서 이렇게 비교해봤는데, 딱히 그렇지는 않네. 대신 바로 위 코드가 좀더 활용도가 좋을수는 있겠다. 코드 뚝 떼서 쓰기에는 더 좋을지도..?

 

성능차이를 GPT한테 물어보니까, 한단계 줄여서 쓰는게 (위위코드) 조금더 효용성이 좋을 수는 있다, 데이터가 엄청 크다면 차이가 있을수 있다,라고 답변.

 

가독성 포기못해 ㅠㅠ 하지만 회사에서는 다들 익숙한 테이블일테니, 위위코드도 괜찮을지도?