PostgreSQL如何按id是否有treatment=1记录分组统计outcome=1行数

你可以通过窗口函数或者CTE先按ID维度标记是否曾接受处理，再统计符合要求的行数即可，以下是两种可行写法：

写法1：窗口函数实现（性能更优，仅需扫描一次表） #

SELECT 
  ever_treated AS "ever treated",
  COUNT(*) AS count
FROM (
  SELECT 
    outcome,
    -- 按ID分组取treatment最大值，存在1则标记为1，全0则标记为0
    MAX(treatment) OVER (PARTITION BY id) AS ever_treated
  FROM t
) t_with_marker
WHERE outcome = 1
GROUP BY ever_treated
ORDER BY ever_treated;

写法2：CTE预计算ID标记（逻辑更直观） #

WITH id_marker AS (
  -- 先统计每个ID是否曾接受处理
  SELECT 
    id,
    MAX(treatment) AS ever_treated
  FROM t
  GROUP BY id
)
SELECT 
  m.ever_treated AS "ever treated",
  COUNT(*) AS count
FROM t
INNER JOIN id_marker m ON t.id = m.id
WHERE t.outcome = 1
GROUP BY m.ever_treated
ORDER BY m.ever_treated;

原写法问题说明 #

你之前编写的SQL是按每行的treatment字段直接分组统计，没有先按ID维度完成「是否曾接受处理」的聚合标记，统计逻辑和需求不符，因此结果不符合预期。

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