如何利用SQL子查询在广告系统中计算每个素材的转化率？-SQL-PHP中文网

转化率计算必须用 left join + coalesce/nullif 对齐维度并防除零，因曝光与转化分表存储，inner join 会丢数据，count() 在空组返回0导致除零错误，子查询嵌套引发 o(n²) 性能灾难。

如何利用sql子查询在广告系统中计算每个素材的转化率？

直接说结论：转化率 = 转化数 / 曝光数，但必须用 LEFT JOIN 或 COALESCE 处理曝光为 0 的情况，否则子查询除零会报错或返回 NULL。

为什么不能直接写 `SELECT ad_id, COUNT(conversion)/COUNT(impression)`？

广告系统里曝光和转化通常分表存储（ad_impressions 和 ad_conversions），且一条素材可能有曝光但无转化——此时 COUNT(conversion) 为 0，但更危险的是：如果某素材完全没进曝光表，主表缺失会导致整行被丢弃。

用 INNER JOIN 会漏掉“有曝光无转化”的素材
用 LEFT JOIN 后不处理 NULL，COUNT() 在空组里返回 0，但除法中分母为 0 会触发数据库报错（如 PostgreSQL 报 division by zero，MySQL 默认静默转为 NULL，行为不一致）
子查询若单独算分母，没加 WHERE 对齐时间窗口或广告位，结果会偏高

正确写法：用关联子查询 + `COALESCE` 防除零

核心是让每个素材的曝光数、转化数都对齐到同一维度（比如按 ad_id），且分母兜底为 1（避免除零）或明确标为 NULL（表示不可计算）。

Clippah

一款面向视频内容处理与创作的AI工具，通过自动化方式辅助用户处理视频素材并制作更适合传播的内容。

下载

SELECT 
  a.ad_id,
  COALESCE(c.conv_cnt, 0) * 1.0 / NULLIF(i.imp_cnt, 0) AS cvr
FROM (SELECT DISTINCT ad_id FROM ad_impressions WHERE dt = '2024-06-01') a
LEFT JOIN (
  SELECT ad_id, COUNT(*) AS imp_cnt 
  FROM ad_impressions 
  WHERE dt = '2024-06-01' 
  GROUP BY ad_id
) i ON a.ad_id = i.ad_id
LEFT JOIN (
  SELECT ad_id, COUNT(*) AS conv_cnt 
  FROM ad_conversions 
  WHERE dt = '2024-06-01' AND status = 'success'
  GROUP BY ad_id
) c ON a.ad_id = c.ad_id;

NULLIF(i.imp_cnt, 0) 是关键：分母为 0 时返回 NULL，整个表达式结果就是 NULL，安全
两个子查询都带 WHERE dt = '2024-06-01'，确保时间口径一致；转化表额外加 status = 'success' 过滤无效记录
用 * 1.0 强制转浮点，避免整数除法截断（如 MySQL 中 1/2 = 0）

性能陷阱：别在 WHERE 里嵌套子查询算分母

下面这种写法看着简洁，实际极慢：

SELECT 
  ad_id,
  (SELECT COUNT(*) FROM ad_conversions c WHERE c.ad_id = i.ad_id AND c.dt = i.dt) * 1.0 / 
  NULLIF((SELECT COUNT(*) FROM ad_impressions ii WHERE ii.ad_id = i.ad_id AND ii.dt = i.dt), 0)
FROM ad_impressions i
WHERE i.dt = '2024-06-01'
GROUP BY ad_id;