SQL实现订单跨周多节点当前及历史承诺日期查询

实现逻辑 #

• 按订单ID分组，取每组最大的date_value作为当前记录基准日期recdate，对应匹配到的prmdate即为当前承诺日期
• 基于recdate分别往前推7天、14天、28天，得到三个历史锚点日期rec1date/rec2date/rec3date
• 通过左连接原表，精准匹配各锚点日期对应的prmdate，不存在对应日期记录时返回空值

通用可运行代码 #

以下代码兼容MySQL 8.0及以上版本，SQL Server使用时只需将日期计算函数替换为DATEADD即可：

WITH order_anchor AS (
    SELECT
        order_id,
        MAX(date_value) AS recdate,
        DATE_SUB(MAX(date_value), INTERVAL 7 DAY) AS rec1date,
        DATE_SUB(MAX(date_value), INTERVAL 14 DAY) AS rec2date,
        DATE_SUB(MAX(date_value), INTERVAL 28 DAY) AS rec3date
    FROM biz_order_promise
    GROUP BY order_id
)
SELECT
    a.order_id,
    a.recdate AS current_stat_date,
    curr.prmdate AS current_promise_date,
    a.rec1date AS 7d_ago_stat_date,
    d7.prmdate AS 7d_ago_promise_date,
    a.rec2date AS 14d_ago_stat_date,
    d14.prmdate AS 14d_ago_promise_date,
    a.rec3date AS 28d_ago_stat_date,
    d28.prmdate AS 28d_ago_promise_date
FROM order_anchor a
LEFT JOIN biz_order_promise curr
    ON a.order_id = curr.order_id AND a.recdate = curr.date_value
LEFT JOIN biz_order_promise d7
    ON a.order_id = d7.order_id AND a.rec1date = d7.date_value
LEFT JOIN biz_order_promise d14
    ON a.order_id = d14.order_id AND a.rec2date = d14.date_value
LEFT JOIN biz_order_promise d28
    ON a.order_id = d28.order_id AND a.rec3date = d28.date_value;

常见错误说明 #

• 用ROW_NUMBER()按日期排序后取固定行号差匹配的逻辑不成立：date_value可能存在缺失、跳变的情况，行号差为N不代表实际日期差为N
• 用CROSS APPLY取TOP 1按日期差排序匹配的逻辑会引入误差：如果锚点日期无记录，会自动匹配相邻日期的非目标数据，不符合等值匹配要求
• 日期计算未做分组级聚合：直接在窗口函数中做日期偏移会导致每个订单的基准日期不统一，最终匹配结果错位

扩展场景适配 #

如果业务要求锚点日期无记录时，取锚点日期之前最近的一条承诺记录，可将左连接替换为关联子查询，以7天节点为例，匹配逻辑修改为：匹配同订单下date_value <= rec1date的最大date_value对应的prmdate即可。

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