如何查询各账户月度余额：无交易时沿用历史最新余额

我有一张名为balance_history的表，结构及简化后的数据如下：

该表仅在账户发生交易时才记录余额，我可以通过rank() over partition语句获取各账户的当前最新余额，但现在需要查询各账户的月度余额：若某账户在特定月份无交易记录，则需回溯至之前月份，找到最新交易对应的余额（表中存在id字段）。期望输出结果如下：

如结果所示，即使账户c在9月无交易，仍需显示其余额。请问该如何实现？

要实现这个需求，核心是生成所有需要的月份与账户的笛卡尔积，再关联历史余额数据，通过窗口函数匹配每个月每个账户对应的最新余额，最后处理初始无余额的情况。以下是具体实现（以MySQL为例，不同数据库语法需做对应调整）：

WITH months AS (
    -- 生成目标时间段内的所有月份起始日期
    SELECT '2024-07-01' AS month_start
    UNION ALL
    SELECT DATE_ADD(month_start, INTERVAL 1 MONTH)
    FROM months
    WHERE month_start < '2024-09-01'
),
accounts AS (
    -- 获取所有唯一账户
    SELECT DISTINCT Account FROM balance_history
),
month_account_pairs AS (
    -- 生成所有月份-账户组合对
    SELECT 
        DATE_FORMAT(m.month_start, '%m/%Y') AS Month,
        a.Account,
        m.month_start
    FROM months m
    CROSS JOIN accounts a
),
ranked_balances AS (
    -- 关联历史余额，按交易日期+id排序取最新记录
    SELECT 
        map.Month,
        map.Account,
        bh.Balance,
        ROW_NUMBER() OVER (
            PARTITION BY map.Month, map.Account 
            ORDER BY bh.Transaction_date DESC, bh.id DESC
        ) AS rn
    FROM month_account_pairs map
    LEFT JOIN balance_history bh
        ON map.Account = bh.Account
        AND STR_TO_DATE(bh.Transaction_date, '%d/%m/%Y') <= map.month_start
)
-- 筛选最新记录，无余额则显示0
SELECT 
    Month,
    Account,
    COALESCE(Balance, 0) AS Balance
FROM ranked_balances
WHERE rn = 1
ORDER BY Month, Account;

关键逻辑说明 #

1. 生成月份维度：通过递归CTE生成需要统计的所有月份，确保每个月份都被覆盖
2. 笛卡尔积组合：将月份与所有账户交叉连接，保证每个账户在每个月份都有一条基础记录
3. 匹配最新余额：左连接历史交易数据，用ROW_NUMBER()窗口函数按交易日期（及id，处理同日期多条交易）降序排序，取第一条即为该月份账户的最新余额
4. 默认值处理：用COALESCE()将无交易记录的账户余额设为0，符合期望输出格式

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