SQL Server 2019中如何获取费率/资金变更的历史值？

针对你需要展示指定客户在2024年9月的费率、资金变更记录的需求，除了LAG函数，以下两种方案更灵活且能避免空值问题：

1. CTE + ROW_NUMBER + 自连接 #

先通过CTE给每个客户的记录按更新时间排序编号，再自连接匹配历史记录，精准筛选变更数据：

WITH CustomerBillingVersions AS (
    SELECT 
        pb.customerId,
        pb.RATE,
        fm.fundingName,
        pb.updateDate,
        -- 按客户分组，更新日期倒序编号（最新记录为1）
        ROW_NUMBER() OVER (PARTITION BY pb.customerId ORDER BY pb.updateDate DESC) AS versionRank
    FROM ci_periodicBillings pb
    JOIN fundingMethods fm ON pb.fundingMethodId = fm.fundingMethodId
    -- 筛选2024年9月有更新/创建的记录
    WHERE pb.updateDate >= '2024-09-01' AND pb.updateDate < '2024-10-01'
)
SELECT 
    curr.customerId,
    prev.RATE AS historicalRate,
    curr.RATE AS latestRate,
    prev.fundingName AS historicalFunding,
    curr.fundingName AS latestFunding,
    curr.updateDate AS changeOccurredDate
FROM CustomerBillingVersions curr
-- 关联同一客户的上一版本记录
LEFT JOIN CustomerBillingVersions prev 
    ON curr.customerId = prev.customerId 
    AND curr.versionRank = prev.versionRank + 1
WHERE 
    curr.customerId = '7F7CEE3C'
    -- 只保留费率或资金发生变化的记录
    AND (curr.RATE <> prev.RATE OR curr.fundingName <> prev.fundingName)

优点： #

• 清晰区分同一客户的不同版本记录，避免LAG因窗口范围错误导致的空值
• 可扩展处理同一客户当月多次变更的场景

2. 使用OUTER APPLY关联历史记录 #

通过OUTER APPLY为每条当前记录匹配最近的历史记录，逻辑更直观：

SELECT 
    curr.customerId,
    prev.RATE AS historicalRate,
    curr.RATE AS latestRate,
    prev_fm.fundingName AS historicalFunding,
    curr_fm.fundingName AS latestFunding,
    curr.updateDate AS changeOccurredDate
FROM ci_periodicBillings curr
JOIN fundingMethods curr_fm ON curr.fundingMethodId = curr_fm.fundingMethodId
-- 匹配同一客户、更新时间更早的最近一条记录
OUTER APPLY (
    SELECT TOP 1 pb_prev.RATE, fm_prev.fundingName
    FROM ci_periodicBillings pb_prev
    JOIN fundingMethods fm_prev ON pb_prev.fundingMethodId = fm_prev.fundingMethodId
    WHERE pb_prev.customerId = curr.customerId
      AND pb_prev.updateDate < curr.updateDate
    ORDER BY pb_prev.updateDate DESC
) prev
WHERE 
    curr.customerId = '7F7CEE3C'
    AND curr.updateDate >= '2024-09-01' AND curr.updateDate < '2024-10-01'
    -- 筛选有变更的记录
    AND (curr.RATE <> prev.RATE OR curr_fm.fundingName <> prev.fundingName)

优点： #

• 无需额外排序编号，直接针对每行数据获取最近历史值
• 对单条变更记录的匹配效率更高

补充说明： #

如果之前用LAG出现空值，大概率是窗口函数未按customerId分组，或者排序字段（如updateDate）存在重复值未处理，可对比上述方案调整原LAG语句，但上述两种方案在变更记录筛选上更可控。

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