如何查询订单表中Customer_Due_Date字段的变更记录？

问题背景 #

我有一个包含数百万条记录的大型订单数据库，需要找出Customer_Due_Date字段的变更时间，通过onDate字段追踪变更。尝试用表自连接查询，但没得到想要的结果：

原查询代码：

Select
A.onDate AS Date1,
A.Order_No AS Order_No1,
A.Line_No AS LN1,
A.Customer_Due_Date AS Due_Date1,
B.onDate AS Date2,
B.Order_No AS Order_No2,
B.Line_No AS LN1,
B.Customer_Due_Date AS Due_Date2,
Count(DISTINCT A.Customer_Due_Date) as Count

FROM view_Bup_Backlog A (nolock), view_Bup_Backlog B (nolock)

WHERE A.onDate >= '2023-06-15'AND A.ERP_Id = 21
AND B.onDate >= '2023-06-15'AND B.ERP_Id = 21
AND A.Release_Key = B.Release_Key
AND A.Customer_Due_Date <> B.Customer_Due_Date

GROUP BY A.onDate, A.Order_No, A.Line_No, A.Customer_Due_Date, B.onDate, B.Order_No, B.Line_No, B.Customer_Due_Date

这个查询返回的数据太多，会列出所有到期日不匹配的记录。而我需要的是仅显示Customer_Due_Date字段每次变更的日期、变更前后的值——比如某订单仅变更2次，就只返回2条记录。

补充说明：每次编辑订单数据（哪怕改的不是Customer_Due_Date字段），都会生成一条相同Release_Key但onDate不同的新记录。我只需要捕获Customer_Due_Date字段的变更记录。

示例数据 #

期望输出 #

解决方案 #

用窗口函数LAG()可以高效获取每条记录的上一个到期日，结合去重步骤过滤掉重复的无变更记录，就能得到精准的变更日志：

WITH DeduplicatedRecords AS (
    -- 先去重：去掉同一订单行同一天的重复到期日记录，减少计算量
    SELECT DISTINCT
        onDate,
        Order_No,
        Line_No,
        Customer_Due_Date,
        Release_Key
    FROM view_Bup_Backlog
    WHERE onDate >= '2023-06-15' AND ERP_Id = 21
),
OrderedRecords AS (
    -- 用LAG窗口函数获取上一条记录的到期日
    SELECT
        onDate,
        Order_No,
        Line_No,
        Customer_Due_Date,
        LAG(Customer_Due_Date) OVER (
            PARTITION BY Release_Key, Order_No, Line_No
            ORDER BY onDate ASC
        ) AS Old_Due_Date
    FROM DeduplicatedRecords
)
-- 筛选出到期日发生变更的记录
SELECT
    onDate AS [Date Changed],
    Order_No AS [Order No],
    Line_No AS [Line Item],
    Old_Due_Date AS [Old Due Date],
    Customer_Due_Date AS [New Due Date]
FROM OrderedRecords
WHERE Old_Due_Date IS NOT NULL  -- 排除第一条无历史的记录
  AND Customer_Due_Date <> Old_Due_Date
ORDER BY Order_No, Line_No, onDate;

逻辑说明 #

1. 去重步骤：因为同一订单行可能在同一天生成多条无到期日变更的重复记录，先去重可以大幅减少后续计算的数据量，提升查询效率。
2. 窗口函数LAG()：按Release_Key、Order_No、Line_No分组，按onDate排序，自动获取每条记录的上一条到期日，无需低效的自连接。
3. 筛选变更记录：只保留上一个到期日存在且和当前到期日不同的记录，正好对应每次到期日变更的节点。

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