如何基于日期与campaign关联两表并统计花费、注册数及获客成本？

你的问题出在直接关联原始表导致了笛卡尔积重复计算——比如ad_spend里同一个日期+广告组有多条花费记录，和signups_from_ad的注册记录关联后，每条注册会匹配多条花费记录，最终sum出来的花费会被放大N倍（N是对应日期广告组的花费记录数）。

正确的做法是先分别对两个表按date和campaign做聚合统计，再将聚合后的结果关联：

步骤1：分别聚合两个表 #

• 对ad_spend统计每个日期+广告组的总花费：

SELECT date, campaign, SUM(spend) AS total_spend
FROM ad_spend
GROUP BY date, campaign

• 对signups_from_ad统计每个日期+广告组的注册人数：

SELECT date, campaign, COUNT(customer_id) AS signup_count
FROM signups_from_ad
GROUP BY date, campaign

步骤2：关联聚合结果并计算单客成本 #

用关联把两个聚合表拼起来，同时处理可能的“只有花费无注册”或“只有注册无花费”的情况：

SELECT 
    COALESCE(s.date, a.date) AS date,
    COALESCE(s.campaign, a.campaign) AS campaign,
    COALESCE(s.signup_count, 0) AS signup_count,
    COALESCE(a.total_spend, 0) AS total_spend,
    -- 避免除以0的情况，没有注册时单客成本设为NULL
    CASE 
        WHEN COALESCE(s.signup_count, 0) = 0 THEN NULL 
        ELSE a.total_spend / s.signup_count 
    END AS cost_per_signup
FROM (
    SELECT date, campaign, COUNT(customer_id) AS signup_count
    FROM signups_from_ad
    GROUP BY date, campaign
) s
-- 用FULL OUTER JOIN保留所有日期+广告组的记录，若只需要同时有数据的记录改成INNER JOIN
FULL OUTER JOIN (
    SELECT date, campaign, SUM(spend) AS total_spend
    FROM ad_spend
    GROUP BY date, campaign
) a ON s.date = a.date AND s.campaign = a.campaign
ORDER BY date, campaign;

为什么之前的方法不行？ #

你之前直接关联原始表，比如1/1/2021的广告组C，ad_spend有2条500的记录，signups_from_ad有3条注册记录，关联后会生成2×3=6条记录：

• count(customer_id)会得到6（加distinct后得到3，这是对的）
• sum(a.spend)会得到500×6=3000，但实际总花费是500×2=1000，这就是重复计算的根源。

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