[PGM] 연도별 대장균 크기의 편차 구하기 (SQL)[PGM] Get the deviation of E. coli size by year (SQL)
📅
🚀
알고리즘 분류🔗
LEFT JOIN, GROUP BY, ORDER BY
🚀
문제 설명🔗
- 대장균의 ID, 부모 대장균의 ID, 대장균의 크기, 대장균의 변이 날짜, 대장균의 유전자형을 담은 테이블 ECOLI_DATA가 주어집니다.
- ECOLI_DATA 테이블에서 각 연도별 대장균 최대치와 비교하여 대장균 크기의 차를 구하는 SQL을 작성해야 합니다.
- 정렬 기준은 연도 오름차순, 편차 오름차순입니다.
| 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 |
🚀
풀이🔗
- 내부 서브쿼리부터 작성합니다.
- ECOLI_DATA 테이블에서 연도별 최대 대장균 크기를 구합니다.
- 연도별 최대 대장균 크기를 구하기 위해
YEAR(DIFFERENTIATION_DATE)로 연도를 추출하고,MAX(SIZE_OF_COLONY)로 최대 대장균 크기를 구합니다. GROUP BY YEAR(DIFFERENTIATION_DATE)로 연도별로 그룹핑합니다.
- 외부 쿼리에서 연도별 대장균 크기의 편차를 구합니다.
- ECOLI_DATA 테이블에서
YEAR(DIFFERENTIATION_DATE)로 연도를 추출하고,SIZE_OF_COLONY로 대장균 크기를 구합니다. - 내부 서브쿼리에서 구한 연도별 최대 대장균 크기를 외부 서브쿼리에서 사용하기 위해 JOIN으로 연결합니다.
YEAR(t1.DIFFERENTIATION_DATE) = t2.YEAR로 연도별로 매칭합니다.MAX_COLONY - SIZE_OF_COLONY로 대장균 크기의 편차를 구합니다.
- ECOLI_DATA 테이블에서
ORDER BY YEAR, YEAR_DEV로 정렬합니다.
🚀
코드 - SQL🔗
SELECT YEAR(t1.DIFFERENTIATION_DATE) as YEAR, (t2.MAX_COLONY - t1.SIZE_OF_COLONY ) as YEAR_DEV, t1.ID
FROM ECOLI_DATA as t1
JOIN (
SELECT YEAR(DIFFERENTIATION_DATE) as YEAR, MAX(SIZE_OF_COLONY) as MAX_COLONY
FROM ECOLI_DATA
GROUP BY YEAR(DIFFERENTIATION_DATE)
) t2 ON YEAR(t1.DIFFERENTIATION_DATE) = t2.YEAR
ORDER BY YEAR, YEAR_DEV🚀
핵심🔗
- 서브쿼리를 사용하여 연도별 최대 대장균 크기를 구하는 것.
- JOIN을 사용하여 연도별 최대 대장균 크기와 대장균 크기의 편차를 구하는 것.