如何用PostgreSQL计算每日所有账户最后交易余额的总和

你的核心问题是原查询仅统计了当日有交易的账户，完全忽略了当日无交易但有历史余额的账户。要实现每日所有账户的余额总和，需要先生成完整的日期序列，再为每个日期匹配所有账户的最新余额，最后求和。

步骤1：生成目标日期范围的连续日期序列 #

首先确定要统计的日期区间（比如从最早交易日到当前日期，或指定起止日期），用generate_series生成连续日期：

SELECT generate_series(
  (SELECT date_trunc('day', MIN(createdat)) FROM wallet_history),
  (SELECT date_trunc('day', MAX(createdat)) FROM wallet_history),
  '1 day'::interval
) AS stat_date;

如果需要固定区间（比如2022-10-01到2022-10-09），直接替换起止值：

SELECT generate_series(
  '2022-10-01'::date,
  '2022-10-09'::date,
  '1 day'::interval
) AS stat_date;

步骤2：关联所有账户与日期，获取每个账户在统计日期的最新余额 #

用LATERAL JOIN为每个统计日期、每个账户找到该日期及之前最后一次交易的余额：

WITH date_series AS (
  -- 生成连续日期
  SELECT generate_series(
    (SELECT date_trunc('day', MIN(createdat)) FROM wallet_history),
    (SELECT date_trunc('day', MAX(createdat)) FROM wallet_history),
    '1 day'::interval
  )::date AS stat_date
),
all_accounts AS (
  -- 获取所有唯一账户
  SELECT DISTINCT wallet_id FROM wallet_history
)
SELECT 
  ds.stat_date,
  aa.wallet_id,
  COALESCE(wh.postbalance, 0) AS latest_balance -- 无交易账户默认余额设为0，可按需调整
FROM date_series ds
CROSS JOIN all_accounts aa
LEFT JOIN LATERAL (
  SELECT postbalance
  FROM wallet_history
  WHERE wallet_id = aa.wallet_id
    AND createdat <= ds.stat_date + INTERVAL '1 day' - INTERVAL '1 millisecond' -- 匹配当日及之前所有交易
  ORDER BY createdat DESC
  LIMIT 1
) wh ON true;

步骤3：按日期聚合求和 #

对每个日期的所有账户余额求和，得到每日总余额：

WITH date_series AS (
  SELECT generate_series(
    (SELECT date_trunc('day', MIN(createdat)) FROM wallet_history),
    (SELECT date_trunc('day', MAX(createdat)) FROM wallet_history),
    '1 day'::interval
  )::date AS stat_date
),
all_accounts AS (
  SELECT DISTINCT wallet_id FROM wallet_history
),
daily_account_balances AS (
  SELECT 
    ds.stat_date,
    aa.wallet_id,
    COALESCE(wh.postbalance, 0) AS latest_balance
  FROM date_series ds
  CROSS JOIN all_accounts aa
  LEFT JOIN LATERAL (
    SELECT postbalance
    FROM wallet_history
    WHERE wallet_id = aa.wallet_id
      AND createdat <= ds.stat_date + INTERVAL '1 day' - INTERVAL '1 millisecond'
    ORDER BY createdat DESC
    LIMIT 1
  ) wh ON true
)
SELECT 
  stat_date AS "DATE",
  SUM(latest_balance) AS "BALANCE"
FROM daily_account_balances
GROUP BY stat_date
ORDER BY stat_date;

关键说明 #

1. CROSS JOIN all_accounts：确保每个统计日期都包含所有账户，无论当日是否有交易。
2. LATERAL JOIN：高效为每个账户和日期组合匹配最新交易余额，性能优于普通子查询。
3. COALESCE：处理从未有过交易的账户，默认余额设为0，可根据实际需求替换为账户初始余额（如从账户主表获取）。
4. 若你的表结构中closingBalance是最终余额字段，直接替换SQL中的postbalance即可。

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