使用SQL JOIN基于两表公共过滤条件获取独立记录并生成聚合报表

你之前的写法直接关联两张原始表后再聚合，会因为同月份下两张表的行匹配生成笛卡尔积，导致求和、去重计数的结果不符合预期。正确的做法是先分别对两张表做单表预聚合，再关联聚合后的结果，SQL参考如下：

WITH member_agg AS (
    -- 预聚合会员表的月维度指标
    SELECT 
        month_type,
        month,
        engaged,
        COUNT(DISTINCT member_id) AS 1st_Table_Member_count
    FROM MemberTable
    GROUP BY month_type, month, engaged
),
payment_agg AS (
    -- 预聚合支付表的月维度指标
    SELECT 
        month,
        SUM(amount) AS amount_sum,
        COUNT(DISTINCT member_id) AS 2nd_Table_Member_count
    FROM PaymentTable
    GROUP BY month
)
-- 关联两个预聚合结果得到最终报表
SELECT 
    COALESCE(a.month, b.month) AS month,
    COALESCE(a.month_type, '无对应数据') AS month_type,
    a.engaged,
    COALESCE(b.amount_sum, 0) AS amount_sum,
    COALESCE(a.1st_Table_Member_count, 0) AS 1st_Table_Member_count,
    COALESCE(b.2nd_Table_Member_count, 0) AS 2nd_Table_Member_count
FROM member_agg a
FULL OUTER JOIN payment_agg b
ON a.month = b.month
ORDER BY month;

如果所用的SQL引擎不支持CTE语法，将两个预聚合逻辑替换为子查询即可实现相同效果。

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