[프로그래머스] 멸종위기의 대장균 찾기(재귀함수), 잡은 물고기 중 가장 큰 물고기의 길이 구하기(concat), 노선별 평균 역 사이 거리 조회하기 (order by)

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

WITH RECURSIVE 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,
        1+ G.GENERATION AS GENERATION
    FROM GEN AS G 
    JOIN ECOLI_DATA AS E ON G.ID = E.PARENT_ID
)

SELECT 
    COUNT(G.ID) AS COUNT,
    G.GENERATION
FROM GEN AS G 
LEFT JOIN ECOLI_DATA AS E ON E.PARENT_ID = G.ID 
WHERE E.ID IS NULL 
GROUP BY 2
ORDER BY 2

 

 

재귀문법이란?

WITH RECURSIVE cte_name AS (
    -- 1) 기저부(Base Case): 가장 작은 단위(반환할 초기 결과) 정의
    SELECT … 
    FROM … 
    WHERE …   -- 보통 “부모가 없는(루트) 행”을 추출

    UNION ALL

    -- 2) 재귀부(Recursive Step): 기저부 또는 이전 단계에서 추출된 결과를 이용해
    --    더 다음 단계(브anches)를 구하는 부분
    SELECT …
    FROM … 
    JOIN cte_name AS prev   -- “prev”는 이미 계산된 바로 직전 단계 결과
      ON …                   -- prev를 부모로 삼는 자식 행을 매칭
)
SELECT *
FROM cte_name;

MySQL에서는 재귀 CTE의 “재귀부”에서 다음 두 가지를 반드시 지켜야 합니다
재귀 CTE의 자기 참조 테이블(여기서는 GEN)을 INNER JOIN으로 연결해야 하고,
조인 순서상 재귀 테이블(GEN)이 항상 왼쪽에 위치해야 합니다.

 

문제 - 잡은 물고기 중 가장 큰 물고기의 길이 구하기
FISH_INFO 테이블에서 잡은 물고기 중 가장 큰 물고기의 길이를 'cm' 를 붙여 출력하는 SQL 문을 작성해주세요.
이 때 컬럼명은 'MAX_LENGTH' 로 지정해주세요.

-- 가장 큰 물고기의 길이에 cm 붙여서 출력 

SELECT 
    CONCAT(MAX(LENGTH),'cm') AS MAX_LENGTH
FROM FISH_INFO

 

문제 - 노선별 평균 역 사이 거리 조회하기

SUBWAY_DISTANCE 테이블에서 노선별로 노선, 총 누계 거리, 평균 역 사이 거리를 노선별로 조회하는 SQL문을 작성해주세요.

총 누계거리는 테이블 내 존재하는 역들의 역 사이 거리의 총 합을 뜻합니다. 총 누계 거리와 평균 역 사이 거리의 컬럼명은 각각 TOTAL_DISTANCE, AVERAGE_DISTANCE로 해주시고, 총 누계거리는 소수 둘째자리에서, 평균 역 사이 거리는 소수 셋째 자리에서 반올림 한 뒤 단위(km)를 함께 출력해주세요.
결과는 총 누계 거리를 기준으로 내림차순 정렬해주세요.

SELECT 
    ROUTE,
    CONCAT(ROUND(SUM(D_BETWEEN_DIST),1),'km') AS TOTAL_DISTANCE,
    CONCAT(ROUND(AVG(D_BETWEEN_DIST),2), 'km') AS AVERAGE_DISTANCE
FROM SUBWAY_DISTANCE 
GROUP BY 1
ORDER BY sum(D_BETWEEN_DIST) DESC

문제는 어렵지 않았는데 ORDER BY 에서 실수함. 평소에 "ORDER BY 1, 2 " 이런 형식으로 컬럼 순서를 지정했음. 

근데 이번 문제에서는 concat으로 인해 TOTAL_DISTANCE 컬럼이 숫자 -> 문자 변환됨. 문자열로 순서 정렬할 경우 숫자열과 다르게 지정될 수 있음!!