Postgres中按matrix_id分组计算矩阵迹的SQL实现方法

你原有写法无法执行的核心问题是：子查询未关联外层分组的matrix_id，且分组场景下直接引用非分组、非聚合字段时，数据库无法匹配到对应row_id的行值。

针对固定10×10的矩阵结构，优先用条件聚合实现，仅需单次扫描表，性能最优：

SELECT
  matrix_id,
  SUM(
    CASE row_id
      WHEN 1 THEN col1
      WHEN 2 THEN col2
      WHEN 3 THEN col3
      WHEN 4 THEN col4
      WHEN 5 THEN col5
      WHEN 6 THEN col6
      WHEN 7 THEN col7
      WHEN 8 THEN col8
      WHEN 9 THEN col9
      WHEN 10 THEN col10
      ELSE 0
    END
  ) AS matrix_trace
FROM matrix
GROUP BY matrix_id;

实现逻辑非常直接：逐行判断当前行的row_id，只取该行对应主对角线位置的列值，其余位置值记为0，按matrix_id分组后直接求和，结果就是对应矩阵的迹。

如果要改用关联子查询写法（不推荐，表数据量大时性能会明显下降，需要多次回表查询），必须给每个子查询加上matrix_id的关联条件，参考如下：

SELECT
  m.matrix_id,
  COALESCE((SELECT col1 FROM matrix WHERE matrix_id = m.matrix_id AND row_id = 1), 0) +
  COALESCE((SELECT col2 FROM matrix WHERE matrix_id = m.matrix_id AND row_id = 2), 0) +
  COALESCE((SELECT col3 FROM matrix WHERE matrix_id = m.matrix_id AND row_id = 3), 0) +
  COALESCE((SELECT col4 FROM matrix WHERE matrix_id = m.matrix_id AND row_id = 4), 0) +
  COALESCE((SELECT col5 FROM matrix WHERE matrix_id = m.matrix_id AND row_id = 5), 0) +
  COALESCE((SELECT col6 FROM matrix WHERE matrix_id = m.matrix_id AND row_id = 6), 0) +
  COALESCE((SELECT col7 FROM matrix WHERE matrix_id = m.matrix_id AND row_id = 7), 0) +
  COALESCE((SELECT col8 FROM matrix WHERE matrix_id = m.matrix_id AND row_id = 8), 0) +
  COALESCE((SELECT col9 FROM matrix WHERE matrix_id = m.matrix_id AND row_id = 9), 0) +
  COALESCE((SELECT col10 FROM matrix WHERE matrix_id = m.matrix_id AND row_id = 10), 0) AS matrix_trace
FROM matrix m
GROUP BY m.matrix_id;

这里给每个子查询加了COALESCE处理空值：如果某个矩阵存在行缺失（比如没有row_id=3的记录），对应位置会按0计算，避免整个求和结果因为NULL值变成NULL。条件聚合的写法天然兼容行缺失场景，不需要额外做空值处理，鲁棒性更强。

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