如何优化用户账户累计求和（Running total）的计算性能？

你当前的脚本通过窗口函数计算每日累计注册用户数，但随着public.account_users表数据量持续增长，全表扫描+从头计算累计值的效率越来越低。下面是几个比“存储历史结果再累加”更高效的优化方案：

方案1：利用物化视图（Materialized View） #

物化视图会把查询结果物理存储下来，比每次实时计算快很多，可按日增量更新：

• 创建物化视图：

CREATE MATERIALIZED VIEW daily_user_totals AS
WITH us_data AS (
    SELECT
        COUNT(us.*) AS us_reg,
        COUNT(us.*) FILTER (WHERE status = 'active') AS us_active,
        DATE_TRUNC('day', us.created_at) AS rp_date
    FROM public.account_users us
    GROUP BY rp_date
)
SELECT
    SUM(us_reg) OVER (ORDER BY rp_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)::INT AS total_users,
    SUM(us_active) OVER (ORDER BY rp_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)::INT AS total_active_users,
    rp_date
FROM us_data
ORDER BY rp_date;

• 为物化视图创建索引加速查询：

CREATE UNIQUE INDEX idx_daily_user_totals_rp_date ON daily_user_totals(rp_date);

• 每日增量更新（仅处理新增日期数据）：

WITH latest_date AS (
    SELECT MAX(rp_date) AS max_date FROM daily_user_totals
),
new_data AS (
    SELECT
        COUNT(us.*) AS us_reg,
        COUNT(us.*) FILTER (WHERE status = 'active') AS us_active,
        DATE_TRUNC('day', us.created_at) AS rp_date
    FROM public.account_users us
    WHERE DATE_TRUNC('day', us.created_at) > (SELECT max_date FROM latest_date)
    GROUP BY rp_date
),
cumulative_new AS (
    SELECT
        (SELECT total_users FROM daily_user_totals WHERE rp_date = (SELECT max_date FROM latest_date)) + SUM(us_reg) OVER (ORDER BY rp_date)::INT AS total_users,
        (SELECT total_active_users FROM daily_user_totals WHERE rp_date = (SELECT max_date FROM latest_date)) + SUM(us_active) OVER (ORDER BY rp_date)::INT AS total_active_users,
        rp_date
    FROM new_data
)
INSERT INTO daily_user_totals (total_users, total_active_users, rp_date)
SELECT total_users, total_active_users, rp_date FROM cumulative_new;

方案2：预存储每日增量，查询时计算累计 #

只存储每日新增的注册/活跃用户数，查询时通过窗口函数快速计算累计值，存储量小且更新、查询效率都很高：

• 创建每日增量表：

CREATE TABLE daily_user_increments (
    rp_date DATE PRIMARY KEY,
    new_reg INT NOT NULL DEFAULT 0,
    new_active INT NOT NULL DEFAULT 0
);
-- 初始化历史增量数据（仅执行一次）
INSERT INTO daily_user_increments (rp_date, new_reg, new_active)
SELECT
    DATE_TRUNC('day', us.created_at)::DATE AS rp_date,
    COUNT(us.*) AS new_reg,
    COUNT(us.*) FILTER (WHERE status = 'active') AS new_active
FROM public.account_users us
GROUP BY rp_date;

• 每日更新增量表：

INSERT INTO daily_user_increments (rp_date, new_reg, new_active)
SELECT
    CURRENT_DATE,
    COUNT(us.*) AS new_reg,
    COUNT(us.*) FILTER (WHERE status = 'active') AS new_active
FROM public.account_users us
WHERE DATE_TRUNC('day', us.created_at) = CURRENT_DATE
ON CONFLICT (rp_date) DO UPDATE
SET new_reg = EXCLUDED.new_reg,
    new_active = EXCLUDED.new_active;

• 查询累计值：

SELECT
    SUM(new_reg) OVER (ORDER BY rp_date)::INT AS total_users,
    SUM(new_active) OVER (ORDER BY rp_date)::INT AS total_active_users,
    rp_date
FROM daily_user_increments
ORDER BY rp_date;

方案3：用触发器维护实时累计值 #

如果需要近乎实时的累计数据，可通过触发器在用户注册/状态变更时自动更新汇总表：

• 创建汇总表：

CREATE TABLE user_cumulative_totals (
    rp_date DATE PRIMARY KEY,
    total_users INT NOT NULL,
    total_active_users INT NOT NULL
);
-- 初始化历史累计数据（仅执行一次）
INSERT INTO user_cumulative_totals (rp_date, total_users, total_active_users)
WITH us_data AS (
    SELECT
        COUNT(us.*) AS us_reg,
        COUNT(us.*) FILTER (WHERE status = 'active') AS us_active,
        DATE_TRUNC('day', us.created_at)::DATE AS rp_date
    FROM public.account_users us
    GROUP BY rp_date
)
SELECT
    rp_date,
    SUM(us_reg) OVER (ORDER BY rp_date)::INT AS total_users,
    SUM(us_active) OVER (ORDER BY rp_date)::INT AS total_active_users
FROM us_data
ORDER BY rp_date;

• 创建触发器函数：

CREATE OR REPLACE FUNCTION update_user_cumulative()
RETURNS TRIGGER AS $$
DECLARE
    target_date DATE := DATE_TRUNC('day', COALESCE(NEW.created_at, OLD.created_at))::DATE;
    prev_total_users INT := 0;
    prev_total_active INT := 0;
BEGIN
    -- 获取目标日期之前的累计值
    SELECT COALESCE(total_users, 0), COALESCE(total_active_users, 0)
    INTO prev_total_users, prev_total_active
    FROM user_cumulative_totals
    WHERE rp_date < target_date
    ORDER BY rp_date DESC
    LIMIT 1;

    IF TG_OP = 'INSERT' THEN
        -- 更新目标日期的累计值
        INSERT INTO user_cumulative_totals (rp_date, total_users, total_active_users)
        VALUES (target_date, prev_total_users + 1, prev_total_active + CASE WHEN NEW.status = 'active' THEN 1 ELSE 0 END)
        ON CONFLICT (rp_date) DO UPDATE
        SET total_users = user_cumulative_totals.total_users + 1,
            total_active_users = user_cumulative_totals.total_active_users + CASE WHEN NEW.status = 'active' THEN 1 ELSE 0 END;
    ELSIF TG_OP = 'UPDATE' AND OLD.status != NEW.status THEN
        -- 更新活跃用户数
        UPDATE user_cumulative_totals
        SET total_active_users = total_active_users + CASE WHEN NEW.status = 'active' THEN 1 ELSE -1 END
        WHERE rp_date = target_date;
    END IF;

    -- 更新后续日期的累计值（保证实时准确性）
    UPDATE user_cumulative_totals
    SET total_users = (SELECT COUNT(*) FROM public.account_users WHERE DATE_TRUNC('day', created_at) <= rp_date),
        total_active_users = (SELECT COUNT(*) FILTER (WHERE status = 'active') FROM public.account_users WHERE DATE_TRUNC('day', created_at) <= rp_date)
    WHERE rp_date > target_date;

    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

• 绑定触发器到源表：

CREATE TRIGGER trigger_update_user_cumulative
AFTER INSERT OR UPDATE OF status ON public.account_users
FOR EACH ROW EXECUTE FUNCTION update_user_cumulative();

方案4：优化原查询的索引 #

如果不想维护额外表，可给源表创建复合索引加速统计：

CREATE INDEX idx_account_users_created_at_status ON public.account_users(created_at, status);

该索引可让PostgreSQL快速按日期分组统计，避免全表扫描。若表数据量极大，还可考虑按created_at做分区表，进一步提升查询效率。

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