如何用 SQL 计算每个 Agent 的每日累计余额

结论

可以用一条 SQL 完成。不要直接按 agent_id 和日期连接 Sales 与 Payments。先把两类数据转换为统一的“每日资金变动”，按 Agent 和日期汇总，再用窗口函数计算累计余额。

这种写法能保留只有 Sales 或只有 Payments 的日期，也能避免多对多连接导致金额重复。

为什么直接 JOIN 会重复汇总

假设某个 Agent 同一天有：

• 2 条 Sales
• 2 条 Payments

直接连接两个明细表会得到 2 × 2 = 4 行，每条 Sale 和 Payment 都会重复出现。此时再执行 SUM()，金额就会被重复累计。

问题不在 SUM()，而是连接前的两个表都没有达到“一名 Agent 每天一行”的粒度。

推荐查询

下面的写法适用于支持 CTE 和窗口函数的数据库。查询先用 UNION ALL 合并资金变动，再按天汇总：

WITH movements AS (
    SELECT
        agent_id,
        CAST(sale_date AS DATE) AS activity_date,
        COALESCE(amount, 0) AS sales_amount,
        0 AS payment_amount
    FROM Sales

    UNION ALL

    SELECT
        agent_id,
        CAST(payment_date AS DATE) AS activity_date,
        0 AS sales_amount,
        COALESCE(amount, 0) AS payment_amount
    FROM Payments
    -- 如果只有已完成的付款才应入账，请在这里增加状态条件：
    -- WHERE status = '实际的成功状态值'
),
daily_summary AS (
    SELECT
        agent_id,
        activity_date,
        SUM(sales_amount) AS total_sales,
        SUM(payment_amount) AS total_payments
    FROM movements
    GROUP BY
        agent_id,
        activity_date
)
SELECT
    a.id AS agent_id,
    a.name AS agent_name,
    d.activity_date,
    d.total_sales,
    d.total_payments,
    d.total_sales - d.total_payments AS daily_balance,
    COALESCE(a.opening_balance, 0)
        + SUM(d.total_sales - d.total_payments) OVER (
            PARTITION BY d.agent_id
            ORDER BY d.activity_date
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS cumulative_balance
FROM daily_summary AS d
JOIN Agents AS a
    ON a.id = d.agent_id
ORDER BY
    a.id,
    d.activity_date;

计算方式如下：

daily_balance = total_sales - total_payments

cumulative_balance
    = opening_balance
    + 从第一天到当前日期的 daily_balance 总和

例如：

使用 UNION ALL 后，只有 Payment 的日期仍会出现在结果中，只有 Sale 的日期也不会丢失。

如果更喜欢先分别汇总

也可以分别聚合 Sales 和 Payments，再合并两份日汇总数据。原理相同：

WITH sales_daily AS (
    SELECT
        agent_id,
        CAST(sale_date AS DATE) AS activity_date,
        SUM(COALESCE(amount, 0)) AS total_sales,
        0 AS total_payments
    FROM Sales
    GROUP BY
        agent_id,
        CAST(sale_date AS DATE)
),
payments_daily AS (
    SELECT
        agent_id,
        CAST(payment_date AS DATE) AS activity_date,
        0 AS total_sales,
        SUM(COALESCE(amount, 0)) AS total_payments
    FROM Payments
    -- WHERE status = '实际的成功状态值'
    GROUP BY
        agent_id,
        CAST(payment_date AS DATE)
),
daily_summary AS (
    SELECT
        agent_id,
        activity_date,
        SUM(total_sales) AS total_sales,
        SUM(total_payments) AS total_payments
    FROM (
        SELECT * FROM sales_daily
        UNION ALL
        SELECT * FROM payments_daily
    ) AS daily_entries
    GROUP BY
        agent_id,
        activity_date
)
SELECT
    a.id AS agent_id,
    a.name AS agent_name,
    d.activity_date,
    d.total_sales,
    d.total_payments,
    d.total_sales - d.total_payments AS daily_balance,
    COALESCE(a.opening_balance, 0)
        + SUM(d.total_sales - d.total_payments) OVER (
            PARTITION BY d.agent_id
            ORDER BY d.activity_date
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS cumulative_balance
FROM daily_summary AS d
JOIN Agents AS a
    ON a.id = d.agent_id
ORDER BY
    a.id,
    d.activity_date;

这种结构稍长，但各个 CTE 的业务含义更清楚，也便于单独检查 Sales 或 Payments 的日汇总结果。

性能优化

建议至少创建以下索引：

CREATE INDEX idx_sales_agent_date
    ON Sales (agent_id, sale_date);

CREATE INDEX idx_payments_agent_date
    ON Payments (agent_id, payment_date);

如果报表只统计某种 Payment 状态，可以根据实际筛选条件考虑包含 status 的复合索引，例如：

CREATE INDEX idx_payments_status_agent_date
    ON Payments (status, agent_id, payment_date);

索引字段的顺序需要结合实际查询条件和数据库执行计划确定，不能只看表结构就断定哪一种最快。

报表有日期范围时，最好直接在两个明细查询中增加过滤条件，避免扫描全部历史数据。字段包含时间部分时，建议使用半开区间：

WHERE sale_date >= :start_date
  AND sale_date <  :end_date_exclusive

Payments 分支使用相同的日期条件。与在列上调用日期函数进行筛选相比，这种写法通常更便于数据库使用日期索引。

注意事项

1. 确认余额的正负方向

上面的查询使用以下规则：

余额 = opening_balance + Sales - Payments

也就是说，Sales 增加 Agent 应付金额，Payments 减少应付金额。如果系统用正数表示“业务方欠 Agent”，则需要反转计算方向：

d.total_payments - d.total_sales

opening_balance 也必须使用同一套符号规则。

2. 明确哪些 Payment 可以入账

如果 Payments.status 包含待处理、失败或撤销等状态，应当只汇总真正生效的付款。具体状态值由现有数据定义决定，不能直接假设为 paid、completed 或其他固定值。

3. 日期字段可能包含时间

如果 sale_date 和 payment_date 是时间戳，CAST(... AS DATE) 会按照数据库会话时区转换日期。跨时区系统应先统一业务时区，再截取日期，否则临近午夜的数据可能被分到错误的日期。

4. 没有业务发生的日期不会显示

查询会显示存在 Sale 或 Payment 的所有日期，但不会自动生成完全没有业务记录的日期。

如果报表要求每天都有一行，包括 Sales 和 Payments 均为零的日期，就需要额外准备日历表或日期序列，与 Agent 组合后再 LEFT JOIN daily_summary。这种方式会明显增加结果行数，因此应当限定明确的日期范围。

5. 不需要在应用层逐条循环

逐条查询 Payments 会造成典型的 N+1 查询问题。Sales 越多，应用与数据库之间的往返次数就越多。日汇总和累计计算都适合集合运算，由数据库一次完成通常更简单，也更容易保证结果一致。

备注：内容仅供参考。