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

1. 문제목적

서브쿼리, 집계 함수(MAX, MIN), GROUP BY와 날짜 처리 함수를 활용하여 데이터를 그룹화하고, 그룹 내에서 계산을 수행하는 방법을 학습하기 위해 설계되었다. 이를 통해 다음과 같은 SQL 개념을 익히는 것이 목표이다.

• 그룹화된 데이터의 최대값, 최소값 등의 집계 계산
• 서브쿼리를 활용한 동적 계산
• 연도별 데이터 분석 및 정렬

---

2. 핵심개념

서브쿼리

• 정의: 메인 쿼리 안에 포함되어 연산을 수행하는 하위 쿼리.

SELECT column1,
       (SELECT MAX(column2) FROM table_name WHERE condition) AS alias_name
FROM table_name;

• 서브쿼리는 메인 쿼리의 조건에 따라 동적으로 값을 계산하는 데 사용된다.

집계 함수 (MAX, MIN)

• MAX: 그룹화된 데이터에서 최대값을 반환.
• MIN: 그룹화된 데이터에서 최소값을 반환.

SELECT MAX(column_name), MIN(column_name) FROM table_name GROUP BY group_column;

DATE 함수 (YEAR)

• YEAR: 날짜 데이터에서 연도를 추출하는 함수.

SELECT YEAR(date_column) FROM table_name;

ORDER BY

• 정의: 결과 데이터를 정렬한다.

SELECT column_name FROM table_name ORDER BY column_name ASC;

---

3. 문제해결 절차

(1) 문제 분석

• 테이블에서 각 연도의 대장균 크기(SIZE_OF_COLONY)를 분석해야 한다.
• 연도별로 최대 크기와 개별 크기의 편차를 계산해야 한다.
• 연도별로 정렬된 결과를 반환해야 한다.

(2) 해결 접근 방법

1. 연도 추출:
   • YEAR(DIFFERENTIATION_DATE)로 데이터를 연도별로 분석.
2. 서브쿼리로 최대값 계산:
   • 연도별 최대 크기를 서브쿼리로 계산.
3. 편차 계산:
   • 서브쿼리에서 구한 최대값과 개별 값의 차이를 계산.
4. 결과 정렬:
   • 연도별 오름차순으로 정렬하고, 편차 값에 따라 정렬.

---

4. 코드작성

-- ECOLI_DATA 테이블에서 연도별 대장균 크기의 편차를 계산하는 SQL 문
SELECT
    YEAR(DIFFERENTIATION_DATE) AS YEAR, -- 날짜에서 연도를 추출하고 별칭 부여
    (
        SELECT MAX(SIZE_OF_COLONY)      -- 서브쿼리: 연도별 최대 크기 계산
        FROM ECOLI_DATA
        WHERE YEAR(DIFFERENTIATION_DATE) = YEAR -- 현재 연도와 일치하는 데이터 선택
    ) - SIZE_OF_COLONY AS YEAR_DEV,    -- 최대 크기와 개별 크기의 차이를 계산
    ID                                -- 대장균 개체 ID
FROM ECOLI_DATA
ORDER BY YEAR, YEAR_DEV;              -- 연도와 편차 값을 기준으로 정렬

---

5. 코드분석

(1) SELECT 절

• YEAR(DIFFERENTIATION_DATE): 날짜 열에서 연도를 추출.
• 서브쿼리:WHERE YEAR(DIFFERENTIATION_DATE) = YEAR: 서브쿼리에서 메인 쿼리의 연도를 기준으로 최대값을 계산.
• MAX(SIZE_OF_COLONY): 연도별 최대 대장균 크기를 계산.
• MAX(SIZE_OF_COLONY) - SIZE_OF_COLONY:
• 최대값과 현재 행의 값을 비교하여 편차 계산.

(2) 서브쿼리

• 서브쿼리는 메인 쿼리의 각 행마다 실행되며, 동적으로 연도별 최대값을 계산한다.

(3) ORDER BY 절

• YEAR: 연도별로 결과를 오름차순 정렬.
• YEAR_DEV: 같은 연도 내에서 편차 값에 따라 정렬.