[프로그래머스 SQL] 연도별 대장균 크기의 편차 구하기 (SUM, MAX, MIN) | MySQL

[프로그래머스 SQL] 연도별 대장균 크기의 편차 구하기 (SUM, MAX, MIN) | MySQL

하단의 목차를 클릭하여 이동할 수 있습니다 :)

1. 문제
2. 해답
3. 풀이

1. 문제

• SELF JOIN
  • 해당 문제는 연도별로 MAX(SIZE_OF_COLONY)를 구하고 각 연도별 SIZE_OF_COLONY를 참감해 주어야 한다.
  • 문제는 GROUP BY로 연도별로 그룹화 할 경우 MAX(SIZE_OF_COLONY)는 구할 수 있으나 SIZE_OF_COLONY를 차감할 수가 없다. (집계가 아님).
  • 따라서 SELF JOIN문에 SUBQUERY를 사용하여 MAX(SIZE_OF_COLONY)를 추출한 뒤 다시 원본 테이블의 SIZR_OF_COLONY를 차감해준다.

---

2. 해답

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

---

3. 풀이

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

• 위는 SELF JOIN 내부의 SUBQUERY이다. 위 코드는 연도별로 연도와 연도에 해당하는 최대 SIZE_OF_COLONY를 반환한다. 
• SUBQUERY만 확인한다면 연도별 최대값만 반환하지만 이를 SELF JOIN하여 연결하면 각 연도마다 최대값이 복사되어 추가된다. 
  • 결과는 아래와 같이 SELF JOIN 후 각 MAX(DIFFERENTIATION_DATE) 값이 개별 년도마다 복사되어 JOIN 되었음을 확인할 수 있다.

[Figure 2] Result of SELF JOIN

• 따라서 위처럼 SELF JOIN을 하였다면 '2. 해답'의 코드와 같이 MAX_SIZE 필드를 JOIN한 테이블에서 가져온 뒤 기존 테이블의 SIZE_OF_COLONY를 차감할 수 있다.