如何在单SQL查询中计算两类蛋白质的平均长度并合并结果

方法一：条件聚合（推荐，单表扫描效率更高） #

通过CASE语句结合AVG()聚合函数，一次扫描蛋白质表即可同时计算两类平均值，结果以列的形式展示：

SELECT
  -- 计算存在于mrna_pieces表的蛋白质平均长度
  AVG(CASE 
        WHEN EXISTS (SELECT 1 FROM mrna_pieces mp WHERE mp.protein_id = p.protein_id) 
        THEN p.length 
      END) AS avg_length_in_mrna,
  -- 计算不存在于mrna_pieces表的蛋白质平均长度
  AVG(CASE 
        WHEN NOT EXISTS (SELECT 1 FROM mrna_pieces mp WHERE mp.protein_id = p.protein_id) 
        THEN p.length 
      END) AS avg_length_not_in_mrna
FROM proteins p;

方法二：UNION ALL合并独立查询 #

如果更习惯用独立查询的逻辑，可通过UNION ALL将两个统计结果合并为两行展示：

-- 统计存在于mrna_pieces的平均长度
SELECT '存在于mrna_pieces' AS category, AVG(p.length) AS avg_length
FROM proteins p
WHERE EXISTS (SELECT 1 FROM mrna_pieces mp WHERE mp.protein_id = p.protein_id)

UNION ALL

-- 统计不存在于mrna_pieces的平均长度
SELECT '不存在于mrna_pieces' AS category, AVG(p.length) AS avg_length
FROM proteins p
WHERE NOT EXISTS (SELECT 1 FROM mrna_pieces mp WHERE mp.protein_id = p.protein_id);

常见错误原因 #

你遇到的syntax error at or near "WHERE"错误，大概率是因为直接拼接两个查询时未使用UNION ALL关键字，或者在单个查询中重复使用WHERE子句。比如错误写法示例：

-- 错误：两个查询直接拼接，无UNION ALL连接
SELECT AVG(length) FROM proteins WHERE EXISTS (...)
SELECT AVG(length) FROM proteins WHERE NOT EXISTS (...)

-- 错误：单个查询中重复使用WHERE
SELECT AVG(length) FROM proteins 
WHERE EXISTS (...)
WHERE NOT EXISTS (...)

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