[프로그래머스 SQL Lv.1] 특정 형질을 가지는 대장균 찾기 (MySQL)

문제풀이 : https://school.programmers.co.kr/learn/courses/30/lessons/301646

프로그래머스

 

1. 테이블 : ECOLI_DATA

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

 

3. 문제풀이

형질을 이진수로 변환하고, 이진수를 이용하여 형질의 보유 유무를 찾는 것이 중요한 문제였다.

형질의 번호는 다음 예시와 같이 정수 값을 이진수로 변환해서 각 자리 비트에 대응된다. (오른쪽부터 차례대로 형질 1, 형질 2, ...)

• ID 1의 GENOTYPE이 8이면, 이진법으로 1000 -> 형질 4만 보유
• ID 2의 GENOTYPE이 15이면, 이진법으로 1111 -> 형질 1, 2, 3, 4 모두 보유
• ID 3의 GENOTYPE이 1이면, 이진법으로 0001 -> 형질 1만 보유
• ID 4의 GENOTYPE이 13이면, 이진법으로 1101 -> 형질 1, 3, 4 보유

그렇다면 이진수를 이용해서 형질 1 또는 3은 보유하고 형질 2는 보유하지 않은 대장균 개체를 어떻게 찾을까?

비트연산자를 이용해야 한다.

먼저 1번이나 3번 형질을 보유하고 있는지를 살펴보자. 형질 1 또는 3을 보유하기 위해서는 이진수 0101 과의 비트 AND(&) 연산 결과가 0보다 커야 한다.

 

예를 들어

• 형질 1만 보유한 개체의 GENOTYPE은 이진수로 0001 -> 이진수 0101과의 비트 AND(&) 연산 결과 : 0001(정수로 1)이므로 0보다 크다. 
• 형질 3만 보유한 개체의 GENOTYPE은 이진수로 0100 -> 이진수 0101과의 비트 AND(&) 연산 결과 : 0100(정수로 4)이므로 0보다 크다.
• 형질 1과 3 모두 보유한 개체의 GENOTYPE은 이진수로 0101 -> 이진수 0101과의 비트 AND(&) 연산 결과 : 0101(정수로 5)이므로 0보다 크다.
• 형질 1과 3 모두 보유하지 않고 4만 보유한 개체의 GENOTYPE은 이진수로 1000 -> 이진수 0101과의 비트 AND(&) 연산 결과 : 0000(정수로 0)이므로 0보다 크지 않다.

 

따라서 WHERE genotype & 5 > 0 조건이 포함되어야 한다.

이때 이진수가 아니라 '5'로 비교하는 이유는, sql는 기본적으로 숫자를 십진수로 인식하고 비트연산자를 만나면 내부적으로 자동 이진 변환해서 비트연산하기 때문!
만약 이진수 자체로 비교하고 싶다면, 해당 값이 이진수임을 알려주는 0b(또는 MySQL 기준 b'0101'도 가능) 같은 이진수 리터럴을 사용하면 된다.
ex) WHERE genotype & 0b0101 > 0또는 WHERE genotype & b'0101' > 0

 

동일한 방식으로 2번 형질을 보유하지 않기 위해서는 이진수 0010과의 비트 AND(&) 연산 결과가 0이어야 한다. (보유하지 않기 때문!!)

따라서 WHERE genotype & 2 = 0 조건이 포함되어야 한다.

 

4. 정답

따라서 정답은 

SELECT count(*) as count
FROM ecoli_data
WHERE genotype & 5 > 0 AND genotype & 2 = 0

 

 

---

Reference

https://wing-beat.tistory.com/140

[프로그래머스 코딩테스트 연습 SQL - 14] 조건에 맞는 개발자 찾기 (MySQL) | SQL BIT 연산

https://velog.io/@hskhyl/SQL-%EA%B4%80%EB%A0%A8-%EC%9D%B4%EC%A7%84%EC%88%98-%ED%92%80%EC%9D%B4-%EC%A0%95%EB%A6%AC

 

SQL 관련 이진수 풀이 정리

https://informatik.tistory.com/75

[SQL] 특정 형질을 가지는 대장균 찾기 풀이 / 비트 연산이란?