[PGM] 年ごとの大腸菌サイズの偏差を求める (SQL)

https://school.programmers.co.kr/learn/courses/30/lessons/299310↗

• 大腸菌のID、親のID、サイズ、分化した日付、遺伝子型を持つECOLI_DATAテーブルが与えられます。
• 各年の最大サイズと比較して、それぞれの大腸菌のサイズとの差を求めるSQLを書きます。
• 年の昇順、偏差の昇順で並べ替えます。

1. 内側のサブクエリから作成します。
   • ECOLI_DATAテーブルから、年ごとの大腸菌の最大サイズを求めます。
   • YEAR(DIFFERENTIATION_DATE)で年を取り出し、MAX(SIZE_OF_COLONY)で最大サイズを求めます。
   • GROUP BY YEAR(DIFFERENTIATION_DATE)で、年ごとにグループ化します。
2. 外側のクエリで、年ごとのサイズの偏差を求めます。
   • ECOLI_DATAのYEAR(DIFFERENTIATION_DATE)で年を取り出し、SIZE_OF_COLONYでサイズを取得します。
   • 内側のサブクエリで求めた年ごとの最大サイズを、外側のクエリから使えるようにJOINします。
   • YEAR(t1.DIFFERENTIATION_DATE) = t2.YEARで、年を対応付けます。
   • MAX_COLONY - SIZE_OF_COLONYで、サイズの偏差を求めます。
3. ORDER BY YEAR, YEAR_DEVで並べ替えます。

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を使い、各年の最大サイズから、それぞれのサイズの偏差を求めること。