如何用SQL基于辅助日期表平摊月度营销成本至当月每日

核心逻辑 #

将现有「月度成本聚合」的结果，与辅助日期表按月份维度关联，再用月度总成本除以当月天数，即可得到每日平摊金额。

可直接运行的Redshift查询 #

SELECT
    d.date AS event_date,
    ROUND(m.month_total / DAY(LAST_DAY(d.date)), 2) AS marketing_costs
FROM dates d
INNER JOIN (
    -- 你已有的月度成本聚合逻辑，直接作为子查询使用
    SELECT
        DATE_TRUNC('month', event_date)::date AS month,
        SUM(marketing_costs) AS month_total
    FROM PaL
    GROUP BY 1
) m
ON DATE_TRUNC('month', d.date)::date = m.month
ORDER BY d.date;

关键说明 #

• 关联逻辑：子查询按月份聚合PaL表的成本后，把dates表的每日日期也截断到当月第一天做匹配，即可让该月的所有自然日都拿到对应的月度总成本。
• 当月天数计算：Redshift中用LAST_DAY(日期)拿到当月最后一天的日期，再通过DAY()取该日期的「日」部分，即可得到当月的总天数（例如2020年1月最后一天为1月31日，计算得到当月共31天）。
• 精度处理：用ROUND(..., 2)保留两位小数，和预期结果的计算精度完全匹配，例如1月总成本2100除以31天得到67.74，3月总成本1100除以31天得到35.48。

补充：如果需要输出所有dates表覆盖的日期（即使当月没有营销成本），可将INNER JOIN改为LEFT JOIN，同时把计算逻辑调整为ROUND(COALESCE(m.month_total, 0) / DAY(LAST_DAY(d.date)), 2)，无成本的月份会自动输出每日平摊值为0。

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