[프로그래머스] 10주차 MySQL 스터디 2

🌟🌟🌟🌟 1. SELECT - [특정 세대의 대장균 찾기] _ LEVEL 4

대장균들은 일정 주기로 분화하며, 분화를 시작한 개체를 부모 개체, 분화가 되어 나온 개체를 자식 개체라고 합니다.

다음은 실험실에서 배양한 대장균들의 정보를 담은 ECOLI_DATA 테이블입니다.

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

 

[ 풀이 ]

 

풀이 1) 규칙을 발견하여 귀납적 처리(INNER JOIN 사용)

SELECT A.id
FROM ecoli_data A
JOIN ecoli_data B ON A.parent_id = B.id
JOIN ecoli_data C ON B.parent_id = C.id
WHERE C.parent_id IS NULL;

• N세대 대장균을 찾아내는 방법(위의 예시로부터 귀납적 사고방식으로 처리)
  • 1세대 대장균의 parent_id 가 NULL임을 이용!
  • 2세대 대장균은 A테이블의 A.parent_id와 JOIN한 B테이블의 B.parent_id가 NULL이라는 뜻
  • 같은 원리로 3세대 대장균을 골라낼 수 있음
  • N세대 대장균 공식

-- N세대 대장균 공식
SELECT T1.id
FROM ecoli_data T1
JOIN ecoli_data T2 ON T1.parent_id = T2.id
JOIN ecoli_data T3 ON T2.parent_id = T3.id
:
JOIN ecoli_data Tn ON Tn-1.parent_id = Tn.id
WHERE Tn.parent_id IS NULL;

---

풀이 2) RECURSIVE CTE 사용

WITH RECURSIVE Generation AS (
    -- 초기 조건: 첫 번째 세대 (PARENT_ID가 NULL인 개체)
    SELECT ID, PARENT_ID, 1 AS Gen
    FROM ECOLI_DATA
    WHERE PARENT_ID IS NULL

    UNION ALL

    -- 재귀적 조건: 다음 세대 찾기
    SELECT e.ID, e.PARENT_ID, g.Gen + 1 AS Gen
    FROM ECOLI_DATA e
    INNER JOIN Generation g ON e.PARENT_ID = g.ID
)
-- 최종 결과 선택: 3세대 개체의 ID
SELECT ID
FROM Generation
WHERE Gen = 3
ORDER BY ID;

재귀 CTE로 생성한 테이블 전체 출력

• 재귀 CTE를 사용하는 것은 계층적 데이터를 다룰 때 매우 강력한 도구
• 복잡한 부모-자식 관계를 간결하고 이해하기 쉽게 표현할 수 있음
• 각 재귀 단계에서 새로운 세대는 바로 이전 세대의 결과를 참조하여 생성되지만, 이전 모든 단계의 결과는 Generation CTE 내에 계속 축적됨

---

1. CTE 시작 (WITH RECURSIVE Generation AS (...)): 재귀적으로 사용될 CTE를 정의. Generation은 각 세대의 대장균을 추적.
2. 초기 조건:
   • 첫 번째 SELECT 문은 초기 조건을 설정. 여기서는 PARENT_ID가 NULL인 대장균, 즉 첫 세대를 선택.
   • Gen이라는 열을 추가하여, 현재 세대를 나타냄 (첫 세대는 1).
3. 재귀적 조건:
   • UNION ALL을 사용하여 초기 조건의 결과와 재귀적으로 생성된 결과를 결합.
   • 두 번째 SELECT 문에서는 ECOLI_DATA 테이블의 각 행(e)을 Generation CTE의 결과(g)와 조인. 여기서 e.PARENT_ID = g.ID는 자식 대장균을 부모와 연결.
   • Gen + 1은 각 반복에서 세대를 하나씩 증가시킴. 즉, 부모의 세대에서 자식 세대로 넘어갈 때마다 세대 수가 증가.
4. 최종 결과 선택:
   • WHERE Gen = 3을 통해 3세대의 대장균만 선택.
   • 결과는 ID에 대해 오름차순으로 정렬.

 

참고) 재귀 CTE에 관한 개념 설명 : RECURSIVE CTE

RECURSIVE CTE(재귀 CTE)

 

---

🌟🌟🌟🌟 2. SELECT - [멸종위기의 대장균 찾기] _ LEVEL 5

대장균들은 일정 주기로 분화하며, 분화를 시작한 개체를 부모 개체, 분화가 되어 나온 개체를 자식 개체라고 합니다.

다음은 실험실에서 배양한 대장균들의 정보를 담은 ECOLI_DATA 테이블입니다.

각 세대별 자식이 없는 개체의 수(COUNT)와 세대(GENERATION)를 출력하는 SQL문을 작성해주세요.

이때 결과는 세대에 대해 오름차순 정렬해주세요. 단, 모든 세대에는 자식이 없는 개체가 적어도 1개체는 존재합니다.

 

[ 정답 쿼리 ]

WITH RECURSIVE gen(id, parent_id, generation) AS (
    SELECT id, parent_id, 1
    FROM ecoli_data
    WHERE parent_id IS NULL
    
    UNION ALL

    SELECT C.id, C.parent_id, generation + 1
    FROM ecoli_data C
    JOIN gen P ON P.id = C.parent_id
)

SELECT COUNT(*) count, generation
FROM gen 
WHERE id NOT IN (SELECT parent_id FROM gen) iS NOT FALSE -- 아래의 3가지 방법 모두 가능
GROUP BY 2
ORDER BY 2;

-- 방법 1)
WHERE id NOT IN (SELECT parent_id FROM gen WHERE parent_id IS NOT NULL)
-- 방법 2)
WHERE id NOT IN (SELECT parent_id FROM gen) IS NOT FALSE
-- 방법 3)
WHERE NOT EXISTS (SELECT * FROM gen B WHERE gen.id = B.parent_id)

 

[ 풀이 ]

• RECURSIVE CTE의 내용은 1번 문제와 동일하므로 PASS
• 각 세대별 자식이 없는 대장균 수를 출력하기 위해서
  • 자식이 없다 = 부모 명단에 없다 : id가 parent_id에 없는 id를 COUNT(*)
  • 주의할 점은 parent_id에 NULL이 존재하므로 NOT IN 연산자를 쓸 때 조심
  • 해당 내용에 대해 따로 정리해서 포스팅 해두었다! NOT IN, NOT EXISTS와 NULL

NOT IN, NOT EXISTS와 NULL

 

---

3. GROUP BY - [부서별 평균 연봉 조회하기] _ LEVEL 3

HR_DEPARTMENT 테이블은 회사의 부서 정보를 담은 테이블입니다.

HR_EMPLOYEES 테이블은 회사의 사원 정보를 담은 테이블입니다.

HR_DEPARTMENT와 HR_EMPLOYEES 테이블을 이용해 부서별 평균 연봉을 조회하려 합니다. 부서별로 부서 ID, 영문 부서명, 평균 연봉을 조회하는 SQL문을 작성해주세요.

평균연봉은 소수점 첫째 자리에서 반올림하고 컬럼명은 AVG_SAL로 해주세요.

결과는 부서별 평균 연봉을 기준으로 내림차순 정렬해주세요.

 

[ 정답 쿼리 ]

SELECT 
    E.dept_id
    , dept_name_en
    , ROUND(AVG(sal),0) avg_sal
FROM hr_employees E
JOIN hr_department D ON E.dept_id = D.dept_id
GROUP BY 1
ORDER BY 3 DESC;

 

---

4. GROUP BY - [노선별 평균 역 사이 거리 조회하기] _ LEVEL 2

SUBWAY_DISTANCE 테이블은 서울지하철 2호선의 역 간 거리 정보를 담은 테이블입니다.

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

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

결과는 총 누계 거리를 기준으로 내림차순 정렬해주세요.

 

[ 정답 쿼리 ]

SELECT 
    route
    , CONCAT(ROUND(SUM(d_between_dist), 1) , 'km') total_distance
    , CONCAT(ROUND(AVG(d_between_dist), 2) , 'km') average_distance
FROM subway_distance
GROUP BY 1
ORDER BY ROUND(SUM(d_between_dist), 1) DESC;

 

[ 풀이 ]

1. 정렬 기준
   • 'TOTAL_DISTANCE'를 기준으로 하면 안된다. 해당 칼럼은 concat() 함수로 인해 더이상 숫자가 아니다. 문자열이다. 따라서 이 컬럼을 기준으로 정렬 시 문자열(사전식) 정렬기준에 따라 정렬되는 버그가 발생한다.
2. 반올림 함수 인자 설정
   • round(숫자, 반올림할 자릿수)
   • 반올림할 자릿수하는 말이 좀 헷갈림의 여지가 있다고 느껴지는데, 예를 들어 '소수 둘째 자리에서 반올림한다'고 가정하면 반올림할 자릿수라는 매개변수에는 1이 들어가야 한다.
   • 즉, 반올림 연산 수행 후에 유효한 자릿수가 매개변수로 사용되는 것이다.

 

---

5. String, Date - [연도 별 평균 미세먼지 농도 조회하기] _ LEVEL 4

AIR_POLLUTION 테이블은 전국의 월별 미세먼지 정보를 담은 테이블입니다.

AIR_POLLUTION 테이블에서 수원 지역의 연도 별 평균 미세먼지 오염도와 평균 초미세먼지 오염도를 조회하는 SQL문을 작성해주세요. 이때, 평균 미세먼지 오염도와 평균 초미세먼지 오염도의 컬럼명은 각각 PM10, PM2.5로 해 주시고, 값은 소수 셋째 자리에서 반올림해주세요.

결과는 연도를 기준으로 오름차순 정렬해주세요.

[ 정답 쿼리 ]

SELECT 
    YEAR(ym) `year`
    , ROUND(AVG(pm_val1), 2) `pm10`
    , ROUND(AVG(pm_val2), 2) `pm2.5`
FROM air_pollution
WHERE location2 = '수원'
GROUP BY 1
ORDER BY 1;

 

[ 풀이 ]

• 특수기호가 들어간 컬럼명은 “”또는 ``으로 감싸줘야 SQL이 제대로 인식할 수 있다

---

6. String, Date - [한 해에 잡은 물고기 수 구하기] _ LEVEL 1

낚시앱에서 사용하는 FISH_INFO 테이블은 잡은 물고기들의 정보를 담고 있습니다.

FISH_INFO 테이블에서 2021년도에 잡은 물고기 수를 출력하는 SQL 문을 작성해주세요.

이 때 컬럼명은 'FISH_COUNT' 로 지정해주세요.

 

[ 정답 쿼리 ]

SELECT COUNT(*) fish_count
FROM fish_info
WHERE YEAR(time) = 2021;