Postgres如何根据总预算与起止日期生成每日预算及日期序列

嘿，这个需求在Postgres里处理起来挺顺手的，咱们一步步来解决：

要实现这个需求，我们需要完成三件核心事情：

1. 生成指定日期范围内的所有日期序列
2. 计算每个活动的总天数和每日平摊预算
3. 将日期序列与活动数据关联，筛选出每个活动有效期内的每日预算记录

WITH date_series AS (
    -- 生成2018-04-01至2018-06-30的所有日期
    SELECT generate_series(
        '2018-04-01'::DATE,
        '2018-06-30'::DATE,
        '1 day'::INTERVAL
    ) AS date_day
),
campaign_calculations AS (
    -- 计算每个活动的总天数和每日预算
    SELECT
        campaign,
        budget,
        start_date,
        end_date,
        -- 注意：起止日期都算活动天数，所以要+1修正间隔计算
        (end_date - start_date + 1) AS total_campaign_days,
        -- 转成NUMERIC避免整数除法丢失精度，保留两位小数让结果更直观
        ROUND(budget::NUMERIC / (end_date - start_date + 1), 2) AS daily_budget
    FROM campaign
)
-- 关联日期序列和活动数据，只保留活动有效期内的记录
SELECT
    ds.date_day::DATE,
    cc.campaign,
    cc.daily_budget
FROM date_series ds
INNER JOIN campaign_calculations cc
    ON ds.date_day BETWEEN cc.start_date AND cc.end_date
ORDER BY ds.date_day, cc.campaign;

• date_series CTE：Postgres的generate_series是生成序列的神器，这里用它直接生成我们需要的所有日期，省去了手动维护日期表的麻烦，非常高效。
• campaign_calculations CTE：
  • 计算活动总天数时，end_date - start_date得到的是两个日期之间的间隔天数（比如4月1日到4月3日是2天间隔），但实际活动覆盖3天，所以必须加1才是正确的总活动天数。
  • 把budget转成NUMERIC是为了避免整数除法的精度丢失问题（比如25400除以91，如果用整数除法会得到279，转成NUMERIC后会得到精确的279.12），再用ROUND保留两位小数让结果更友好。
• 最终关联查询：用INNER JOIN只保留那些日期在活动起止范围内的记录，最后按日期和活动名称排序，得到结构清晰的每日预算表。

如果你希望输出所有日期+所有活动的组合，即使当天不在活动期内也显示（预算设为0），可以用下面的变体：

WITH date_series AS (
    SELECT generate_series(
        '2018-04-01'::DATE,
        '2018-06-30'::DATE,
        '1 day'::INTERVAL
    ) AS date_day
),
campaign_calculations AS (
    SELECT
        campaign,
        budget,
        start_date,
        end_date,
        (end_date - start_date + 1) AS total_campaign_days,
        ROUND(budget::NUMERIC / (end_date - start_date + 1), 2) AS daily_budget
    FROM campaign
)
SELECT
    ds.date_day::DATE,
    c.campaign,
    -- 非活动日预算统一设为0
    COALESCE(cc.daily_budget, 0) AS daily_budget
FROM date_series ds
-- 先生成所有日期和活动的笛卡尔积组合
CROSS JOIN campaign c
-- 左关联匹配活动期内的预算数据
LEFT JOIN campaign_calculations cc
    ON c.campaign = cc.campaign
    AND ds.date_day BETWEEN cc.start_date AND cc.end_date
ORDER BY ds.date_day, c.campaign;

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