PostgreSQL CTE查询求助：避免非预期分组/重复结果

原查询出现重复的核心原因是：虽然各个CTE都按payor、name_policy、program三个字段分组，但后续关联（JOIN）时仅用name_policy作为匹配条件，忽略了payor和program，导致同一name_policy下不同payor/program的分组被错误关联，从而产生重复行。此外，多次扫描同一张表也会降低查询效率。

直接使用条件聚合，一次分组完成所有统计需求，避免多表关联的问题：

SELECT
  payor,
  name_policy,
  program,
  -- 统计Show类的去重event_id数量
  COUNT(DISTINCT CASE WHEN event_status LIKE 'Show%' THEN event_id END) AS sevents,
  -- 统计Show类的item_qty总和
  SUM(CASE WHEN event_status LIKE 'Show%' THEN item_qty ELSE 0 END) AS qty_show,
  -- 统计No Show类的去重event_id数量
  COUNT(DISTINCT CASE WHEN event_status LIKE 'No Show%' THEN event_id END) AS nsevents,
  -- 统计No Show类的item_qty总和
  SUM(CASE WHEN event_status LIKE 'No Show%' THEN item_qty ELSE 0 END) AS qty_no_show
FROM cart_item_funder_policy_worker
WHERE event_date BETWEEN '2022-04-01' AND '2022-09-30' -- 注意：原表结构定义为event_date，修正字段名
GROUP BY payor, name_policy, program
ORDER BY payor, name_policy, program;

1. 条件聚合：通过CASE语句筛选目标状态的记录，在同一分组中完成多维度统计，无需拆分多个CTE，提升查询效率。
2. 避免重复：严格按payor、name_policy、program分组，确保每个分组唯一，从根源上避免关联错误导致的重复行。
3. 空值处理：SUM中用ELSE 0保证无对应状态记录时显示0而非NULL；若需要COUNT(DISTINCT)结果也显示0，可嵌套COALESCE，例如：COALESCE(COUNT(DISTINCT CASE ...), 0) AS sevents。
4. 字段修正：原表结构定义的时间字段是event_date，已同步修正WHERE条件中的字段名，避免查询报错。

内容的提问来源于stack exchange，提问作者Rahul Dev vasisht