计算自上次订单日期 TSQL 查询以来的天数

Question

我的任务是编写一个查询，该查询返回 CustomerID 为 90 的客户的所有订单，以及自客户上一个订单以来的订单总数和天数。

我将使用相关的子查询来计算自上一个订单以来的天数。

我正在使用 WideWorldImporters 示例数据库中的 Sales.Orders 和 Sales.OrderLines 表。

我需要有列 OrderID、OrderDate、OrderTotal 和 DaysSincePreviousOrder。后者是自客户上一个订单以来的天数。

我对解决方案的尝试开始设置我的查询，如下所示：

SELECT 
O.OrderID,
O.OrderDate,
ISNULL(SUM(OL.UnitPrice * OL.Quantity), 0.00) AS OrderTotal,
DATEDIFF(DAY, 
(
SELECT MAX(OrderDate)
WHERE OrderDate < O.OrderDate 
), O.OrderDate) AS DaysSincePreviousOrder       
FROM Sales.Orders O
INNER JOIN Sales.OrderLines OL ON OL.OrderID = O.OrderID
AND O.CustomerID = 90
GROUP BY O.OrderID, O.OrderDate, O.CustomerID
ORDER BY O.OrderID;

我不确定如何在相关子查询中获取客户上一个订单的日期。

我的前两个结果应如下所示：

**OrderID**---  **OrderDate**   ------**OrderTotal**    -----**DaysSincePreviousOrder**  
1455    -------2013-01-29   -----365.00    --------- NULL  
1890    -------2013-02-06   -----915.00     ----------8

任何帮助将不胜感激。

Answer 1

也许这会有所帮助。

create table MyOrder ( OrderID int,
CustomerID int,
OrderDate  date )
insert into MyOrder ( OrderID, CustomerID, OrderDate ) values
( 1, 9999, '2018-05-01T00:00:00.000' ),
( 2, 9999, '2018-05-10T00:00:00.000' ),
( 3, 9999, '2018-06-10T00:00:00.000' ),
( 4, 7777, '2018-07-01T00:00:00.000' ),
( 5, 7777, '2018-07-15T00:00:00.000' ),
( 6, 3333, '2018-09-09T00:00:00.000' )
select OrderID, 
CustomerID, 
OrderDate,
( select max(OrderDate) 
from MyOrder 
where     CustomerID = O.CustomerID 
and OrderDate < O.OrderDate ) 
as PreviousOrderDate
from MyOrder O
order by CustomerID, OrderDate

Answer 2

您可以使用LAG () OVER ()来获取上一个订单日期。然后计算日期差

SELECT O.OrderDate,
DATEDIFF(DAY, 
LAG(O.OrderDate) OVER (PARTITION BY CustomerID Order by O.OrderDate),
O.OrderDate) AS DaysSincePreviousOrder

Answer 3

David Dubois 几乎给出了正确的答案，但它没有考虑同一天的订单。

where CustomerID = O.CustomerID and OrderID < O.OrderID

而不是

where CustomerID = O.CustomerID and OrderDate < O.OrderDate

将解决这个问题。