PostgreSQL按小时粒度生成多市场累计历史数据的方案咨询

最优批量计算方案（无循环，性能优先） #

不需要循环遍历，直接通过单次批量SQL即可完成全量指标计算写入，避免逐次循环的查询开销，适合所有数据量场景，逻辑如下：
前提说明：假设使用PostgreSQL数据库，统计结果写入的历史表为market_org_hourly_stats，包含字段stats_hour（统计整点时间）、market、total_unique_orgs（全量累计唯一机构数）、last_30d_unique_orgs（近30天累计唯一机构数）。

INSERT INTO market_org_hourly_stats (stats_hour, market, total_unique_orgs, last_30d_unique_orgs)
WITH 
-- 生成从最早交易整点到当前时间的小时时间序列
hour_dim AS (
    SELECT generate_series(
        DATE_TRUNC('hour', MIN(transaction_date_time)),
        DATE_TRUNC('hour', CURRENT_TIMESTAMP),
        INTERVAL '1 hour'
    ) AS stats_hour
    FROM customer_transactions
),
-- 生成所有待统计的市场列表
market_dim AS (
    SELECT DISTINCT market FROM customer_transactions
),
-- 生成全量 小时-市场 统计维度组合，避免漏统计无交易的时间/市场
full_dim AS (
    SELECT h.stats_hour, m.market
    FROM hour_dim h
    CROSS JOIN market_dim m
)
-- 关联交易数据计算指标
SELECT
    f.stats_hour,
    f.market,
    COUNT(DISTINCT ct.organization_id) AS total_unique_orgs,
    COUNT(DISTINCT CASE WHEN ct.transaction_date_time >= f.stats_hour - INTERVAL '30 days' THEN ct.organization_id END) AS last_30d_unique_orgs
FROM full_dim f
LEFT JOIN customer_transactions ct
    ON ct.market = f.market
    AND ct.transaction_date_time <= f.stats_hour
-- 避免重复写入历史表的过滤条件，不需要可以删除
WHERE NOT EXISTS (
    SELECT 1 FROM market_org_hourly_stats s 
    WHERE s.stats_hour = f.stats_hour AND s.market = f.market
)
GROUP BY f.stats_hour, f.market
ORDER BY f.stats_hour, f.market;

方案优势 #

• 全程单SQL批量计算，无PL/pgSQL循环开销，数据量越大性能优势越明显
• 自动覆盖所有时间和市场组合，不会漏算无交易的时间区间/市场
• 可通过拆分时间范围的方式优化超大表计算，比如每次只计算一个月的区间，避免单次查询内存占用过高

循环实现方案（适配你的原始思路） #

如果需要按你原计划的循环逻辑实现，PL/pgSQL示例如下，仅适合小数据量或调试场景使用：

DO $$
DECLARE
    current_calc_hour TIMESTAMP;
    hour_cur CURSOR FOR
        SELECT generate_series(
            DATE_TRUNC('hour', MIN(transaction_date_time)),
            DATE_TRUNC('hour', CURRENT_TIMESTAMP),
            INTERVAL '1 hour'
        ) AS stats_hour
        FROM customer_transactions;
BEGIN
    OPEN hour_cur;
    LOOP
        FETCH hour_cur INTO current_calc_hour;
        EXIT WHEN NOT FOUND;
        
        INSERT INTO market_org_hourly_stats (stats_hour, market, total_unique_orgs, last_30d_unique_orgs)
        SELECT
            current_calc_hour,
            market,
            COUNT(DISTINCT organization_id),
            COUNT(DISTINCT CASE WHEN transaction_date_time >= current_calc_hour - INTERVAL '30 days' THEN organization_id END)
        FROM customer_transactions
        WHERE transaction_date_time <= current_calc_hour
        GROUP BY market;
    END LOOP;
    CLOSE hour_cur;
END $$;

方案不足 #

每次循环都会全表扫描交易表，数据量超过100万行后性能会远低于批量计算方案。 #

优化建议 #

• 给customer_transactions表的transaction_date_time、market字段建联合索引，两种方案的查询速度都会提升3~10倍
• 如果不需要统计无交易的市场/时间组合，可以去掉CROSS JOIN相关逻辑，直接按交易存在的时间、市场分组计算

内容的提问来源于stack exchange，提问作者Robin G.