PostgreSQL：聚合随机更新的广告预算数据生成锯齿图的问题

问题背景 #

现有广告活动预算追踪表budget_usages，字段包括：

• advertiser_id（广告主ID）
• budget_scope_id（广告活动ID）
• budget：广告活动单日预算（支持当日变更）
• percentage：当日已花费预算百分比（00:00-23:59，午夜重置为0）
• usage_updated_timestamp：记录更新时间戳

需求是按advertiser_id聚合，生成成本随时间逐步上升、午夜归零的锯齿图。但直接按advertiser_id和usage_updated_timestamp分组求和会出现当日成本下降的问题——因为同一时间戳下可能只有部分广告活动的更新记录，未更新的活动未被计入总和，导致总和降低。且无法通过date_trunc('hour', timestamp)统一时间粒度，因为更新是随机的。

核心思路 #

针对每个时间点（按分钟粒度），获取每个广告活动在该时间点之前的最新有效记录（仅限当日，因百分比午夜重置），再汇总总成本，确保每个时间点都包含所有活动的最新状态。

解决方案（PostgreSQL） #

WITH time_series AS (
    -- 生成覆盖数据时间范围的每分钟时间序列
    SELECT generate_series(
        (SELECT MIN(date_trunc('minute', usage_updated_timestamp)) FROM budget_usages),
        (SELECT MAX(date_trunc('minute', usage_updated_timestamp)) FROM budget_usages),
        '1 minute'::interval
    ) AS minute_timestamp
),
latest_activity_records AS (
    -- 关联所有广告主-活动组合，获取每个分钟点的最新记录
    SELECT
        ts.minute_timestamp,
        bs.advertiser_id,
        bs.budget_scope_id,
        bu.budget,
        bu.percentage
    FROM time_series ts
    CROSS JOIN (SELECT DISTINCT advertiser_id, budget_scope_id FROM budget_usages) bs
    LEFT JOIN LATERAL (
        SELECT budget, percentage
        FROM budget_usages bu
        WHERE bu.advertiser_id = bs.advertiser_id
          AND bu.budget_scope_id = bs.budget_scope_id
          AND bu.usage_updated_timestamp <= ts.minute_timestamp
          -- 仅取当日记录，避免跨天的旧百分比干扰
          AND date(bu.usage_updated_timestamp) = date(ts.minute_timestamp)
        ORDER BY bu.usage_updated_timestamp DESC
        LIMIT 1
    ) bu ON true
),
daily_aggregated_costs AS (
    -- 汇总每个时间点的总成本，无记录的活动按0计算
    SELECT
        advertiser_id,
        minute_timestamp,
        SUM(COALESCE(budget * percentage / 100, 0)) AS cost
    FROM latest_activity_records
    GROUP BY advertiser_id, minute_timestamp
)
-- 可选：过滤连续重复的成本值，减少绘图数据量
SELECT
    advertiser_id,
    minute_timestamp,
    cost
FROM daily_aggregated_costs dc
WHERE cost != COALESCE(
    (SELECT cost FROM daily_aggregated_costs WHERE advertiser_id = dc.advertiser_id AND minute_timestamp < dc.minute_timestamp ORDER BY minute_timestamp DESC LIMIT 1),
    -1
)
ORDER BY advertiser_id, minute_timestamp;

代码解释 #

1. time_series CTE：生成覆盖所有数据的每分钟时间戳，确保每个时间点都被纳入计算，避免遗漏。
2. latest_activity_records CTE：
   • 通过CROSS JOIN枚举所有广告主-活动组合，确保每个活动在每个时间点都有状态记录。
   • 用LATERAL JOIN查询每个活动在当前时间点之前的最新记录，同时通过date过滤确保只取当日数据，避免跨天的旧百分比干扰。
3. daily_aggregated_costs CTE：用COALESCE将无记录的活动成本设为0（对应午夜重置后的初始状态），再按广告主和时间点汇总总成本。
4. 最后一步可选过滤：移除连续相同的成本值，减少绘图所需的数据点，同时不影响趋势展示。

预期结果 #

以示例数据为例，会得到符合预期的逐步上升的成本曲线：

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