[PCSQL] 프로그래머스 - 대장균들의 자식의 수 구하기(MySQL)

대장균들의 자식의 수 구하기(Lv.3)

프로그래머스의 대장균 시리즈를 풀어봤다. 그 중, 가장 어려웠던 문제는 정답률 72%의 대장균들의 자식의 수 구하기였다.
이 문제는 테이블이 1개 뿐인데 그룹별 수 집계가 필요하기에 자기참조와 LEFT JOIN이 필요한 문제로 여러 개념이 복합적으로 등장하기 때문에 고득점을 위한 발판으로 삼기 좋은 문제라 생각한다.

프로그래머스

---

 

문제 설명

대장균들은 일정 주기로 분화하며, 분화를 시작한 개체를 부모 개체, 분화가 되어 나온 개체를 자식 개체라고 합니다.
다음은 실험실에서 배양한 대장균들의 정보를 담은 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

 

---

 

문제 정답

SELECT P.ID, COUNT(C.ID) AS CHILD_COUNT
FROM ECOLI_DATA P
LEFT JOIN ECOLI_DATA C
	ON P.ID = C.PARENT_ID
GROUP BY P.ID
ORDER BY P.ID;

 

문제 해설

이 문제를 풀기 위해서는 몇가지의 주요 개념을 이해하고 있어야 한다.

• 자기참조 테이블(Self-Referencing)
• Self JOIN
• LEFT JOIN
• GROUP BY + COUNT()
• NULL 처리

이 중 오늘은 자기참조 테이블과 JOIN 그리고 COUNT에 대해 자세히 알아보려 한다.

 

1. 자기참조 테이블(Self-Referencing)

출저: https://dba.stackexchange.com

자기참조 구조(Self-Referencing)이란 한 테이블의 컬럼이 같은 테이블의 다른 컬럼을 참조하여 한 행이 같은 테이블 내 다른 행과 관계를 가지는 구조를 말한다. 대표적으로 부모-자식 관계, 상하 계층 구조 표현이 있으며, 위 ERD에서 빨간색 박스에 있는 employees와 LookupValues 테이블이 자기참조 테이블이다. 자세히 들여다보자면, employees 테이블은 다음과 같은 자기참조 구조를 가진다.

• 컬럼: manager_id
• 참조 대상: 같은 테이블의 employee_id
• 의미: 각 직원(employee_id)이 누구를 매니저로 두는지 나타냄

즉, 한 행의 manager_id 값은 같은 employees 테이블 내 다른 직원의 employee_id를 가리키며, 이에 따라 해당 테이블의 몇가지 특징을 예상해볼 수 있다.

• 최상위 직원(CEO 등)의 manager_id = NULL 이며, 해당 컬럼은 Nullable=True 일 것이다.
• 하위 직원은 manager_id에 상위 직원의 employee_id를 넣어 계층 구조를 표현했다.
• 자기참조를 활용하여 부서별 직원 트리, 매니저별 팀원 수 등을 구할 수 있다.

이 같은 자기참조 구조는 조직도·카테고리·메뉴 구조 등을 표현하기 위한 계층 구조(Hierarchical Data), 폴더·메뉴·분류 체계를 표현하기 위한 트리 구조(Tree Data), 친구 관계·팔로워/팔로잉 등을 표현하기 위한 그래프/네트워크 구조로 많이 쓰인다.

 

2. Self JOIN으로 부모-자식 매칭 ( + LEFT JOIN → 자식 없는 부모도 포함)

위에서 알아본 바와 같이 대장균들의 자식의 수 구하기 문제는 자기참조 구조를 가진 1개의 테이블만이 주어졌다. 그런데 대장균 ID와 부모개체의 ID는 있지만 자식 ID가 없기 때문에 이를 매칭 시켜주려면 셀프 조인이 필요하다.

FROM ECOLI_DATA P
LEFT JOIN ECOLI_DATA C
ON P.ID = C.PARENT_ID

이 부분이 내가 가장 헷갈렸던 부분인데, 어떤 경우에 셀프조인이 필요한지에 대한 이해를 잘 못하고 있었기 때문이었다.
셀프조인이 필요한 때를 쉽게 알아보는 방법은 테이블에서 한가지만 따져보면 된다.

• 집계하려는 값이 같은 행 안에 있으면 → 단일 테이블로 가능
• 다른 행에 있으면 → 조인 필요

이게 무슨말이냐면, 예를 들어 employees 테이블에 부서를 나타내는 department와 사원번호를 나타내는 employee_id 라는 컬럼이 있다고 가정했을 때, 부서별 인원을 세는 문제는 다음과 같이 처리할 수 있다.

SELECT department, COUNT(*) AS emp_count
FROM employees
GROUP BY department;

위의 식이 가능한 이유는 department 컬럼 자체가 직원과 부서의 관계를 가지고 있기 때문이다. 즉, 자기 자신(row) 안에 이미 집계 기준이 포함되어 있어서 단일 테이블만으로 가능하다는 의미이다.

반면, ECOLI_DATA 테이블의 경우 각각의 행 안에 대장균 ID와 부모 개체의 ID는 있지만, 자식이 누군지 가리키는 컬럼이 없기에 집계가 불가능하다. 자식이 몇 명인지 세어야하는데, 자식은 다른 행에 있고, 자신의 row에는 정보가 없다. 

FROM ECOLI_DATA P
LEFT JOIN ECOLI_DATA C
ON P.ID = C.PARENT_ID

아래는 표는 위의 쿼리를 통해 만들어진 새테이블로, 이를 통해 쿼리가 어떻게 동작하였는지 조금 더 쉽게 이해할 수 있다.

P.ID	C.ID	C.PARENT_ID
1	3	1
2	5	2
2	4	2
3	null	null
4	6	4
5	null	null
6	null	null

정리해보자면,
셀프조인을 통해 하나를 부모 역할(P), 다른 하나를 자식 역할(C)로 지정한 뒤, 부모의 ID와 자식이 가진 부모 ID가 일치하면 그 둘을 한 행으로 묶는 과정에서 C.ID는 자연스럽게 “해당 부모의 자식 ID”가 되는 효과를 가진다. 그러므로 이제 각각의 행은 모두 본인 ID와 자식 ID를 매칭할 수 있게 된다. 또한, 문제에서 자식이 없는 대장균은 0으로 출력을 요구하였으니 LEFT JOIN을 사용해 자식이 없는 대장균도 포함하여야 한다.

 

3. COUNT() / NULL 처리

마지막으로 COUNT(C.ID)와 COUNT(C.PARENT_ID)의 차이점에 대해 알아보려 한다.

SELECT P.ID, COUNT(C.ID) AS CHILD_COUNT
FROM ECOLI_DATA P
LEFT JOIN ECOLI_DATA C
    ON P.ID = C.PARENT_ID
GROUP BY P.ID;

실제 문제에선 COUNT(C.ID)와 COUNT(C.PARENT_ID) 모두 정답처리 된다. 하지만 위 정답에선 C.ID를 사용했는데 그 이유는, C.ID는 자식 대장균 한 개를 나타내는 고유값으로 JOIN 결과에서 자식이 없으면 C.ID는 NULL이고,  COUNT(C.ID)는 NULL이 아닌 값만 세므로 자식 수를 정확히 센다.

C.PARENT_ID도 자식이 존재하면 NULL이 아니므로 비슷하게 세지만, 데이터에 중복이 있거나 PARENT_ID가 잘못 입력돼 있는 경우를 고려했을 때, ID 기준이 더 안전하다.

---

문제의 정답은 고작 6줄 밖에 안되지만 이를 이해하기 위해 많은 개념이 필요한 문제였다. 꾸준히해서 Lv5까지 다 풀어버려야지!