PostgreSQL如何在单查询中实现distinct求和并生成统计结果

你可以通过**CASE表达式结合COUNT(DISTINCT)**实现各类型独立用户数的统计，同时保留各类型的总次数统计，最终直接将结果插入到event_summary表中。以下是完整的SQL语句：

INSERT INTO event_summary (
  created_at,
  product_id,
  ad_id,
  search,
  unique_search,
  sidebar,
  unique_sidebar,
  pdp,
  unique_pdp
)
SELECT
  -- 用时间区间的起始时间作为汇总记录的created_at，也可通过date_trunc统一到分钟级别
  '2023-09-01 14:17:00'::timestamp AS created_at,
  product_id,
  ad_id,
  -- 统计type=0的总触发次数
  SUM(CASE WHEN type = 0 THEN 1 ELSE 0 END) AS search,
  -- 统计type=0的独立用户数（不符合条件时返回null，COUNT(DISTINCT)会自动忽略null）
  COUNT(DISTINCT CASE WHEN type = 0 THEN user_id END) AS unique_search,
  -- type=1的总触发次数
  SUM(CASE WHEN type = 1 THEN 1 ELSE 0 END) AS sidebar,
  -- type=1的独立用户数
  COUNT(DISTINCT CASE WHEN type = 1 THEN user_id END) AS unique_sidebar,
  -- type=2的总触发次数
  SUM(CASE WHEN type = 2 THEN 1 ELSE 0 END) AS pdp,
  -- type=2的独立用户数
  COUNT(DISTINCT CASE WHEN type = 2 THEN user_id END) AS unique_pdp
FROM page_event
WHERE
  -- 注意：page_event表的时间字段是viewed_at，原查询存在字段名笔误，需修正
  viewed_at >= '2023-09-01 14:17:00' 
  AND viewed_at < '2023-09-01 14:17:59'
-- 若需按product_id+ad_id联合分组（同一商品不同广告分开统计），保留ad_id；否则移除
GROUP BY product_id, ad_id;

关键细节说明 #

• 独立用户数统计逻辑：COUNT(DISTINCT CASE WHEN type = N THEN user_id END) 仅提取type=N记录的user_id并去重计数，不符合条件的记录返回null，不会被计入统计。
• 字段笔误修正：原查询中误用created_at作为过滤条件，但源表的时间字段是viewed_at，必须修正否则会导致查询无结果或报错。
• ad_id分组处理：因汇总表包含ad_id字段，若需区分同一商品下的不同广告数据，必须在GROUP BY中加入ad_id；若无需区分，需对ad_id使用聚合函数（如MAX(ad_id)）或按业务逻辑处理后才能去掉分组。
• 汇总时间设置：示例直接使用区间起始时间作为汇总记录的created_at，也可通过DATE_TRUNC('minute', viewed_at)统一取分钟级时间，确保同一时间窗口的汇总记录时间一致。

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