Programmers - 부모의 형질을 모두 가지는 대장균 찾기

문제 설명

대장균들은 일정 주기로 분화하며, 분화를 시작한 개체를 부모 개체, 분화가 되어 나온 개체를 자식 개체라고 합니다.
다음은 실험실에서 배양한 대장균들의 정보를 담은 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 값입니다.

---

문제

부모의 형질을 모두 보유한 대장균의 ID(ID), 대장균의 형질(GENOTYPE), 부모 대장균의 형질(PARENT_GENOTYPE)을 출력하는 SQL 문을 작성해주세요. 이때 결과는 ID에 대해 오름차순 정렬해주세요.

 

---

예시

예를 들어 ECOLI_DATA 테이블이 다음과 같다면

ID	PARENT_ID	SIZE_OF_COLONY	DIFFERENTIATION_DATE	GENOTYPE
1	NULL	10	2019/01/01	1
2	1	2	2019/01/01	1
3	1	100	2020/01/01	3
4	2	16	2020/01/01	2
5	4	17	2020/01/01	8
6	3	101	2021/01/01	5
7	2	101	2022/01/01	5
8	6	1	2022/01/01	13

각 대장균 별 형질을 2진수로 나타내면 다음과 같습니다.

ID 1 : 1₍₂₎
ID 2 : 1₍₂₎
ID 3 : 11₍₂₎
ID 4 : 10₍₂₎
ID 5 : 1000₍₂₎
ID 6 : 101₍₂₎
ID 7 : 101₍₂₎
ID 8 : 1101₍₂₎

 

각 대장균 별 보유한 형질을 다음과 같습니다.

ID 1 : 1
ID 2 : 1
ID 3 : 1, 2
ID 4 : 2
ID 5 : 4
ID 6 : 1, 3
ID 7 : 1, 3
ID 8 : 1, 3, 4

 

각 개체별로 살펴보면 다음과 같습니다.

ID 1 : 최초의 대장균 개체이므로 부모가 없습니다.
ID 2 : 부모는 ID 1 이며 부모의 형질인 1번 형질을 보유하고 있습니다.
ID 3 : 부모는 ID 1 이며 부모의 형질인 1번 형질을 보유하고 있습니다.
ID 4 : 부모는 ID 2 이며 부모의 형질인 1번 형질을 보유하고 있지 않습니다.
ID 5 : 부모는 ID 4 이며 부모의 형질인 2번 형질을 보유하고 있지 않습니다.
ID 6 : 부모는 ID 3 이며 부모의 형질 1, 2번 중 2 번 형질을 보유하고 있지 않습니다.
ID 7 : 부모는 ID 2 이며 부모의 형질인 1번 형질을 보유하고 있습니다.
ID 8 : 부모는 ID 6 이며 부모의 형질 1, 3번을 모두 보유하고 있습니다.

따라서 부모의 형질을 모두 보유한 개체는 ID 2, ID 3, ID 7, ID 8 이며 결과를 ID 에 대해 오름차순 정렬하면 다음과 같아야 합니다.

ID	GENOTYPE	PARENT_GENOTYPE
2	1	1
3	3	1
7	5	1
8	13	5

---

문제 풀이 및 설명

문제의 목적

이 문제는 대장균의 분화 관계와 형질을 기준으로, 각 대장균이 부모의 형질을 모두 보유하고 있는지 확인하여 해당 대장균의 ID, GENOTYPE, PARENT_GENOTYPE을 출력하는 SQL 문제입니다.

• 부모 대장균의 형질을 모두 보유한 대장균만 출력해야 합니다.
• 출력은 대장균의 ID를 기준으로 오름차순 정렬합니다.

 

각 대장균은 GENOTYPE이라는 형질을 보유하고 있으며, 형질을 2진수로 표현할 수 있습니다. 예를 들어, GENOTYPE 값 1은 1₂이고, 3은 11₂입니다.

• 부모 대장균의 형질을 모두 보유한 대장균은 해당 대장균이 부모 형질을 포함하는 형질을 보유하는 경우입니다.
• 예를 들어, 대장균의 형질이 3이라면 부모의 형질 1을 포함해야 하며, 부모 형질이 1인 경우 해당 대장균은 형질 1을 포함하고 있어야 합니다.

해결 과정

1. 부모 대장균의 형질을 찾기 위해 JOIN 사용:
   • 대장균과 그 부모 대장균을 연결해야 하므로, ECOLI_DATA 테이블을 자기 자신과 JOIN합니다. e.PARENT_ID = p.ID로 부모와 자식을 연결합니다.
   • e는 자식 대장균을, p는 부모 대장균을 나타냅니다.
2. 형질 비교:
   • 부모 대장균의 형질 (p.GENOTYPE)을 자식 대장균의 형질 (e.GENOTYPE)과 비교하여, 자식이 부모의 형질을 모두 보유했는지를 확인합니다.
   • 이때 비트 연산을 사용합니다. 자식의 형질이 부모 형질을 모두 포함하는지 확인하려면 비트 AND 연산을 사용하여 (e.GENOTYPE & p.GENOTYPE = p.GENOTYPE)를 확인합니다. 이 조건이 참이면, 자식 대장균은 부모의 형질을 모두 보유하고 있습니다.
3. NULL 처리:
   • 부모가 없는 대장균 (첫 번째 대장균)은 부모 형질이 없기 때문에 출력에 포함되지 않도록 해야 합니다. 이를 위해 JOIN을 사용하여 부모가 없는 대장균은 결과에서 제외됩니다.
4. 결과 정렬:
   • 결과는 대장균의 ID를 기준으로 오름차순 정렬합니다.

 

1.  

최종 SQL 쿼리

SELECT e.ID, e.GENOTYPE, p.GENOTYPE AS PARENT_GENOTYPE
FROM ECOLI_DATA e
JOIN ECOLI_DATA p
  ON e.PARENT_ID = p.ID
WHERE e.GENOTYPE & p.GENOTYPE = p.GENOTYPE
ORDER BY e.ID;

쿼리 설명

1. JOIN:
   • ECOLI_DATA 테이블을 자기 자신과 JOIN합니다. 자식 대장균(e)과 부모 대장균(p)을 연결하는데, 자식 대장균의 PARENT_ID가 부모 대장균의 ID와 일치하도록 합니다.
2. WHERE e.GENOTYPE & p.GENOTYPE = p.GENOTYPE:
   • 이 조건은 자식 대장균이 부모의 형질을 모두 보유하는지 확인하는 조건입니다. 비트 연산 &를 사용하여, 자식 대장균의 형질이 부모 대장균의 형질을 모두 포함하는지 확인합니다. 만약 포함된다면 이 조건은 참이 되어 자식 대장균이 출력됩니다.
3. ORDER BY e.ID:
   • 결과를 대장균의 ID 값 기준으로 오름차순으로 정렬합니다.