비트 연산자 활용 쿼리 리뷰

문제 1: 프로그래머스

컬럼명	타입	설명
ID	INTEGER	대장균 개체 ID
PARENT_ID	INTEGER	부모 개체 ID (NULL 가능)
SIZE_OF_COLONY	INTEGER	개체의 크기
DIFFERENTIATION_DATE	DATE	분화된 날짜
GENOTYPE	INTEGER	형질 (비트 기반 인코딩)

🎯 문제 목표
다음 조건을 모두 만족하는 대장균 개체의 수(COUNT)를 구하라.

2번 형질을 보유하지 않음

1번 또는 3번 형질을 보유함

1번과 3번을 동시에 보유해도 포함

접근 방식

초반에는 genotype에서 나머지 연산자를 활용해서 쿼리를 작성함
-> 쿼리를 이해하기 어려워서 비트 연산자 활용해서 문제를 풀었음

정답 쿼리

SELECT count(*) `count`
FROM ecoli_data
WHERE genotype & 2 = 0
AND (genotype & 4 > 0 or genotype & 1 > 0)

genotype & 2 인 이유: 2번 형질을 포함하지 않아야 하니까 = 0으로 조건
genotype & 4 > 0 or genotype & 1 > 0:
genotype & 4 = 1로 하면 세번째 비트 값만 켜질 때만 필터링되서 > 0으로 설정하면 모두 포함됨

---

문제 2: 프로그래머스

DEVELOPERS 테이블에서 Front End 스킬을 가진 개발자의 정보를 조회하려 합니다.
조건에 맞는 개발자의 ID, 이메일, 이름, 성을 조회하는 SQL 문을 작성해 주세요.
결과는 ID를 기준으로 오름차순 정렬해 주세요.

컬럼명	타입	제약조건	설명
NAME	VARCHAR(N)	UNIQUE, NOT NULL	스킬 이름 (예: Python, HTML 등)
CATEGORY	VARCHAR(N)	NOT NULL	스킬 범주 (예: Front End, Back End 등)
CODE	INTEGER	UNIQUE, NOT NULL	스킬 코드 (2의 제곱수: 1, 2, 4, 8, 16 등)

컬럼명	타입	제약조건	설명
ID	VARCHAR(N)	UNIQUE, NOT NULL	개발자 ID
FIRST_NAME	VARCHAR(N)	NULLABLE	이름
LAST_NAME	VARCHAR(N)	NULLABLE	성
EMAIL	VARCHAR(N)	UNIQUE, NOT NULL	이메일 주소
SKILL_CODE	INTEGER	NOT NULL	보유한 스킬들의 비트 코드 합산값

접근 방식

비트 연산자를 사용해 어떤 스킬이 포함되어 있는지 확인
Front End 기술이 포함된 경우만 필터링 하고 & > 0을 활용해 기술을 포함하는지 확인
Exists 사용 이유: Exists는 조건을 만족하는 서브쿼리 결과가 1개라도 존재한면 true (Front End 기술이 1개라도 있으면 결과에 포함)

정답 쿼리

SELECT id, email, first_name, last_name
FROM developers d
WHERE exists (SELECT 1
             FROM skillcodes s
             WHERE category = 'Front End'
             AND (s.code & d.skill_code) > 0
             )
ORDER BY 1;

---

문제 3: 프로그래머스

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

컬럼명	타입	설명
ID	INTEGER	대장균 개체 ID
PARENT_ID	INTEGER	부모 개체 ID (NULL 가능)
SIZE_OF_COLONY	INTEGER	개체의 크기
DIFFERENTIATION_DATE	DATE	분화된 날짜
GENOTYPE	INTEGER	형질 (비트 기반 인코딩)

접근 방식

부모의 genotype 컬럼을 추가하는 CTE 구문을 작성한 후,
비트 연산자를 활용해서 자식의 genotype이 부모의 genotype을 포함한 것만 필터링.

정답 쿼리

WITH t as
    (SELECT id, parent_id, genotype, (SELECT genotype
                                     FROM ecoli_data
                                     WHERE ed.parent_id = ecoli_data.id) parent_genotype
     FROM ecoli_data ed
    )


SELECT id, genotype, parent_genotype
FROM t
WHERE (genotype & parent_genotype) = parent_genotype
ORDER BY 1;

WHERE (genotype & parent_genotype) = parent_genotype
genotype이 parent_genotype의 genotype을 포함

---

문제4: 프로그래머스

DEVELOPERS 테이블에서 GRADE별 개발자의 정보를 조회하려 합니다. GRADE는 다음과 같이 정해집니다.

A : Front End 스킬과 Python 스킬을 함께 가지고 있는 개발자
B : C# 스킬을 가진 개발자
C : 그 외의 Front End 개발자
GRADE가 존재하는 개발자의 GRADE, ID, EMAIL을 조회하는 SQL 문을 작성해 주세요.

결과는 GRADE와 ID를 기준으로 오름차순 정렬해 주세요.

접근 방식

CTE 절에서 case 구문을 이용해 grade 컬럼을 만들어야한다
case 구문에서는 비트 연산자를 활용해 가진 기술이나 카테고리의 포함여부를 확인

초반에는 case 순서를 C -> B -> A로 설정해서 C로 설정된 row의 경우 C의 조건이 맞던 무시하기 때문에 A가 1명도 없었다

복합조건을 앞에 배치해야한다는 사실을 잊고있어서 조금 시간이 걸렸다
case 구문을 A -> B -> C로 설정하니 잘 작동되었다.

정답 쿼리

WITH t as
    (SELECT *,
        case when exists (SELECT 1
                         FROM skillcodes s
                         WHERE category = 'Front End' and (s.code & d.skill_code) > 0)
                  and exists (SELECT 1
                             FROM skillcodes s
                             WHERE name = 'Python' and (s.code & d.skill_code) > 0)
             then 'A'
            
            when exists (SELECT 1
                          FROM skillcodes s
                          WHERE name = 'C#' AND (s.code & d.skill_code) > 0) 
             then 'B'
     
             when exists (SELECT 1
                         FROM skillcodes s
                         WHERE category = 'Front End' AND (s.code & d.skill_code) > 0)
             then 'C'
             end grade
FROM developers d
)

SELECT grade, id, email
FROM t
WHERE grade is not null
ORDER BY 1, 2;