Postgres中如何按分组生成跨时间补全的累计计数以适配Metabase堆叠面积图

实现思路 #

1. 先生成所有日期与所有分组的笛卡尔积，得到每个日期对应全部分组的基础行，避免缺失条目
2. 左关联原累计计数表，对每个分组按日期排序，取当前及之前最新的非空累计值，无历史值则填0

完整SQL实现 #

假设你的原表名为group_cumulative_data，字段分别为date（日期）、group（分组）、cumulative_count（累计计数），实现代码如下：

WITH all_dates AS (
    -- 提取原表中所有出现过的日期，也可自定义日期范围
    SELECT DISTINCT date FROM group_cumulative_data
),
all_groups AS (
    -- 提取所有分组
    SELECT DISTINCT "group" FROM group_cumulative_data
),
full_date_group AS (
    -- 笛卡尔积生成每个日期对应所有分组的全量行
    SELECT ad.date, ag."group"
    FROM all_dates ad
    CROSS JOIN all_groups ag
)
SELECT 
    fdg.date,
    fdg."group",
    -- 取该分组当前及之前最新的累计值，无历史值则填充0
    COALESCE(
        LAST_VALUE(gcd.cumulative_count) OVER (
            PARTITION BY fdg."group"
            ORDER BY fdg.date
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ),
    0) AS cumulative_count
FROM full_date_group fdg
LEFT JOIN group_cumulative_data gcd
    ON fdg.date = gcd.date AND fdg."group" = gcd."group"
ORDER BY fdg.date, fdg."group";

注意事项 #

• group是Postgres保留关键字，作为字段名使用时需要用双引号包裹
• 若原表日期存储为字符串格式（如1/1/2020），需要先转换为日期类型避免排序错误：TO_DATE(date, 'MM/DD/YYYY')
• 若需要覆盖原表没有的中间日期，可将all_dates逻辑替换为日期生成序列：

all_dates AS (
    SELECT generate_series(
        (SELECT MIN(TO_DATE(date, 'MM/DD/YYYY')) FROM group_cumulative_data),
        (SELECT MAX(TO_DATE(date, 'MM/DD/YYYY')) FROM group_cumulative_data),
        INTERVAL '1 day'
    )::DATE AS date
)

以上代码执行后输出的结果完全符合要求的格式，可直接导入Metabase渲染堆叠面积图，不会出现数据断层问题。

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