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 값입니다

 

---

문제

2번 형질이 보유하지 않으면서 1번이나 3번 형질을 보유하고 있는 대장균 개체의 수(COUNT)를 출력하는 SQL 문을 작성해주세요. 1번과 3번 형질을 모두 보유하고 있는 경우도 1번이나 3번 형질을 보유하고 있는 경우에 포함합니다.

---

 

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

ID	PARENT_ID	SIZE_OF_COLONY	DIFFERENTIATION_DATE	GENOTYPE
1	NULL	10	2019/01/01	8
2	NULL	2	2019/01/01	15
3	2	100	2020/01/01	1
4	2	16	2020/01/01	13

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

ID 1 : 1000₍₂₎
ID 2 : 1111₍₂₎
ID 3 : 1₍₂₎
ID 4 : 1101₍₂₎

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

ID 1 : 4
ID 2 : 1, 2, 3, 4
ID 3 : 1
ID 4 : 1, 3, 4

따라서 2번 형질이 없는 대장균 개체는 ID 1, ID 3, ID 4 이며 이 중 1번이나 3번 형질을 보유한 대장균 개체는 ID 3, ID 4 입니다.

따라서 결과는 다음과 같아야 합니다.

COUNT

2

 

---

문제 분석

ECOLI_DATA 테이블의 주요 컬럼은 다음과 같습니다:

• ID: 대장균 개체의 고유 ID
• PARENT_ID: 부모 대장균 개체의 ID (최초의 대장균 개체는 NULL)
• SIZE_OF_COLONY: 대장균 집합체의 크기
• DIFFERENTIATION_DATE: 대장균 개체가 분화된 날짜
• GENOTYPE: 대장균 개체의 형질을 나타내는 정수 값 (2진수로 표현 가능)

 

형질을 2진수로 나타내기

GENOTYPE 값은 형질을 2진수로 나타낸 값입니다. 각 자리는 특정 형질을 나타내며, 예시에서는 1번, 2번, 3번, 4번 형질이 있으며, 이를 2진수로 표현할 수 있습니다.

 

예시 테이블:

ID	GENOTYPE	2진수 표현
1	8	1000₍₂₎
2	15	1111₍₂₎
3	1	0001₍₂₎
4	13	1101₍₂₎

위에서 GENOTYPE 값을 보면, 1은 1번 형질만 보유하고 있고, 13은 1, 3, 4번 형질을 보유하고 있다는 것을 알 수 있습니다.

요구 사항

1. 2번 형질이 없는 대장균 개체를 찾아야 합니다.
2. 1번이나 3번 형질을 보유한 개체를 찾아야 합니다.

해결 방법

1. 2번 형질이 없는 대장균: 2번 형질은 2진수에서 두 번째 비트에 해당합니다. 이 값이 0인 대장균을 찾으면 됩니다. 즉, GENOTYPE 값을 2로 나눈 나머지가 0인 대장균입니다.
2. 1번 또는 3번 형질을 보유한 대장균: 1번 형질은 2진수에서 첫 번째 비트, 3번 형질은 세 번째 비트에 해당합니다. 즉, GENOTYPE 값이 1 또는 4 이상인 값을 가지는 대장균을 찾으면 됩니다.

---

SQL 쿼리

위 조건을 만족하는 SQL 쿼리는 다음과 같습니다

SELECT COUNT(*) AS COUNT
FROM ECOLI_DATA
WHERE (GENOTYPE & 2 = 0)  -- 2번 형질이 없는 대장균
AND (GENOTYPE & 1 != 0 OR GENOTYPE & 4 != 0);  -- 1번이나 3번 형질을 보유한 대장균

쿼리 설명

1. GENOTYPE & 2 = 0: GENOTYPE 값에서 2번 형질이 없는지 확인하는 조건입니다. 2번 형질이 없으면, GENOTYPE 값의 두 번째 비트가 0이어야 합니다.
2. GENOTYPE & 1 != 0 OR GENOTYPE & 4 != 0: GENOTYPE 값에서 1번 형질이나 3번 형질을 보유한 대장균을 찾는 조건입니다. 각각 1번 형질은 첫 번째 비트, 3번 형질은 세 번째 비트로 확인할 수 있습니다.

예시 실행

주어진 예시 데이터에서, 조건을 만족하는 대장균 개체는 ID 3, ID 4입니다.

따라서 결과는 2가 됩니다.