2024. 10. 17. 18:00ㆍSQL 고득점 Kit/SELECT
https://school.programmers.co.kr/learn/courses/30/lessons/301651
문제 설명
대장균들은 일정 주기로 분화하며, 분화를 시작한 개체를 부모 개체, 분화가 되어 나온 개체를 자식 개체라고 합니다.
다음은 실험실에서 배양한 대장균들의 정보를 담은 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개체는 존재합니다.
예시
예를 들어 ECOLI_DATA 테이블이 다음과 같다면
ID | PARENT_ID | SIZE_OF_COLONY | DIFFERENTIATION_DATE | GENOTYPE |
1 | NULL | 10 | 2019/01/01 | 5 |
2 | NULL | 2 | 2019/01/01 | 3 |
3 | 2 | 100 | 2020/01/01 | 4 |
4 | 2 | 16 | 2020/01/01 | 4 |
5 | 2 | 17 | 2020/01/01 | 6 |
6 | 4 | 101 | 2021/01/01 | 22 |
7 | 4 | 101 | 2022/01/01 | 23 |
8 | 6 | 1 | 2022/01/01 | 27 |
각 세대별 대장균의 ID는 다음과 같습니다.
- 1 세대 : ID 1, ID 2
- 2 세대 : ID 3, ID 4, ID 5
- 3 세대 : ID 6, ID 7
- 4 세대 : ID 8
이 때 각 세대별 자식이 없는 대장균의 ID는 다음과 같습니다.
- 1 세대 : ID 1
- 2 세대 : ID 3, ID 5
- 3 세대 : ID 7
- 4 세대 : ID 8
따라서 결과를 세대에 대해 오름차순 정렬하면 다음과 같아야 합니다.
COUNT | GENERATION |
1 | 1 |
2 | 2 |
1 | 3 |
1 | 4 |
각 세대별 자식이 없는 개체의 수와 그 세대를 순서대로 나열하는 문제입니다.
우선 어떤 대장균이 어느 세대에 속했는지 알기 위해서 이전에 사용했던 재귀 테이블을 사용합니다.
이 테이블을 구성하면 각각의 대장균에 대해 어떤 세대에 속했는지를 알 수 있습니다.
그 다음, 이 세대별로 자식이 없는 개체의 수를 구해야 합니다.
저는 레프트 조인을 통해 자식 관계가 없는 대장균들의 정보를 추려낸 뒤, G2의 ID가 NULL인 값들을 필터링하는 방법으로 자식이 없는 개체들을 구했습니다.
이후에는 세대에 따라서 행이 몇 개인지 세면 되기 때문에, COUNT 함수를 사용해 그 수를 세주었습니다.
정답 코드를 작성해보겠습니다.
WITH RECURSIVE GENERATION AS (
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
FROM ECOLI_DATA E
INNER JOIN
GENERATION G
ON E.PARENT_ID = G.ID
)
SELECT COUNT(*) AS "COUNT", G1.GEN AS "GENERATION"
FROM GENERATION G1
LEFT JOIN
GENERATION G2
ON G1.ID = G2.PARENT_ID
WHERE G2.ID IS NULL
GROUP BY G1.GEN
ORDER BY G1.GEN ASC;
SQL 문제도 고난이도로 갈 수록 재귀 같은 문제들이 많이 나오는 것 같습니다.
'SQL 고득점 Kit > SELECT' 카테고리의 다른 글
[SELECT] 특정 세대의 대장균 찾기 (0) | 2024.10.16 |
---|---|
[SELECT] 대장균의 크기에 따라 분류하기 2 (0) | 2024.10.14 |
[SELECT] 부모의 형질을 모두 가지는 대장균 찾기 (1) | 2024.10.13 |
[SELECT] 특정 형질을 가지는 대장균 찾기 (0) | 2024.10.12 |
[SELECT] 대장균의 크기에 따라 분류하기 1 (0) | 2024.10.11 |