SQL求助：客户多无交易月份余额结转，LAG仅能填充1个月致合计错误

核心思路 #

LAG()仅能获取前一行的非空值，无法跨多行填充连续空值。要解决这个问题，需通过窗口函数结合空值忽略逻辑，或标记连续无交易分组的方式，将最近一次有效期末余额批量填充到后续所有空值月份。

方案一：使用LAST_VALUE + IGNORE NULLS（支持PostgreSQL 11+、SQL Server 2022+、Oracle） #

如果你的数据库支持IGNORE NULLS参数，可直接用该窗口函数跳过空值，持续取最近的非空期末余额：

WITH customer_monthly_balances AS (
    -- 生成所有客户的全量月份（含无交易月份）
    SELECT 
        c.customer_id,
        m.month_date,
        t.end_balance
    FROM customers c
    CROSS JOIN (
        -- 生成交易覆盖的所有月份范围
        SELECT generate_series(
            (SELECT MIN(date_trunc('month', transaction_date)) FROM transactions),
            (SELECT MAX(date_trunc('month', transaction_date)) FROM transactions),
            INTERVAL '1 month'
        ) AS month_date
    ) m
    LEFT JOIN (
        -- 原逻辑：按客户+月份计算期末余额
        SELECT 
            customer_id,
            date_trunc('month', transaction_date) AS month_date,
            SUM(amount) OVER (PARTITION BY customer_id ORDER BY transaction_date) AS end_balance
        FROM transactions
        GROUP BY customer_id, transaction_date, amount
    ) t ON c.customer_id = t.customer_id AND m.month_date = t.month_date
)
SELECT 
    customer_id,
    month_date,
    -- 填充连续空值：取最近的非空期末余额
    LAST_VALUE(end_balance IGNORE NULLS) OVER (
        PARTITION BY customer_id 
        ORDER BY month_date 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS filled_end_balance,
    -- 期初余额 = 上月的填充后期末余额
    LAG(
        LAST_VALUE(end_balance IGNORE NULLS) OVER (
            PARTITION BY customer_id 
            ORDER BY month_date 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        )
    ) OVER (PARTITION BY customer_id ORDER BY month_date) AS filled_start_balance
FROM customer_monthly_balances
ORDER BY customer_id, month_date;

方案二：分组标记法（兼容MySQL 8+等多数数据库） #

若数据库不支持IGNORE NULLS，可通过标记"有效余额组"实现批量填充：

WITH customer_monthly_balances AS (
    -- 生成所有客户的全量月份（含无交易月份）
    SELECT 
        c.customer_id,
        m.month_date,
        t.end_balance
    FROM customers c
    CROSS JOIN (
        SELECT DATE_FORMAT(transaction_date, '%Y-%m-01') AS month_date
        FROM transactions
        GROUP BY DATE_FORMAT(transaction_date, '%Y-%m-01')
        -- 如需覆盖交易范围外的月份，可手动补充
        UNION ALL SELECT '2023-01-01' UNION ALL SELECT '2023-02-01'
    ) m
    LEFT JOIN (
        -- 原逻辑：按客户+月份计算期末余额
        SELECT 
            customer_id,
            DATE_FORMAT(transaction_date, '%Y-%m-01') AS month_date,
            SUM(amount) OVER (PARTITION BY customer_id ORDER BY transaction_date) AS end_balance
        FROM transactions
        GROUP BY customer_id, transaction_date, amount
    ) t ON c.customer_id = t.customer_id AND m.month_date = t.month_date
),
balance_groups AS (
    SELECT 
        *,
        -- 标记有效余额分组：每遇到非空余额，分组号+1
        SUM(CASE WHEN end_balance IS NOT NULL THEN 1 ELSE 0 END) OVER (
            PARTITION BY customer_id ORDER BY month_date
        ) AS balance_group
    FROM customer_monthly_balances
)
SELECT 
    customer_id,
    month_date,
    -- 用组内唯一非空余额填充所有行
    MAX(end_balance) OVER (PARTITION BY customer_id, balance_group) AS filled_end_balance,
    -- 期初余额 = 上月的填充后期末余额
    LAG(MAX(end_balance) OVER (PARTITION BY customer_id, balance_group)) OVER (
        PARTITION BY customer_id ORDER BY month_date
    ) AS filled_start_balance
FROM balance_groups
ORDER BY customer_id, month_date;

计算月度公司总余额 #

得到每个客户的填充后余额后，按月份聚合即可：

WITH -- 插入上述方案的CTE部分
final_balances AS (
    -- 插入上述方案的最终查询结果
)
SELECT 
    month_date,
    SUM(filled_start_balance) AS total_start_balance,
    SUM(filled_end_balance) AS total_end_balance
FROM final_balances
GROUP BY month_date
ORDER BY month_date;

关键注意点 #

• 必须先生成客户+全量月份的笛卡尔积：确保无交易的月份也出现在结果集中，这是填充空值的前提。
• 分组标记法的核心是将连续空值月份归到最近的非空余额组，用组内唯一有效余额批量填充。

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