[프로그래머스] SQL 반복문, WITH RECURSIVE

GROUP BY > 입양시각 구하기(2)

 

ANIMAL_OUTS : 동물 보호소에서 입양 보낸 동물의 정보를 담은 테이블

SEX_UPON_OUTCOME : 동물의 아이디, 생물 종, 입양일, 이름, 성별 및 중성화 여부 정보 테이블

 

 

0시부터 23시까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성하는 문제였다.

그런데 그냥 group by를 해서 결과를 조회하면 0시부터 23시까지 나오지 않고, 입양이 발생한 시간만 조회되는 문제가 있어 0시부터 23시까지의 시간 테이블을 생성해야 하는 것이 관건이었다.

 

이 때 사용되는 것이 SQL에서 반복문처럼 사용할 수 있는 WITH RECURSIVE 함수

/** [Example] 0시부터 24시까지 시간 컬럼 만들기 **/
WITH recursive 테이블명 AS (
SELECT 0 AS cnt -- 0 값을 행으로 가지는 테이블 생성
UNION ALL -- 합집합
SELECT cnt + 1 FROM 테이블명 -- cnt에 +1씩 더한다.
WHERE cnt < 23 -- 언제까지? cnt가 22시가 될 때까지 (cnt+1하면 23시가 최댓값)
)
SELECT cnt FROM 테이블명

 

 

[정답]

-- 24시간 시간 반복문 생성
WITH RECURSIVE time as (
SELECT 0 as hour -- 0시부터 시작
UNION ALL
SELECT hour+1 FROM time -- 1시간씩 더한다
WHERE hour < 23 -- 22시간까지만. (반복문 제어)
)
SELECT t.hour, if(hour_cnt IS NULL, 0, hour_cnt) AS count
FROM time t -- 시간 반복문 TABLE
LEFT JOIN (
SELECT HOUR(datetime) as hour, -- datetime에서 hour만 추출
	     count(hour) as hour_cnt -- 시간대별 입양건수
GROUP BY HOUR(datetime)
) as final ON t.hour = final.hour -- TIME TABLE 기준 시간대별 입양건수 TABLE 'LEFT JOIN'
ORDER BY hour

만들어준 반복문에 기존에 group by 해서 나오는 값을 left join해서 붙여주고 시간대별 입양건수가 NULL 즉, 0인 시간대는 IF 함수를 사용해서 0이 입력되게 해주면 된다.

 

 

---

SELECT > 멸종위기의 대장균 찾기

parent_id가 NULL이면 1세대,

parent_id가 1세대 ID이면 2세대,

parent_id가  2세대 ID이면 3세대 ...

 

id별 세대를 구분해주고 자식을 가지지 않는 id만 세대별로 count 집계해서 조회하는 문제였다.

 

SELF JOIN을 해서 parent_id = id를 하는 것까지는 알겠는데, 그냥 SELF JOIN을 하면 원하는 대로 결과가 안나와서 결국 못풀었다. RECURSIVE도 시도 해봤지만 제대로 이해하지 못했는지 결국 코드를 완성하지 못해서 실패ㅠㅠ

 

[정답]

WITH RECURSIVE generation AS (
    SELECT ID, 1 AS g_level
    FROM ECOLI_DATA
    WHERE PARENT_ID IS NULL -- parent_id가 null인 1세대 id
    UNION ALL
    SELECT e.ID, g.g_level+1 AS g_level -- 1+1 = 2세대 (반복문)
    FROM ECOLI_DATA e
    INNER JOIN GENERATION g ON e.PARENT_ID = g.ID -- 위 generation table에서 1세대 id가 parent_id인 id
)
SELECT COUNT(*) AS count,
       g_level AS generation
FROM generation
WHERE id NOT IN ( -- 자식이 없는 id = parent_id에 없는 id
    SELECT parent_id
    FROM ecoli_data
    WHERE parent_id IS NOT NULL) -- not in 조건을 걸 때 반드시 null값 처리 필요
GROUP BY g_level
ORDER BY g_level