PostgreSQL中针对曾接受干预的ID按分类条件统计行数的实现问题

核心逻辑拆解 #

你之前的写法错误根源是：误按outcome_category维度判断干预状态，实际上干预判断是id维度的属性，需要先识别所有曾接受干预的id，再基于这些id过滤原表统计。
正确执行步骤如下：

1. 提取所有曾接受干预的id集合：按id分组，只要该id下存在任意一行treatment=1，MAX(treatment)结果就为1，即可判定为目标id
2. 用上述id集合过滤原表所有记录
3. 排除outcome_category为NULL的记录后，按分类分组统计行数

方案1：仅返回计数大于0的分类（匹配你第一版期望结果） #

SELECT 
  outcome_category,
  COUNT(*) AS count
FROM g
WHERE id IN (
  -- 子查询提取所有曾接受干预的id
  SELECT id 
  FROM g 
  GROUP BY id 
  HAVING MAX(treatment) = 1
)
AND outcome_category IS NOT NULL
GROUP BY outcome_category
-- 按计数值降序排序，符合你的优化要求
ORDER BY count DESC;

执行结果和你给出的第一版期望完全一致：

方案2：返回全部分类（包含计数为0的metabolic，匹配你第二版可接受结果） #

如果需要展示所有存在的outcome_category分类，哪怕对应计数为0，使用左关联实现：

SELECT 
  oc.outcome_category,
  COUNT(g.id) AS count
FROM (
  -- 提取所有非空的结局分类
  SELECT DISTINCT outcome_category 
  FROM g 
  WHERE outcome_category IS NOT NULL
) oc
LEFT JOIN g 
  ON oc.outcome_category = g.outcome_category
  -- 仅关联曾接受干预的id对应的记录
  AND g.id IN (
    SELECT id 
    FROM g 
    GROUP BY id 
    HAVING MAX(treatment) = 1
  )
GROUP BY oc.outcome_category
ORDER BY count DESC;

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