使用LAG函数实现客户批量付款拆分至车辆账务条目的技术问询

核心规则与场景设定 #

• 默认采购规则：若未购买其他车辆，默认采购Car
• 付款拆分规则：批量付款金额需要按采购时间顺序优先分配给较早采购的物品，直到付款金额耗尽；需在报表中展示每笔采购对应的分配到的借方金额，同时标注实际借方总金额（当分配额与实际总额不符时）

交易场景与期望报表示例 #

场景1：全额60k付款（初始交易） #

实际交易数据：

期望报表输出：

场景2：仅支付15k #

实际借方记录：03-jan-2019的客户借方为15k

期望报表输出：

场景3：04-Jan-2019再支付15k（累计借方30k） #

期望报表输出：

场景4：05-Jan-2019再支付30k（累计借方60k） #

期望报表输出：

基础表结构 #

使用LAG()函数的实现方案 #

思路解析 #

1. 先按采购日期对物品排序，用窗口函数计算每笔采购的累计应付款金额
2. 用LAG()函数获取上一笔采购的累计应付款，以此确定当前物品可分配的付款金额范围
3. 结合实际付款总额，计算每笔物品实际分配到的借方金额，同时处理金额耗尽的边界情况

示例SQL代码 #

WITH ranked_purchases AS (
    -- 第一步：对采购记录按日期排序，计算累计应付款和上一笔累计额
    SELECT
        VALUE_DATE AS purchase_date,
        ITEM,
        Credit_Entry AS credit_amount,
        Debit_Entry AS total_debit,
        SUM(Credit_Entry) OVER (ORDER BY VALUE_DATE) AS cumulative_credit,
        LAG(SUM(Credit_Entry) OVER (ORDER BY VALUE_DATE), 1, 0) 
            OVER (ORDER BY VALUE_DATE) AS prev_cumulative_credit,
        -- 获取当前付款日期（可根据实际数据逻辑调整）
        MAX(VALUE_DATE) OVER () AS payment_date
    FROM your_table
    WHERE ITEM IS NOT NULL -- 过滤纯付款记录（如果存在的话）
),
allocated_debits AS (
    -- 第二步：计算每笔物品实际分配的借方金额
    SELECT
        purchase_date,
        ITEM,
        credit_amount,
        total_debit,
        payment_date,
        -- 根据实际付款总额，计算当前物品可分配的金额
        CASE
            WHEN total_debit <= prev_cumulative_credit THEN 0
            WHEN total_debit >= cumulative_credit THEN credit_amount
            ELSE total_debit - prev_cumulative_credit
        END AS allocated_debit
    FROM ranked_purchases
)
-- 第三步：生成符合需求的报表格式，添加实际总额标注
SELECT
    purchase_date AS "采购日期",
    ITEM AS "采购物品",
    credit_amount AS "贷方账户（Credit_Acc）",
    CASE
        WHEN allocated_debit = credit_amount THEN CAST(allocated_debit AS VARCHAR)
        ELSE CONCAT(allocated_debit, '(', total_debit, '实际数据)')
    END AS "客户借方（Debit Customer）",
    payment_date AS "付款日期"
FROM allocated_debits
ORDER BY purchase_date;

代码说明 #

• ranked_purchases CTE：对采购记录按日期排序，计算累计应付款cumulative_credit，并用LAG()获取上一笔的累计值prev_cumulative_credit，用来划定当前物品的付款分配区间
• allocated_debits CTE：根据实际付款总额total_debit计算分配金额：
  • 若付款总额未覆盖到当前物品，分配0
  • 若付款总额已覆盖当前及之前所有物品，分配全额采购金额
  • 否则分配剩余的付款余额
• 最终SELECT：将分配金额格式化为需求样式，当分配额不等于全额时，标注实际总借方金额

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