如何用SQL从deals、churns、upsells三表聚合生成目标营收表

前提假设（基于常见业务表结构） #

假设三个业务表的核心字段如下：

• deals（新客户成交表）：deal_date（成交日期）、revenue（成交营收）
• churns（客户流失表）：churn_date（流失日期）、lost_revenue（损失营收）
• upsells（客户增购表）：upsell_date（增购日期）、upsell_revenue（增购营收）

核心思路 #

1. 分别对三个表按日期聚合对应营收数据
2. 整合所有涉及的日期（避免遗漏某一天只有单一类型数据的情况）
3. 将三个聚合结果按日期左连接，空值填充为0

SQL语句实现 #

-- 生成所有涉及的日期集合
WITH all_dates AS (
    SELECT deal_date AS report_date FROM deals
    UNION
    SELECT churn_date AS report_date FROM churns
    UNION
    SELECT upsell_date AS report_date FROM upsells
),
-- 聚合新客户成交营收
deal_revenue AS (
    SELECT
        deal_date AS report_date,
        SUM(revenue) AS new_customer_revenue
    FROM deals
    GROUP BY deal_date
),
-- 聚合客户流失损失营收
churn_loss AS (
    SELECT
        churn_date AS report_date,
        SUM(lost_revenue) AS churn_loss_revenue
    FROM churns
    GROUP BY churn_date
),
-- 聚合增购营收
upsell_revenue AS (
    SELECT
        upsell_date AS report_date,
        SUM(upsell_revenue) AS upsell_revenue
    FROM upsells
    GROUP BY upsell_date
)
-- 关联所有数据，生成目标报表
SELECT
    ad.report_date,
    COALESCE(dr.new_customer_revenue, 0) AS 新客户成交营收,
    COALESCE(cl.churn_loss_revenue, 0) AS 客户流失损失营收,
    COALESCE(ur.upsell_revenue, 0) AS 增购营收
FROM all_dates ad
LEFT JOIN deal_revenue dr ON ad.report_date = dr.report_date
LEFT JOIN churn_loss cl ON ad.report_date = cl.report_date
LEFT JOIN upsell_revenue ur ON ad.report_date = ur.report_date
ORDER BY ad.report_date;

说明 #

• 使用WITH子句拆分逻辑，提升代码可读性
• UNION用于去重生成所有日期，确保没有重复日期
• COALESCE函数将NULL值替换为0，保证报表数值完整性
• 最终结果按日期排序，符合常规报表展示习惯

如果你的表结构字段名或业务逻辑有差异，只需调整对应字段名和聚合规则即可。

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