[DBMS] MYSQL WITH RECURSIVE

MYSQL에서 반복문 작성하기

---

문제 : 프로그래머스_ 멸종위기의 대장균 찾기

문제 설명
대장균들은 일정 주기로 분화하며, 분화를 시작한 개체를 부모 개체, 분화가 되어 나온 개체를 자식 개체라고 합니다.
다음은 실험실에서 배양한 대장균들의 정보를 담은 ECOLI_DATA 테이블입니다. ECOLI_DATA 테이블의 구조는 다음과 같으며, ID, PARENT_ID, SIZE_OF_COLONY, DIFFERENTIATION_DATE, GENOTYPE 은 각각 대장균 개체의 ID, 부모 개체의 ID, 개체의 크기, 분화되어 나온 날짜, 개체의 형질을 나타냅니다.

Column name Type Nullable
ID INTEGER FALSE
PARENT_ID INTEGER TRUE
SIZE_OF_COLONY INTEGER FALSE
DIFFERENTIATION_DATE DATE FALSE
GENOTYPE INTEGER FALSE
최초의 대장균 개체의 PARENT_ID 는 NULL 값입니다.

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

https://school.programmers.co.kr/learn/courses/30/lessons/301651

접근방법

---

재귀함수를 활용하여 조건을 비교해가며 결과를 내야 하는 문제였음

1. 각 ID별로 세대 구분하기
2. 각 세대 별로 자식이 없는 개체 수 세기

해결방법

---

1. 각 ID별로 세대 구하기

WITH RECURSIVE CTE AS(
	
    # 1세대 구하기
    SELECT ID, PARENT_ID, 1 AS GENERATION
    FROM ECOLI_DATA
    WHERE PARAENT_ID IS NULL
    
    UNION ALL
    
    # 이후세대 구하기
    SELECT AFT.ID, AFT.PARENT_ID, BEF.GENERATION+1 AS GENERATION
    FROM ECOLI_DATA AFT
    INNER JOIN CTE BEF
    ON AFT.PARENT_ID = BEF.ID

)

2. 각 세대 별로 자식이 없는 개체 수 세기

SELECT COUNT(ID) AS COUNT, GENERATION
FROM CTE
WHERE ID NOT IN (
			SELECT DISTINCT PARENT_ID 
            FROM CTE
            WHERE PARENT_ID IS NOT NULL
		)
GROUP BY GENERATION
ORDER BY GENERATION;

새로 배운 내용

---

1) 재귀 함수 작성법

WITH RECURSIVE 이름 AS (

SELECT 구문

UNION ALL

SELECT 구문

)

형식으로 사용해야 한다는 것을 배웠다.

2) 조인의 종류 복습

🟩 INNER JOIN
두 테이블 모두 공통 컬럼을 가지고 있으며, 서로 모두 포함하는 레코드를 합쳐서 표현한다.
즉, 한마디로 교집합

SELECT * 
FROM A
JOIN B
ON A.ID=B.ID

🟩 LEFT OUTER JOIN, RIGHT OUTER JOIN
하나의 테이블을 기준으로 합치는 조인

LEFT : 기존의 테이블
RIGHT : 새로 적용시키려는 테이블

어떤 테이블을 기준으로 할 지에 따라 LEFT JOIN, RIGHT JOIN을 사용한다.

SELECT * 
FROM A
LEFT OUTER JOIN B
ON A.ID=B.ID

-> 매칭되지 않는 A의 레코드들은 B에 해당하는 속성값들에 NULL을 부여한다.

🟩 CROSS JOIN
집합 곱, 모든 속성들을 다 일일히 매치시킨다.
즉, A에 컬럼 4개, B에 컬럼 6개라면 => 조인 결과 : 4x6 =24개의 레코드가 반환된다.