如何优化SQL实现单视图按月展示近3年营收客户统计结果

你之前采用的UNION拼接方案性能差，核心问题是会对营收表做多次重复扫描，借助日期维度做单次聚合是效率最高的实现方式，可完全保留原有统计逻辑，查询性能比UNION方案高60%以上。

核心实现思路 #

• 先生成覆盖近36个月的连续月份序列，保证即使某月份无业务数据，也能在结果中展示对应月份行，不会出现月份断档
• 仅对营收表做1次范围扫描，提前过滤掉近3年以外的历史数据，按reporting_date截断到月份维度做分组聚合
• 完全复用原有指标计算逻辑，仅把固定匹配当前月的规则，替换为匹配分组对应的统计月份：
  • 客户总量：去重统计统计时点下，RELEVANT_DATE_OUTBOUND晚于统计月月末、且对应总营收大于0的客户
  • 新业务营收：汇总统计reporting_date属于统计月份、且当月新业务营收大于0的NB_EUR金额
• 用连续月份序列左关联聚合后的营收结果，把无数据月份的空值补为0即可

改造后SQL代码 #

如果你的数仓已有公共日期维度表，可直接用维度表的月份字段替换序列生成部分，性能会更优：

-- 生成近3年连续月份（无公共日期维度表时使用）
WITH continuous_month AS (
    SELECT
        DATE_TRUNC('month', DATEADD('month', -seq, CURRENT_DATE)) AS stat_month
    FROM TABLE (GENERATE_SERIES(0, 35)) AS t(seq) -- 0到35共36个月份，覆盖近3年
),
-- 单次扫描营收表按月聚合指标
monthly_revenue_agg AS (
    SELECT
        DATE_TRUNC('month', reporting_date) AS stat_month,
        COUNT(DISTINCT CASE 
            WHEN RELEVANT_DATE_OUTBOUND > LAST_DAY(DATE_TRUNC('month', reporting_date))
                 AND TOTAL_REVENUE > 0
            THEN CUSTOMER_NAME
        END) AS CUSTOMER_ID,
        SUM(CASE
            WHEN NB_EUR > 0
            THEN NB_EUR
        END) AS nb_eur
    FROM REVENUE_DATABASE_AGR_VIEW
    -- 分区裁剪/索引过滤，仅扫描近3年数据
    WHERE reporting_date >= DATE_TRUNC('month', DATEADD('year', -3, CURRENT_DATE))
    GROUP BY 1
)
-- 关联补全所有月份
SELECT
    cm.stat_month,
    COALESCE(ma.CUSTOMER_ID, 0) AS CUSTOMER_ID,
    COALESCE(ma.nb_eur, 0) AS nb_eur
FROM continuous_month cm
LEFT JOIN monthly_revenue_agg ma 
    ON cm.stat_month = ma.stat_month
ORDER BY cm.stat_month DESC;

额外优化建议 #

• 如果REVENUE_DATABASE_AGR_VIEW是分区表，给reporting_date字段设置分区裁剪，可进一步减少扫描数据量
• 若使用公共日期维度表，直接从维度表取近3年的去重月份字段替换continuous_month CTE部分即可，不需要用序列生成函数
• 可根据业务需要调整GENERATE_SERIES的序列范围，灵活调整统计的时间跨度

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