[Programmers / MySQL] 특정 형질을 가지는 대장균 찾기 (Level 1)

📌 문제

문제 설명

대장균들은 일정 주기로 분화하며, 분화를 시작한 개체를 부모 개체, 분화가 되어 나온 개체를 자식 개체라고 합니다. 다음은 실험실에서 배양한 대장균들의 정보를 담은 ECOLI_DATA 테이블입니다. ECOLI_DATA 테이블의 구조는 다음과 같으며, ID, PARENT_ID, SIZE_OF_COLONY, DIFFERENTIATION_DATE, GENOTYPE 은 각각 대장균 개체의 ID, 부모 개체의 ID, 개체의 크기, 분화되어 나온 날짜, 개체의 형질을 나타냅니다.

 

 

최초의 대장균 개체의 PARENT_ID 는 NULL 값입니다.

 

문제

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

 

예시

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

 

각 대장균 별 형질을 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 입니다.

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

---

📌 나의 풀이

Code

SELECT COUNT(*) AS COUNT
FROM ECOLI_DATA
WHERE (BIN(GENOTYPE) LIKE '%0_' OR BIN(GENOTYPE) LIKE '_')
    AND (BIN(GENOTYPE) LIKE '%1'OR BIN(GENOTYPE) LIKE '%1__')

Solution

1. BIN 함수를 사용하여 GENOTYPE을 이진수로 바꾸기

 

2. 이진수로 바뀐 GENOTYPE에 대해 아래 두 조건을 동시에 만족하는 행만 필터링

• 오른쪽에서 두번째 자리가 0이거나, 한자리 밖에 없는 경우 (2번 형질을 보유하지 않는 경우)
• 가장 오른쪽 자리가 1이거나, 오른쪽에서 세번째 자리가 1인 경우 (1번 형질 또는 3번 형질을 보유하는 경우)

3. 필터링 된 전체 행의 개수 출력

---

📌 다른 사람의 풀이

Code

SELECT COUNT(*) AS COUNT
FROM ECOLI_DATA A
WHERE (GENOTYPE & 2) != 2 
    AND ((GENOTYPE & 4) = 4 OR (GENOTYPE & 1) = 1)

Solution

1. 비트 연산자를 사용하여 아래 두 조건을 동시에 만족하는 행 필터링

• 2의 비트와 GENOTYPE의 비트를 AND 연산자로 비교했을 때 2의 비트가 그대로 나오지 않는 경우 (두번째 비트가 1이 아닌 경우)
• 4의 비트와 비교했을 때 4가 그대로 나오거나, 1의 비트와 비교했을 때 1이 그대로 나오는 경우 (세번째 비트가 1이거나 첫번째 비트가 1인 경우)

2. 필터링 된 전체 행의 개수 출력

---

배운 내용

1. SQL에서도 파이썬과 같이 비트 연산자 사용이 가능하다.

• &, |, ^, ~, <<, >> 모두 사용 가능

2. BIN 함수의 반환값은 기본적으로 문자열이므로 LIKE로 패턴 매칭이 가능하다.