PostgreSQL中统计多列指定值出现次数并计算占比

我明白你的需求了——你想一次性完成fixtures表中match_count、one_plus_mc_per和two_plus_mc_per三列的计算与更新，避免多次查询带来的效率问题。结合你给出的示例逻辑（match_count为非空mc列数量，one_plus_mc_per是mc值≥1的列占比，two_plus_mc_per是mc值≥2的列占比），可以用一条UPDATE语句实现：

UPDATE fixtures
SET
  -- 计算当前记录中非空的mc_*列数量作为match_count
  match_count = (
    CASE WHEN mc_0 IS NOT NULL THEN 1 ELSE 0 END +
    CASE WHEN mc_1 IS NOT NULL THEN 1 ELSE 0 END +
    CASE WHEN mc_3 IS NOT NULL THEN 1 ELSE 0 END +
    CASE WHEN mc_4 IS NOT NULL THEN 1 ELSE 0 END
  ),
  -- 计算mc值≥1的列占match_count的百分比，取整
  one_plus_mc_per = ROUND(
    (
      CASE WHEN COALESCE(mc_0, 0) >= 1 THEN 1 ELSE 0 END +
      CASE WHEN COALESCE(mc_1, 0) >= 1 THEN 1 ELSE 0 END +
      CASE WHEN COALESCE(mc_3, 0) >= 1 THEN 1 ELSE 0 END +
      CASE WHEN COALESCE(mc_4, 0) >= 1 THEN 1 ELSE 0 END
    ) * 100.0 / NULLIF(
      CASE WHEN mc_0 IS NOT NULL THEN 1 ELSE 0 END +
      CASE WHEN mc_1 IS NOT NULL THEN 1 ELSE 0 END +
      CASE WHEN mc_3 IS NOT NULL THEN 1 ELSE 0 END +
      CASE WHEN mc_4 IS NOT NULL THEN 1 ELSE 0 END,
      0
    )
  ),
  -- 计算mc值≥2的列占match_count的百分比，取整
  two_plus_mc_per = ROUND(
    (
      CASE WHEN COALESCE(mc_0, 0) >= 2 THEN 1 ELSE 0 END +
      CASE WHEN COALESCE(mc_1, 0) >= 2 THEN 1 ELSE 0 END +
      CASE WHEN COALESCE(mc_3, 0) >= 2 THEN 1 ELSE 0 END +
      CASE WHEN COALESCE(mc_4, 0) >= 2 THEN 1 ELSE 0 END
    ) * 100.0 / NULLIF(
      CASE WHEN mc_0 IS NOT NULL THEN 1 ELSE 0 END +
      CASE WHEN mc_1 IS NOT NULL THEN 1 ELSE 0 END +
      CASE WHEN mc_3 IS NOT NULL THEN 1 ELSE 0 END +
      CASE WHEN mc_4 IS NOT NULL THEN 1 ELSE 0 END,
      0
    )
  );

关键逻辑说明： #

• CASE语句：逐个判断每个mc_*列是否符合条件（非空、≥1、≥2），符合则计1，否则计0，累加得到对应数量。
• COALESCE：处理null值，将null转为0，确保数值判断的准确性。
• NULLIF：避免当match_count为0时出现除以0的错误，此时百分比列会返回null。
• ROUND：将百分比结果取整，和你示例中的66（2/3≈66.67取整）保持一致。

如果只需要更新特定记录（比如id=182），只需在语句末尾添加WHERE id = 182;即可。

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