Postgres中如何高效统计不同时间点的累计物品记录数量？

现有写法的性能问题 #

你当前用的LATERAL JOIN写法会对每个时间窗口都全表扫描一次item表，N个时间窗口就会扫N次表，数据量较大时性能会非常差。

推荐优化方案（核心用到Postgres特性） #

• generate_series 函数：灵活生成任意天数的时间序列，不用硬写VALUES列表
• 预聚合+窗口函数：仅扫描1次item表即可计算所有时间窗口的累计值，性能提升极明显
• 表达式索引：可进一步优化时间差计算的查询速度

实现代码 #

-- 先预计算每天的新增item数量，再累加得到各时间窗口的累计值
WITH daily_new_items AS (
    -- 计算每个item创建时间距今天的天数，按天分组统计新增数
    SELECT 
        (EXTRACT(EPOCH FROM CURRENT_TIMESTAMP - creation_time) / 86400)::int AS days_ago,
        COUNT(DISTINCT id) AS daily_count
    FROM item
    GROUP BY days_ago
),
-- 生成你需要的时间窗口天数序列，这里示例是过去7天，要30天就改成generate_series(0,29)
time_windows AS (
    SELECT d AS days_threshold
    FROM generate_series(0, 6) AS days(d)
)
SELECT 
    CONCAT('Days 0 - ', days_threshold + 1) AS time_window,
    -- 累加所有早于等于当前阈值天数的item数
    SUM(COALESCE(daily_count, 0)) OVER (ORDER BY days_threshold ASC) AS item_count
FROM time_windows
LEFT JOIN daily_new_items ON days_ago <= days_threshold
ORDER BY days_threshold;

可选性能优化（适合大数据量表） #

如果item表数据量很大，可以给创建时间加表达式索引，避免每次查询实时计算天数差：

CREATE INDEX idx_item_creation_days_diff ON item 
USING btree( (EXTRACT(EPOCH FROM CURRENT_TIMESTAMP - creation_time)::int / 86400) );

灵活调整统计范围 #

如果不需要从今天开始统计，只需要把代码中所有的CURRENT_TIMESTAMP替换成你指定的统计起始时间即可，也可以随意调整generate_series的起止参数来适配任意天数的统计需求。

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