PostgreSQL中基于转账表创建余额计算物化视图的问题

我们可以通过分别统计用户的收款总额与付款总额，再合并计算余额的方式创建所需物化视图，完整语句如下：

CREATE MATERIALIZED VIEW public.user_balance
(
    user_id integer NOT NULL,
    amount integer NOT NULL,
    currency integer NOT NULL,
    balance integer NOT NULL
)
AS
WITH incoming_transfers AS (
    -- 统计每个用户每种货币的收款总额
    SELECT
        recipient_id AS user_id,
        currency,
        SUM(amount) AS total_in
    FROM public.transfer
    GROUP BY recipient_id, currency
),
outgoing_transfers AS (
    -- 基于你提供的查询调整，统计每个用户每种货币的付款总额
    SELECT
        sender_id AS user_id,
        currency,
        SUM(amount) AS total_out
    FROM public.transfer
    GROUP BY sender_id, currency
)
-- 合并收支数据并计算余额
SELECT
    COALESCE(it.user_id, ot.user_id) AS user_id,
    -- 注：此处amount字段按收款总额处理，若需替换为付款总额/总交易金额，可修改为COALESCE(ot.total_out, 0)或COALESCE(it.total_in,0)+COALESCE(ot.total_out,0)
    COALESCE(it.total_in, 0) AS amount,
    COALESCE(it.currency, ot.currency) AS currency,
    COALESCE(it.total_in, 0) - COALESCE(ot.total_out, 0) AS balance
FROM incoming_transfers it
FULL OUTER JOIN outgoing_transfers ot
    ON it.user_id = ot.user_id AND it.currency = ot.currency
WITH DATA;

核心逻辑说明 #

• CTE子查询拆分：用两个子查询分别统计收款、付款的分组总额，逻辑清晰且便于维护。
• 全连接（FULL OUTER JOIN）：确保仅存在收款或仅存在付款记录的用户也能被纳入结果，避免数据遗漏。
• COALESCE函数：处理空值场景，比如用户无收款记录时，用0替代NULL参与计算，保证余额结果准确。

物化视图刷新 #

物化视图不会自动同步源表数据，当transfer表交易记录更新后，需手动刷新：

REFRESH MATERIALIZED VIEW public.user_balance;

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