[프로그래머스]조건에 맞는 개발자 찾기, 대장균들의 자식의 수 구하기

프로그래머스 SQL고득점 Kit 문제

1. 조건에 맞는 개발자 찾기

2. 대장균들의 자식의 수 구하기

---

문제1. 조건에 맞는 개발자 찾기

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

프로그래머스

풀이 자세히

문제 설명

SKILLCODES 테이블은 개발자들이 사용하는 프로그래밍 언어에 대한 정보를 담은 테이블입니다. SKILLCODES 테이블의 구조는 다음과 같으며, NAME, CATEGORY, CODE는 각각 스킬의 이름, 스킬의 범주, 스킬의 코드를 의미합니다. 스킬의 코드는 2진수로 표현했을 때 각 bit로 구분될 수 있도록 2의 제곱수로 구성되어 있습니다.

NAME	TYPE	UNIQUE	NULLABLE
NAME	VARCHAR(N)	Y	N
CATEGORY	VARCHAR(N)	N	N
CODE	INTEGER	Y	N

DEVELOPERS 테이블은 개발자들의 프로그래밍 스킬 정보를 담은 테이블입니다. DEVELOPERS 테이블의 구조는 다음과 같으며, ID, FIRST_NAME, LAST_NAME, EMAIL, SKILL_CODE는 각각 개발자의 ID, 이름, 성, 이메일, 스킬 코드를 의미합니다. SKILL_CODE 컬럼은 INTEGER 타입이고, 2진수로 표현했을 때 각 bit는 SKILLCODES 테이블의 코드를 의미합니다.

NAME	TYPE	UNIQUE	NULLABLE
ID	VARCHAR(N)	Y	N
FIRST_NAME	VARCHAR(N)	N	Y
LAST_NAME	VARCHAR(N)	N	Y
EMAIL	VARCHAR(N)	Y	N
SKILL_CODE	INTEGER	N	N

예를 들어 어떤 개발자의 SKILL_CODE가 400 (=b'110010000')이라면, 이는 SKILLCODES 테이블에서 CODE가 256 (=b'100000000'), 128 (=b'10000000'), 16 (=b'10000') 에 해당하는 스킬을 가졌다는 것을 의미합니다.

---

문제

DEVELOPERS 테이블에서 Python이나 C# 스킬을 가진 개발자의 정보를 조회하려 합니다. 조건에 맞는 개발자의 ID, 이메일, 이름, 성을 조회하는 SQL 문을 작성해 주세요.

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

---

예시

예를 들어 SKILLCODES 테이블이 다음과 같고,

NAME	CATEGORY	CODE
C++	Back End	4
JavaScript	Front End	16
Java	Back End	128
Python	Back End	256
C#	Back End	1024
React	Front End	2048
Vue	Front End	8192
Node.js	Back End	16384

DEVELOPERS 테이블이 다음과 같다면

ID	FIRST_NAME	LAST_NAME	EMAIL	SKILL_CODE
D165	Jerami	Edwards	jerami_edwards@grepp.co	400
D161	Carsen	Garza	carsen_garza@grepp.co	2048
D164	Kelly	Grant	kelly_grant@grepp.co	1024
D163	Luka	Cory	luka_cory@grepp.co	16384
D162	Cade	Cunningham	cade_cunningham@grepp.co	8452

다음과 같이 DEVELOPERS 테이블에 포함된 개발자 중 Python 스킬이나 C# 스킬을 가진 개발자의 정보가 결과에 나와야 합니다.

ID	EMAIL	FIRST_NAME	LAST_NAME
D162	cade_cunningham@grepp.co	Cade	Cunningham
D164	kelly_grant@grepp.co	Kelly	Grant
D165	jerami_edwards@grepp.co	Jerami	Edwards

• D162번 개발자의 경우 SKILL_CODE가 8452 = 8192 + 256 +4 로 Vue, Python, Cpp 스킬을 보유하고 있습니다.
• D164번 개발자의 경우 SKILL_CODE가 1024 로 C# 스킬을 보유하고 있습니다.
• D165번 개발자의 경우 SKILL_CODE가 400 = 256 + 128 + 16 으로 Python, Java, JavaScript 스킬을 보유하고 있습니다.

---

풀이를 위한 선수지식

 

처음 이 문제를 접할 땐, 어떻게 풀어야할지 막막했다.

 

이 문제를 풀기 위해선 이진법과 bit코드에 대한 선수 지식이 있어야 한다. 

 

1. 10진수를 2진수로, 2잔수를 10진수로 & 비트 연산자 활용

비트 연산자 활용

• AND (&): 두 비트 모두 1일 때 결과가 1이 됨
  ex: 1011 & 1101 = 1001
• OR (|): 두 비트 중 하나라도 1이면 결과가 1이 됨
  ex: 1010 | 1100 = 1110
• XOR (^): 두 비트가 서로 다를 때 결과가 1이 됨
  ex: 1010 ^ 1100 = 0110
• NOT (~): 비트를 반전시킴. 1은 0으로, 0은 1로 바뀜
  ex: ~1010 = 0101 (실제로는 시스템에 따라 부호가 반전될 수 있으며, 32비트나 64비트 시스템에서는 맨 앞의 1이 여러 개가 될 수 있음)
• Left Shift (<<): 비트를 왼쪽으로 지정된 횟수만큼 이동시키고, 오른쪽에는 0을 채운다.
  ex: 1011 << 2 = 101100
• Right Shift (>>): 비트를 오른쪽으로 지정된 횟수만큼 이동시키고, 왼쪽에는 최상위 비트(부호 비트)를 채운다.
  ex: 1011 >> 2 = 0010

https://velog.io/@ssfic3380/%ED%94%84%EB%A1%9C%EA%B7%B8%EB%9E%98%EB%A8%B8%EC%8A%A4-SQL-%EA%B3%A0%EB%93%9D%EC%A0%90-%ED%82%B7-%EC%A1%B0%EA%B1%B4%EC%97%90-%EB%A7%9E%EB%8A%94-%EA%B0%9C%EB%B0%9C%EC%9E%90-%EC%B0%BE%EA%B8%B0

프로그래머스 SQL 고득점 킷 조건에 맞는 개발자 찾기

 

10진수를 2진수로, 2잔수를 10진수로

https://m.blog.naver.com/icbanq/221727893563

[진법변환] 2진수,8진수,10진수,16진수 쉽게 변환하는 방법 알아보기!

 

2. Bit코드 계산 방법 (AND, OR, NOT, XOR)

https://baessi.tistory.com/16

 

이 글에선 이 두가지 개념에 대해 알고있다는 전제 하에 진행된다. 

 

풀이

문제에 대해서 간결하게 말하면, SKILLCODES테이블 CODE칼럼에 Python과 C#을  DEVELOPERS테이블 SKILL_CODE칼럼 값으로 포함하는 개발자의 정보를 추출하는 것이다.

 

개요

Step 1.) Devolopers테이블과 Skillcodes테이블의 코드값을 이진수로 표현

Step 2.) 두 테이블의 코드값을 비교

Step 3.) 정렬 및 추출

 

Step 1.) Devolopers테이블과 Skillcodes테이블의 코드값을 이진수로 표현

DEVEOPERS테이블에 SKILL_CODE값은 SKILLCODES테이블에 있는 CODE값들의 합으로 이루어진다. 

 

즉, 이 문제에서 개발자들이 어떤 프로그래밍 언어를 사용하는지 파악하기 위해선, 각각의 DEVEOPERS테이블에 SKILL_CODE값들을 SKILLCODES테이블에 있는 CODE값들로 분해를 해야한다는 것이다. 

 

생각나는 것 중 하나는, 10진수로 표현되어있는 것들을 전부 2진수로 표현하면, 쉽게 이것들을 파악할 수 있다. 

빠른 이해를 위해서, 사진을 첨부한다. 

 

SKILLCODES테이블에서 CODE칼럼값을 이진수로 표현한 BIN_CODE칼럼을 추가한 SKILLCODES_TABLE

 

SKILLCODES테이블의 CODE칼럼을 각각 이진수로 나타내면 위 사진과 같다. 

 

 

DEVELOPERS테이블에 SKILL_CODE칼럼을 이진수로 표현한 BIN_SKILL_CODE칼럼을 추가한 DEVELOPERS_TABLE

 

위 사진과 같이, 자릿수에 1이 있는지로 개발자들이 어떤 프로그래밍 언어를 사용하는지 알 수 있다. 

-- SKILLCODES_TABLE과 DEVELOPERS_TABLE을 만들고, 추출하는 쿼리

WITH DEVELOPERS_TABLE AS (
    SELECT
        *, 
        BIN(SKILL_CODE) AS BIN_SKILL_CODE
    FROM DEVELOPERS  
),

SKILLCODES_TABLE AS (
     SELECT
        *, 
        BIN(CODE) AS BIN_CODE
    FROM SKILLCODES  
)

# SELECT * FROM DEVELOPERS_TABLE ; 
SELECT * FROM SKILLCODES_TABLE ;

 

 

Step 2.) 두 테이블의 코드값을 비교

 

선수지식에서 다룬 것과 같이, 비트 연산자를 활용해서 계산을 할 것이다. 

Developers_table에서 개발자 Jerami의 코드를 살펴보자. 

 

Jerami는 코드값 400으로 이진수로 표현할 시, 110010000이 된다. 

 

즉, Jerami는 코드값 10000, 10000000 , 100000000 을 skill_code값으로 가지는 프로그래밍 언어를 사용한다는 의미이다. 

 

Step1에서 예시를 위해, 직접 2진수로 표현을 하였지만, sql쿼리로 짤 때는 직접 BIT라는 함수를 사용하여 표현해줄 필요는 없다. 

SQL내부에서 비트연산이 이루어지는 동작이 되기 때문이다. 

 

따라서, SKILLCODES테이블에서 Python과 C#의 코드값과 DEVELOPERS테이블의 개발자들의 코드값이 둘다 같은 위치에서 1을 가져서, 최종적으로는 True를 반환해야 한다. (AND, & 사용)

 

DEVELOPERS테이블에서 데이터를 추출할 때, 사용되는 조건절이 형성된다. 

-- 조건절만 정의
WHERE SKILL_CODE & (
	SELECT 
    	CODE
    FROM SKILLCODES
    WHERE NAME = 'Python'
)
	OR SKILL_CODE & (
	SELECT 
    	CODE
    FROM SKILLCODES
    WHERE NAME = 'C#'
)

 

Step 3.) 정렬 및 추출

최종 쿼리는 다음과 같다. 

 

SELECT # Step3. 정렬 및 추출
    ID, EMAIL, FIRST_NAME, LAST_NAME
FROM DEVELOPERS
WHERE SKILL_CODE & ( # Step2. 비트연산자 활용 코드값 비교
    SELECT 
        CODE
    FROM SKILLCODES
    WHERE NAME = 'Python'
) 
    OR 
    SKILL_CODE & (
    SELECT 
        CODE
    FROM SKILLCODES
    WHERE NAME = 'C#'
) 
ORDER BY ID ASC 
;

---

쿼리 총정리

-- 코드를 작성해주세요
# WITH DEVELOPERS_TABLE AS (
#     SELECT
#         *, 
#         BIN(SKILL_CODE) AS BIN_SKILL_CODE
#     FROM DEVELOPERS  
# ),

# SKILLCODES_TABLE AS (
#      SELECT
#         *, 
#         BIN(CODE) AS BIN_CODE
#     FROM SKILLCODES  
# )
# SELECT * FROM DEVELOPERS_TABLE ; 
# SELECT * FROM SKILLCODES_TABLE ; 

SELECT 
    ID, EMAIL, FIRST_NAME, LAST_NAME
FROM DEVELOPERS
WHERE SKILL_CODE & (
    SELECT 
        CODE
    FROM SKILLCODES
    WHERE NAME = 'Python'
) 
    OR 
    SKILL_CODE & (
    SELECT 
        CODE
    FROM SKILLCODES
    WHERE NAME = 'C#'
) 
ORDER BY ID ASC 
;

 

 

---

문제2. 대장균들의 자식의 수 구하기

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

프로그래머스

풀이 자세히

문제 설명

대장균들은 일정 주기로 분화하며, 분화를 시작한 개체를 부모 개체, 분화가 되어 나온 개체를 자식 개체라고 합니다.

다음은 실험실에서 배양한 대장균들의 정보를 담은 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 값입니다.

---

문제

대장균 개체의 ID(ID)와 자식의 수(CHILD_COUNT)를 출력하는 SQL 문을 작성해주세요. 자식이 없다면 자식의 수는 0으로 출력해주세요. 이때 결과는 개체의 ID 에 대해 오름차순 정렬해주세요.

---

예시

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

ID	PARENT_ID	SIZE_OF_COLONY	DIFFERENTIATION_DATE	GENOTYPE
1	NULL	10	2019/01/01	5
2	NULL	2	2019/01/01	3
3	1	100	2020/01/01	4
4	2	17	2020/01/01	4
5	2	10	2020/01/01	6
6	4	101	2021/01/01	22

ID 1인 개체의 자식은 ID 3으로 1개 ID 2인 개체의 자식은 ID 4,5 로 2개 ID 4인 개체의 자식은 ID 6으로 1개이며 나머지 개체들은 자식이 없으므로 ID 에 대해 오름차순 정렬하면 결과는 다음과 같아야 합니다.

ID	CHILD_COUNT
1	1
2	2
3	0
4	1
5	0
6	0

---

 

풀이

이 문제는 생각하기 힘들수도 있지만, PARENT_ID의 집계를 나타내야 하는 문제이다. 

 

논리는 전에 있던 쿼리 문제2(업그레이드 된 아이템 구하기)와 같기 때문에 생략한다. 

논리가 궁금한 사람들을 위해 링크를 참조한다. 

https://jihoonjihoon.tistory.com/26

[프로그래머스]상위 n개 레코드, 업그레이드 된 아이템 구하기

 

 

문제는 NULL값들도 전부 0으로 집계가 되어야 한다는 함정이 있다. 

 

계속 GROUP BY와 COUNT함수를 사용해도, NULL값들이 있는 ID에 대해 집계를 하지 않는다. 

 

여기서 발생하는 문제를 필자는 PARENT_ID를 기준으로 COUNT집계한 프레임과 기존의 프레임을 활용해서, JOIN을 활용하여 해결하였다. 

 

빠른 이해를 위한 사진 첨부A

 

두 테이블을 join을 해야한다. 

 

JOIN시 주의해야 할 점은 ID가 1부터 6까지 전부 나와야하기 때문에, ECOLI_DATA를 기준으로 JOIN을 해야한다. 

 

코드는 다음과 같다. 

-- 코드를 작성해주세요

WITH CNT_TABLE AS ( # PARENT_ID별 COUNT집계하는 프레임
    SELECT 
        PARENT_ID, 
        COUNT(PARENT_ID) AS CNT
    FROM ECOLI_DATA 
    WHERE PARENT_ID IS NOT NULL 
    GROUP BY PARENT_ID 
)


SELECT 
    E.ID,
    IFNULL(C.CNT, 0) AS CHILD_COUNT
FROM ECOLI_DATA E 
    LEFT JOIN CNT_TABLE C
    ON E.ID = C.PARENT_ID 
ORDER BY ID ASC
;

 

JOIN할 테이블을 생각하고, 두 프레임을 합치는 방식으로 문제를 해결하였다. 

 

 마음만을 가지고 있어서는 안된다. 반드시 실천하여야 한다.

-이소룡-