如何编写单条SQL查询统计A、B营销活动各场景的用户数量

我们默认使用两张表存储对应活动的参与用户：

• campaign_a：存储活动A的参与用户，字段user_id为参与用户ID
• campaign_b：存储活动B的参与用户，字段user_id为参与用户ID

你可以直接使用下方SQL完成所有6项指标的统计：

SELECT
  COUNT(DISTINCT a.user_id) AS campaign_a_total,
  COUNT(DISTINCT b.user_id) AS campaign_b_total,
  COUNT(DISTINCT CASE WHEN b.user_id IS NULL THEN a.user_id END) AS only_campaign_a,
  COUNT(DISTINCT CASE WHEN a.user_id IS NULL THEN b.user_id END) AS only_campaign_b,
  COUNT(DISTINCT CASE WHEN a.user_id IS NOT NULL AND b.user_id IS NOT NULL THEN a.user_id END) AS both_campaign,
  COUNT(DISTINCT COALESCE(a.user_id, b.user_id)) AS any_campaign
FROM campaign_a a
FULL OUTER JOIN campaign_b b ON a.user_id = b.user_id;

返回结果的字段和需求的对应关系：

• campaign_a_total：参与过活动A的用户数
• campaign_b_total：参与过活动B的用户数
• only_campaign_a：仅参与过活动A的用户数
• only_campaign_b：仅参与过活动B的用户数
• both_campaign：同时参与两个活动的用户数
• any_campaign：参与过活动A或活动B的用户数

低版本MySQL兼容写法 #

如果使用的数据库不支持FULL OUTER JOIN（比如5.7及以下版本的MySQL），可以改用下面的写法，效果完全一致：

SELECT
  SUM(has_a) AS campaign_a_total,
  SUM(has_b) AS campaign_b_total,
  SUM(CASE WHEN has_a = 1 AND has_b = 0 THEN 1 ELSE 0 END) AS only_campaign_a,
  SUM(CASE WHEN has_a = 0 AND has_b = 1 THEN 1 ELSE 0 END) AS only_campaign_b,
  SUM(CASE WHEN has_a = 1 AND has_b = 1 THEN 1 ELSE 0 END) AS both_campaign,
  COUNT(*) AS any_campaign
FROM (
  SELECT
    user_id,
    MAX(is_a) AS has_a,
    MAX(is_b) AS has_b
  FROM (
    SELECT user_id, 1 AS is_a, 0 AS is_b FROM campaign_a
    UNION ALL
    SELECT user_id, 0 AS is_a, 1 AS is_b FROM campaign_b
  ) t
  GROUP BY user_id
) t2;

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