TSQL-查找SQL Server中多个记录的天数差异

Question

是否有可能找到SQL Server 2008 R2中不同记录的天数差异？

SELECT OrderDate FROM OrdersTbl WHERE SKU='AA0000' ORDER BY ORDERDATE DESC 


OrderDate 
----------------------- 
2009-12-03 00:00:00.000 
2009-04-03 00:00:00.000 
2008-02-22 00:00:00.000 
2008-02-21 00:00:00.000 
2007-02-18 00:00:00.000 
2007-01-27 00:00:00.000 
2006-10-13 00:00:00.000 

我想要一个方法来获得每个订单日期之间有多少天，以便我可以找到平均频率。提前致谢。

Answer 1

您可以用公共表表达式和ROW_NUMBER做到这一点：

WITH OrderDates AS (
    SELECT 
     ROW_NUMBER() OVER (ORDER BY OrderDate DESC) AS RowNumber, 
     OrderDate 
    FROM OrdersTable 
    WHERE SKU = 'AA0000' 
) 
SELECT 
    AVG(DATEDIFF(DD, O2.OrderDate, O1.OrderDate)) AS AverageFrequency 
FROM OrderDates O1 
LEFT JOIN OrderDates O2 
    ON O2.RowNumber = O1.RowNumber + 1 

Answer 2

;With cteDifference as (
    Select SKU, OrderDate, Row_Number() OVER (Partition by SKU Order by OrderDate) as RowNumber 
     from OrdersTbl 
) 
select cur.SKU, 
     cur.OrderDate as CurrentDate, 
     prev.OrderDate as PreviousDate, 
     DATEDIFF(DD,prev.OrderDate, cur.OrderDate) as DaysDifference 
    from cteDifference cur 
     left join cteDifference prev 
      on cur.SKU = prev.SKU 
       and cur.RowNumber = prev.RowNumber + 1 
    where cur.SKU = 'AA0000' 
    order by cur.OrderDate desc 

Answer 3

吮吸没有LEAD/LAG支持的SQL Server：

SELECT z.orderdate, 
     z.prev_date, 
     DATEDIFF(dd, z.prev_date, z.orderdate) 
    FROM (SELECT OrderDate, 
       (SELECT MAX(y.orderdate) 
        FROM ORDERSTBL y 
       WHERE y.orderdate < x.orderdate 
        AND y.sku = x.sku) AS prev_date 
      FROM OrdersTbl x 
     WHERE x.sku ='AA0000') z 
ORDER BY z.orderdate DESC