[SQL] BIN 계산

문제 링크

https://school.programmers.co.kr/learn/courses/30/lessons/301646

문제 설명

문제 설명
대장균들은 일정 주기로 분화하며, 분화를 시작한 개체를 부모 개체, 분화가 되어 나온 개체를 자식 개체라고 합니다.
다음은 실험실에서 배양한 대장균들의 정보를 담은 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
1	NULL	10	2019/01/01
2	NULL	2	2019/01/01
3	2	100	2020/01/01
4	2	16	2020/01/01

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

MySQL에서의 비트 연산

1. BIN() 함수

BIN() 사용한 2진법으로 변환

SELECT BIN(5); -- 출력: '101'

2. BIT 이동 연산자

비트 오른쪽 이동 연산자 ( >> )

SELECT 5 >> 1;  -- 출력: 2

5의 이진수는 101 이를 오른쪽으로 1칸 이동시키면 10이고 십진수 2 이다.

3. 비트 AND 연산

비트 AND 연산자 ( & )

SELECT BIN(5 >> 1) AS shifted_binary, 
(5 >> 1) & 1 AS result;

출력 결과

shifted_binary	result
10	0

5 >> 1을 하면
5의 이진수는 101에서 오른쪽으로 한 칸 이동하면 10

(5 >> 1) & 1은 10과 1을 AND 연산해서 0을 반환한다.

예시) 2진수 11과 10진수 7 비교는?

2진수 11은 10진수로 3 이기 때문에
10진수 3과 7을 비트wise AND 연산한 SQL Query 를 작성하면 된다.

SELECT (3 & 7) AS result;

  011  (3)
& 111  (7)
------
  011  (결과는 3)

문제 풀이 코드

비트 AND 연산자 활용

SELECT COUNT(GENOTYPE) COUNT
FROM ECOLI_DATA
WHERE GENOTYPE & 2 = 0 
AND ((GENOTYPE &1 > 0) or (GENOTYPE &4 > 0))

REGEXP , WITH 사용

WITH sub1 AS 
(
SELECT BIN(GENOTYPE) as b_n
FROM ECOLI_DATA
),
sub2 AS
(
SELECT *
FROM sub1
WHERE (NOT b_n REGEXP "1.$")
    AND (b_n REGEXP "1..$" OR b_n REGEXP "1$")
)
SELECT COUNT(*) COUNT
FROM sub2