분기별 분화된 대장균 개체 수 구하기

문제 설명

대장균들은 일정 주기로 분화하며, 분화를 시작한 개체를 부모 개체, 분화되어 나온 개체를 자식 개체라고 합니다.
실험실에서 배양한 대장균들의 정보를 담은 ECOLI_DATA 테이블은 다음과 같은 구조를 가지고 있습니다.

ECOLI_DATA 테이블

Column name	Type	Nullable	Description
ID	INTEGER	FALSE	대장균 개체의 ID
PARENT_ID	INTEGER	TRUE	부모 개체의 ID
SIZE_OF_COLONY	INTEGER	FALSE	개체의 크기
DIFFERENTIATION_DATE	DATE	FALSE	분화되어 나온 날짜
GENOTYPE	INTEGER	FALSE	개체의 형질

최초의 대장균 개체의 PARENT_ID는 NULL 값입니다.

예를 들어, ECOLI_DATA 테이블이 아래와 같다면

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

각 분기별로 분화된 대장균 개체는 다음과 같이 나뉩니다.

• 1분기 : ID 1, ID 3
• 2분기 : ID 2, ID 4
• 3분기 : ID 5
• 4분기 : ID 6

따라서 결과는 다음과 같아야 합니다.

QUARTER	ECOLI_COUNT
1Q	2
2Q	2
3Q	1
4Q	1

---

문제

각 분기(QUARTER)별 분화된 대장균의 개체의 총 수(ECOLI_COUNT)를 출력하는 SQL 문을 작성해주세요. 이때 각 분기에는 ‘Q’ 를 붙이고 분기에 대해 오름차순으로 정렬해주세요. 대장균 개체가 분화되지 않은 분기는 없습니다.

---

풀이 과정

1. 분기 추출
   • QUARTER(DIFFERENTIATION_DATE) 함수를사용하여 각 대장균 개체의 분화 날짜에서 해당 분기를 숫자(1~4)로 추출
2. 형식 지정
   • CONCAT(QUARTER(DIFFERENTIATION_DATE), ‘Q’) 함수 를 사용하여 추출한 분기 숫자 뒤에 문자 ‘Q’ 붙이고 ‘1Q’, ‘2Q’, ‘3Q’… 이런 형식으로 결과 표시
3. 집계
   • COUNT(*) 함수 이용해 각 분기에 분화된 대장균 개체의 총 개수를 계산
4. 그룹화 및 정렬
   • GROUP BY 절에 CONCAT(QUARTER(DIFFERENTIATION_DATE), ‘Q’) 표현식을 사용해서 동일한 분기의 데이터를 그룹화
   • ORDER BY 로 그룹화된 결과를 분기 순서대로 오름차순 정렬

• 교훈
  • QUARTER랑 CONCAT() 사용해서 분기 형식 데이터 출력!!!!! 아주 유용한 함수를 알았다.
  • MySQL에도 익숙해지고 싶어서 앞으로는 MySQL로 도전을 해보려 한다…. 으라아아아앗! 찻! 으찻찻!

---

정답

SELECT 
    CONCAT(QUARTER(DIFFERENTIATION_DATE), 'Q') AS QUARTER,
    COUNT(*) AS ECOLI_COUNT
FROM 
    ECOLI_DATA
GROUP BY 
    CONCAT(QUARTER(DIFFERENTIATION_DATE), 'Q')
ORDER BY 
    QUARTER;

---

Tip

1. 분기 추출하고 변환하기
   • QUARTER() 함수 사용하여 날짜에서 분기 정보 cncnf
   • CONCAT() 함수 사용하여 분기 번호 뒤에 Q 붙여서 형식 맞추기
2. GROUP BY절 사용하여 각 분기에 해당하는 대장균 개체 수 집계하기

SQL Dialect Used: MySQL

---

개념 확인 (MySQL)

• QUARTER 함수
  • MySQL의 QUARTER() 함수는 날짜 값에서 해당 날짜가 몇 분기에 속하는지를 숫자로 반환하는 내장 함수
    즉! 입력한 날짜가 1월부터 3월이면 1, 4월부터 6월이면 2, 7월부터 9월이면 3, 10월부터 12월이면 4를 반환함
• CONCAT 함수
  • 여러 문자열 또는 문자열로 변환 가능한 값들을 하나의 문자열로 연결하여 반환하는 내장 함수
  • NULL 처리: 인수 중 하나라도 NULL이면 기본적으로 결과 전체가 NULL 처리 됨

---

Useful link

분기별 분화된 대장균 개체 수 구하기