SQL Server中如何计算同表列内的订单日期差值？

需求说明：需要计算同表内每个客户订单日期与上一次订单的天数差，规则为每个客户的首单差值为0，后续订单的差值为当前订单日期与该客户上一次订单日期的天数差。

原查询的问题 #

你提供的原SQL仅针对customer_id='1234'查询，且通过自连接实现的逻辑冗余，易产生重复数据，更高效简洁的方式是使用SQL Server的窗口函数LAG()。

优化后的SQL代码 #

SELECT 
    customer_id,
    order_date,
    ISNULL(DATEDIFF(DAY, LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date), order_date), 0) AS [Diff in orders (days)]
FROM #tmp
-- 若需过滤特定数据，可添加以下条件
-- WHERE TimeToPreviousOrder IS NULL AND stand IS NULL
ORDER BY customer_id, order_date;

代码说明 #

• PARTITION BY customer_id：按客户ID分组，确保只计算同一客户的订单间隔
• ORDER BY order_date：按订单日期排序，保证获取的是上一次订单的日期
• LAG(order_date)：获取当前行的上一行订单日期，首单无前置行时返回NULL
• DATEDIFF(DAY, ..., order_date)：计算当前订单与上一订单的天数差
• ISNULL(..., 0)：将首单的NULL差值转为0，符合需求规则

预期结果 #

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