[SQL] 연도별 대장균 크기의 편차 구하기(프로그래머스/MySQL/Level 2)

안녕하세요! 데코입니다!

 

오늘은 프로그래머스 코딩테스트 연습에 있는 "연도별 대장균 크기의 편차 구하기" 문제를 포스팅하려고 합니다!

 

바로 포스팅 시작할게요! :)

(출처 : https://school.programmers.co.kr/learn/courses/30/lessons/299310)

---

 

1. 문제 : 연도별 대장균 크기의 편차 구하기(Lv. 2)

 

---

 

2. 문제 설명

 

 

대장균들은 일정 주기로 분화하며,

분화를 시작한 개체를 부모 개체, 분화가 되어 나온 개체를 자식 개체라고 합니다.

다음은 실험실에서 배양한 대장균들의 정보를 담은 ECOLI_DATA 테이블입니다.

ECOLI_DATA 테이블의 구조는 다음과 같으며,

ID, PARENT_ID, SIZE_OF_COLONY, DIFFERENTIATION_DATE, GENOTYPE 은 각각

대장균 개체의 ID, 부모 개체의 ID, 개체의 크기, 분화되어 나온 날짜, 개체의 형질을 나타냅니다.

 

[ 테이블 구조('ECOLI_DATA') ]

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 값입니다.

 

[ 문제 ]

분화된 연도(YEAR), 분화된 연도별 대장균 크기의 편차(YEAR_DEV), 대장균 개체의 ID(ID) 출력하는 SQL 문을 작성해 주세요.

 

분화된 연도별 대장균 크기의 편차는

분화된 연도별 가장 큰 대장균의 크기 - 각 대장균의 크기로 구하며

결과는 연도에 대해 오름차순으로 정렬하고

같은 연도에 대해서는 대장균 크기의 편차에 대해 오름차순으로 정렬해주세요.

 

[ 예시 ]

 

예를 들어 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	10	2020/01/01	4
5	2	17	2020/01/01	6
6	4	101	2021/01/01	22

 

분화된 연도별 가장 큰 대장균의 크기는 다음과 같습니다.

2019 : 10
2020 : 100
2021 : 101

따라서 각 대장균의 분화된 연도별 대장균 크기의 편차는 다음과 같습니다.

ID 1 : 10 - 10 = 0
ID 2 : 10 -2 = 8
ID 3 : 100 - 100 = 0

ID 4 : 100 - 10 = 90
ID 5 : 100 - 17 = 83
ID 6 : 101 -101 - 0

이를 분화된 연도에 대해 오름차순으로 정렬하고 

같은 연도에 대해서는 대장균 크기의 편차에 대해 오름차순으로 정렬하면

결과는 다음과 같아야 합니다.

 

[ 출력 결과 ]

YEAR	YEAR_DAY	ID
2019	0	1
2019	8	2
2020	0	3
2020	83	5
2020	90	4
2021	0	6

 

 

---

 

3. 문제 풀이

-- 1안
SELECT  YEAR(A.DIFFERENTIATION_DATE) AS YEAR
        , ABS(A.SIZE_OF_COLONY - B.MAX_SIZE) AS YEAR_DEV
        , A.ID
  FROM  ECOLI_DATA  A
  LEFT
  JOIN  (
        SELECT  YEAR(A.DIFFERENTIATION_DATE) AS YEAR
                , MAX(A.SIZE_OF_COLONY) AS MAX_SIZE
          FROM  ECOLI_DATA A
         GROUP  
            BY  YEAR(A.DIFFERENTIATION_DATE)
        ) B
    ON  YEAR(A.DIFFERENTIATION_DATE) = B.YEAR
 ORDER
    BY  YEAR ASC
        , YEAR_DEV ASC

 

 

이번 문제는 총 1안 한 가지로 작성했습니다!

 

 

#  1안 풀이 설명

# [ 문제 접근 설명 ]

문제를 읽고 조건에 맞는 쿼리를 작성하기 위해서 다음과 같이  네 가지 단계로 진행했어요!

1. ECOLI_DATA 테이블에서 연도별 최대 크기 구하기(GROUP BY, MAX 함수)

2. ECOLI_DATA  테이블과 1번에서 구한 값을 결합하기(JOIN)

3. 크기 편차 구하기(SELECT 문)

4. 연도와 크기 편차에 대해 오름차순 정렬(ORDER BY)

 

# [ 1안 쿼리 작성 설명 ]

 

 1  첫 번째 단계에서는, 문제에서 요청한 것이 연도별 대장균 크기의 편차이기 때문에

 

연도별 가장 큰 대장균의 크기를 구해주었습니다.

 

분화된 일자만 존재하므로, YEAR함수를 통해 연도만을 추출하고

 

이것을 GROUP BY를 통해 연도별 가장 큰 대장균의 크기를 구해주었어요!

 

SELECT   YEAR(A.DIFFERENTIATION_DATE) AS YEAR
                  , MAX(A.SIZE_OF_COLONY) AS MAX_SIZE
   FROM   ECOLI_DATA A
GROUP  
        BY   YEAR(A.DIFFERENTIATION_DATE)

 

 

 2  두 번째 단계에서는 첫 번째 단계에서 구한 것을 서브 쿼리로 만들어 준 뒤

 

ECOLI_DATA 테이블과 LEFT JOIN 해주었습니다! 

 

JOIN 조건인 ON 절에서는 YEAR를 통해 구한 연도를 서로 연결해 주었습니다!

 

SELECT   *
     LEFT
     JOIN  (
                 SELECT   YEAR(A.DIFFERENTIATION_DATE) AS YEAR
                                  , MAX(A.SIZE_OF_COLONY) AS MAX_SIZE
                    FROM   ECOLI_DATA A
                 GROUP  
                          BY   YEAR(A.DIFFERENTIATION_DATE)
                 ) B
          ON  YEAR(A.DIFFERENTIATION_DATE) = B.YEAR

 

 

 3  세 번째 단계에서는 SELECT 문에서 대장균 크기의 편차를 구해주기 위해 계산을 해주었습니다.

 

또한, 편차를 구하는 것인 만큼 절댓값인 ABS 함수를 통해 음수가 아닌 차이인 값만큼을 산출해 주었습니다.

 

그리고 문제에서 요청한 것과 같이 별칭을 맞춰서 적어주었습니다.

 

SELECT    YEAR(A.DIFFERENTIATION_DATE) AS YEAR
                  , ABS(A.SIZE_OF_COLONY - B.MAX_SIZE) AS YEAR_DEV
                  , A.ID
     LEFT
     JOIN  (
                 SELECT   YEAR(A.DIFFERENTIATION_DATE) AS YEAR
                                  , MAX(A.SIZE_OF_COLONY) AS MAX_SIZE
                    FROM   ECOLI_DATA A
                 GROUP  
                          BY   YEAR(A.DIFFERENTIATION_DATE)
                 ) B
          ON  YEAR(A.DIFFERENTIATION_DATE) = B.YEAR

 

 

 4  네 번째 단계에서는 문제에서 요청한 것과 같이 정렬을 해주었습니다.

 

YEAR(연도)와 YEAR_DEV(크기 편차)에 대해 오름차순 정렬을 해주었습니다.

 

이전 문제에서도 말씀드린 것과 같이,

 

쿼리를 작성한 사람이 아니어도, 다른 사람이 보았을 때 어떤 쿼리인지를

 

직관적으로 나타내주기 위해 정렬조건을 꼭 넣어서 작성하는 것이 중요하다고 생각합니다.

 

SELECT   YEAR(A.DIFFERENTIATION_DATE) AS YEAR
                  , ABS(A.SIZE_OF_COLONY - B.MAX_SIZE) AS YEAR_DEV
                  , A.ID
     LEFT
     JOIN  (
                 SELECT   YEAR(A.DIFFERENTIATION_DATE) AS YEAR
                                  , MAX(A.SIZE_OF_COLONY) AS MAX_SIZE
                    FROM   ECOLI_DATA A
                 GROUP  
                          BY   YEAR(A.DIFFERENTIATION_DATE)
                 ) B
          ON  YEAR(A.DIFFERENTIATION_DATE) = B.YEAR
 ORDER
          BY   YEAR ASC
                  , YEAR_DEV ASC

 

 

 

정답...!

---

 

4. 데코의 문제 풀이 후기

 

이번 문제에는 프로그래머스에 LEVEL 2 문제를 풀어보았습니다.

 

이전 문제들과 조금 차이가 있었던 것은, YEAR() 함수로 구한 값을 ON 절에 이용한 것 정도입니다.

 

이외에는 이전에 다른 문제들과 큰 차이는 없고

 

문제에서 다루는 도메인 정도만 달라진 것 같네요!

 

혹시나 더 좋은 쿼리나 어렵게 느껴지신 부분이 있다면 댓글로 남겨주세요! 

 

---

오늘 문제에서 설명이 어려운 부분 혹은 잘 이해가 안 가는 부분

그리고 더 궁금한 내용이 있다면

언제든지 댓글로 남겨주세요!

빠르게 궁금증을 해결해 드릴게요!

공감과 댓글은 큰 힘이 됩니다!

오늘도 블로그 방문해 주시고

포스팅 읽어주셔서 감사합니다!