Sql

title: 2026-01-10 author: 강병호 (이름) date: 2026-01-10 (날짜) category: TIL/강병호/2026/01 (파일 경로 : TIL/{이름}/{연}/{월}) layout: post (자유) —

  • 멸종위기의 대장균 찾기
    • 각 세대별 자식이 없는 개체의 수
    • 세대
    • 새대 기준 오름차순 정렬

    개념

    문제 링크 : https://school.programmers.co.kr/learn/courses/30/lessons/301651

    풀이 과정

    1. 세대 결정하기
      1. PARENT_ID = NULL
      2. N세대는 N-1세대를 부모로 둔 자식들
      3. 데이터가 없을 때까지 반복
    2. 자식이 없는 개체 고르기
      1. 자신의 ID가 누군가의 PARENT_ID가 되지 않는 것
      2. NOT IN 서브 쿼리 or LEFT JOIN 후 IS NULL 활용 - 템플릿 코드
       -- 1. 재귀 쿼리(Recursive CTE)를 사용하여 모든 개체의 세대(Generation) 계산
 WITH RECURSIVE ECOLI_GEN AS (
     -- [초기 단계] 1세대: 부모가 없는 최상위 개체 찾기
     SELECT 
         ID, 
         PARENT_ID, 
         1 AS GENERATION
     FROM 
         ECOLI_DATA
     WHERE 
         /* 빈칸 1: 최상위 조상을 찾는 조건을 작성하세요 (힌트: 부모가 없음) */
         ________________________ 
        
     UNION ALL
        
     -- [재귀 단계] n세대: 이전 세대를 부모로 둔 자식들 찾기
     SELECT 
         E.ID, 
         E.PARENT_ID, 
         G.GENERATION + 1
     FROM 
         ECOLI_DATA AS E
     JOIN 
         ECOLI_GEN AS G ON /* 빈칸 2: 부모의 ID와 자식의 PARENT_ID를 연결하세요 */
                           ________________________
 )
        
 -- 2. 자식이 없는 개체(Leaf Node)들만 필터링하여 세대별 집계
 SELECT 
     COUNT(*) AS COUNT, 
     GENERATION
 FROM 
     ECOLI_GEN
 WHERE 
     -- [필터링] 자신의 ID가 누군가의 PARENT_ID 리스트에 존재하지 않아야 함
     ID NOT IN (
         SELECT 
             DISTINCT /* 빈칸 3: 부모 ID 컬럼명을 입력하세요 */
             ________________________
         FROM 
             ECOLI_DATA
         WHERE 
             /* 빈칸 4: NOT IN 사용 시 NULL을 제외하는 조건이 필수입니다 */
             ________________________
     )
 GROUP BY 
     GENERATION
 ORDER BY 
     GENERATION ASC;

    • 초기 코드

    • 1차 코드

        SELECT 
      COUNT(*) AS COUNT, 
      GENERATION
  FROM 
      ECOLI_DATA
  WHERE 
      -- [필터링] 자신의 ID가 누군가의 PARENT_ID 리스트에 존재하지 않아야 함
      ID NOT IN (
          SELECT 
              DISTINCT /* 빈칸 3: 부모 ID 컬럼명을 입력하세요 */
              PARENT_ID
          FROM 
              ECOLI_DATA
          WHERE PARENT_ID IS NOT NULL
          )
  GROUP BY 
      GENERATION
  ORDER BY 
      GENERATION ASC;

    • 정답 코드

        WITH RECURSIVE ECOLI_GEN AS (
      SELECT 
          ID, 
          PARENT_ID, 
          1 AS GENERATION
      FROM 
          ECOLI_DATA
      WHERE 
          PARENT_ID IS NULL
        
      UNION ALL
        
      SELECT 
          E.ID, 
          E.PARENT_ID, 
          G.GENERATION + 1
      FROM 
          ECOLI_DATA AS E
      JOIN 
          ECOLI_GEN AS G ON E.PARENT_ID = G.ID
  )
        
  SELECT 
      COUNT(*) AS COUNT, 
      GENERATION
  FROM 
      ECOLI_GEN AS G
  WHERE 
      NOT EXISTS (
          SELECT 1 
          FROM ECOLI_DATA AS D 
          WHERE D.PARENT_ID = G.ID
      )
  GROUP BY 
      GENERATION
  ORDER BY 
      GENERATION ASC;

      ### 1. 재귀적 CTE (Recursive CTE): “가상 세대 만들기”

      테이블에 GENERATION 컬럼이 없기 때문에, SQL이 스스로 가계도를 타고 내려가며 숫자를 붙이도록 만드는 과정입니다.

      • Anchor(시작점): PARENT_ID IS NULL인 개체를 찾아 1이라는 숫자를 부여합니다. 이것이 1세대의 정의가 됩니다.
      • Recursive(반복): 이미 세대가 결정된 부모(G)의 ID를 PARENT_ID로 갖는 자식들(E)을 조인합니다. 이때 부모의 세대 번호에 +1을 하여 자식의 세대를 결정합니다.
      • 작동 원리: 더 이상 자식이 없을 때까지 이 루프가 반복되며 전 개체에 세대 번호가 부여됩니다.

      ### 2. NOT EXISTS 상관 서브쿼리: “말단(Leaf) 개체 판별”

      문제의 핵심 조건인 “자식이 없는 개체”를 찾는 필터링 로직입니다.

      • 상관 관계: 메인 쿼리의 특정 개체 G.ID를 서브쿼리로 전달합니다.
      • 존재 여부 확인: “전체 데이터 중에서 누군가의 부모(D.PARENT_ID)가 내 ID(G.ID)와 일치하는 경우가 존재(EXISTS)하지 않는(NOT)가?”를 체크합니다.
      • 결과: 이 조건이 참이라면, 해당 개체는 자식을 한 명도 낳지 않은 ‘마지막 세대(Leaf Node)’가 됩니다.

      ### 3. 그룹화 및 최종 집계

      위의 과정을 통해 “세대 번호가 매겨진 말단 개체들”만 남게 됩니다.

      • GROUP BY: 이제 이 개체들을 GENERATION별로 묶습니다.
      • COUNT(*): 각 묶음(세대)에 몇 명의 말단 개체가 있는지 세어줍니다.
      • ORDER BY: 문제 요구사항대로 세대 번호가 낮은 순서부터 출력합니다.

results matching ""

    No results matching ""