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

糟糕的是 SQL Server 中没有 LEAD/LAG 支持：

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

Answer 3

;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