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

Is it possible to find the difference of days of different records in SQL Server 2008 R2?

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

I would like a way to get how many days in between there are for each order date so that I could find the average frequency. Thanks in advance.

我想要一种方法来计算每个订单日期之间的天数，这样我就能找到平均频率。提前谢谢。

3 个解决方案

#1

You can do it with a common table expression and ROW_NUMBER:

您可以使用公共表表达式和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

#2

Sucks there's no LEAD/LAG support in SQL Server:

在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

#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

#1