PostgreSQL构建月度用户积分聚合视图如何优化冗余提升效率

你原有实现存在两个核心问题：一是使用大量关联子查询，每返回一行就会触发一次独立表扫描，性能极差；二是分组逻辑错误，未按截断后的自然月分组，子查询的时间范围与分组维度不匹配，会导致统计结果不准、出现重复行。

最优实现：条件聚合（单表扫描，性能最高） #

不需要写多层子查询，直接使用PostgreSQL支持的条件聚合语法，仅扫描一次源表即可完成所有分类求和，是性能最高的写法：

CREATE MATERIALIZED VIEW token_usage
AS
SELECT
    "user" AS username, -- user是PostgreSQL保留字，必须加双引号引用，建议后续重命名字段避免冲突
    date_trunc('month', datetime)::date AS date_from,
    (date_trunc('month', datetime) + interval '1 month')::date AS date_next,
    SUM(CASE WHEN transaction_type = 'charge' THEN credits_change ELSE 0 END) AS credits_total,
    SUM(CASE WHEN transaction_type = 'charge' THEN credits_change ELSE 0 END)
        - SUM(CASE WHEN transaction_type = 'usage' THEN credits_change ELSE 0 END) AS credits_left
FROM usage
-- 按用户+自然月维度正确分组，同一用户每月仅返回一条记录
GROUP BY "user", date_trunc('month', datetime)
WITH DATA;

无代码冗余版本：CTE分层聚合（可读性更强，无性能损失） #

如果不想重复编写CASE WHEN判断逻辑，需要复用已计算的credits_total字段，可以用CTE做一层中间聚合，把基础统计值先算完，在外层直接做差值计算。PostgreSQL优化器会自动将这类简单CTE做内联处理，不会产生额外性能开销：

CREATE MATERIALIZED VIEW token_usage
AS
WITH monthly_base_agg AS (
    SELECT
        "user" AS username,
        date_trunc('month', datetime)::date AS date_from,
        (date_trunc('month', datetime) + interval '1 month')::date AS date_next,
        SUM(CASE WHEN transaction_type = 'charge' THEN credits_change ELSE 0 END) AS credits_total,
        SUM(CASE WHEN transaction_type = 'usage' THEN credits_change ELSE 0 END) AS credits_consumed
    FROM usage
    GROUP BY "user", date_trunc('month', datetime)
)
SELECT
    username,
    date_from,
    date_next,
    credits_total,
    credits_total - credits_consumed AS credits_left
FROM monthly_base_agg
WITH DATA;

额外优化建议 #

• 数据量较大时，可在物化视图上创建唯一索引：CREATE UNIQUE INDEX idx_token_usage_user_month ON token_usage(username, date_from);，后续支持使用REFRESH MATERIALIZED VIEW CONCURRENTLY token_usage做无锁刷新，不会阻塞业务读请求。
• 时间字段建议显式转换为date类型，避免返回带时分秒的timestamp值，和业务需求的日期字段语义对齐。
• 避免使用user这类SQL保留字作为字段名，否则所有引用场景都需要加双引号，极易出现语法错误。

内容的提问来源于stack exchange，提问作者loki.dev