如何用SQL正确计算动物饲料转化率(FCR)？

尝试编写SQL查询计算动物的饲料转化率(FCR)，但TotalFeedConsumed和FCR列返回值均不正确。已知计算公式为：

FCR = Total Feed Consumed/Total Weight Gained

尝试的错误代码：

SELECT 
  a.Birthdate,
  a.Tagno,  
  a.Animal,
  SUM(f.Amount) AS TotalFeedConsumed,      
  CASE WHEN (MAX(w.Weight) - MIN(w.Weight)) > 0 THEN SUM(f.Amount) / (MAX(w.Weight) - MIN(w.Weight))  ELSE NULL END AS FCR
FROM Animals a
LEFT JOIN Animalfeeding f ON a.Tagno = f.Tagno
LEFT JOIN AnimalMeasure w ON a.Tagno = w.Tagno
GROUP BY a.Birthdate, a.Tagno, a.Animal ORDER BY FCR

相关数据表 #

Animal表 #

AnimalMeasure表 #

Animalfeeding表 #

预期输出 #

直接将Animal表与Animalfeeding、AnimalMeasure表左连接会产生笛卡尔积：

• 比如Tagno=809的动物有3条体重记录、2条饲料记录，连接后会生成3×2=6条重复数据，导致SUM(f.Amount)计算为15×3=45（而非正确的15），最终FCR结果完全错误。

先分别对饲料消耗和体重数据进行聚合，再与动物表关联，避免笛卡尔积：

SELECT
    a.Tagno,
    a.Animal,
    a.Birthdate,
    COALESCE(f.TotalFeed, 0) AS `TotalFeedConsumed(kg)`,
    CASE
        WHEN w.TotalWeightGain > 0 THEN COALESCE(f.TotalFeed, 0) / w.TotalWeightGain
        ELSE 0
    END AS FCR
FROM Animals a
LEFT JOIN (
    -- 计算每个动物的总饲料消耗
    SELECT Tagno, SUM(Amount) AS TotalFeed
    FROM Animalfeeding
    GROUP BY Tagno
) f ON a.Tagno = f.Tagno
LEFT JOIN (
    -- 计算每个动物的总增重（最大体重-最小体重）
    SELECT Tagno, (MAX(Weight) - MIN(Weight)) AS TotalWeightGain
    FROM AnimalMeasure
    GROUP BY Tagno
) w ON a.Tagno = w.Tagno
ORDER BY FCR;

代码说明 #

1. 子查询聚合饲料数据：单独统计每个动物的总饲料消耗，避免连接时重复计算。
2. 子查询聚合体重数据：单独计算每个动物的总增重，确保数值准确。
3. COALESCE函数：将NULL值（无饲料/无体重记录的动物）转换为0，符合预期输出要求。
4. CASE逻辑：当总增重大于0时计算FCR，否则返回0，匹配预期结果。

内容的提问来源于stack exchange，提问作者Samal