[PGM] 按年度计算大肠杆菌大小偏差（SQL）

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

• 给定ECOLI_DATA表，其中包含大肠杆菌ID、父代ID、个体大小、分化日期和基因型。
• 编写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连接年度最大值，计算每个个体的大小偏差。