sql-server - SQL - 过滤日期 X 天数

Question

我有一张包含订单的表格。我想为特定客户选择相隔一定天数的订单。例如，在下表中，我想选择 CustomerID = 10 且与前一个实例相隔至少 30 天的所有订单。以第一次出现为起点(此数据中的 07/05/2014)。

OrderID | CustomerID |  OrderDate
==========================================
  1           10        07/05/2014
  2           10        07/15/2014
  3           11        07/20/2014
  4           11        08/20/2014
  5           11        09/21/2014
  6           10        09/23/2014
  7           10        10/15/2014
  8           10        10/30/2014

我想选择 OrderIDs (1,6,8)，因为它们彼此相距 30 天，并且都来自 CustomerID = 10 . OrderID 2 和 7 将不包括在内，因为它们是在该客户的上一个订单的 30 天内。

令我困惑的是如何将“检查点”设置为最后一个有效日期。这是一个小小的“伪”SQL。

SELECT OrderID
FROM Orders
WHERE CusomerID = 10 
  AND OrderDate > LastValidOrderDate + 30

Answer 1

我来到这里，我看到了 @SveinFidjestøl已经发布了答案，但经过长时间的尝试，我无法控制自己:
在 LAG 的帮助下和 LEAD我们可以在同一列之间进行比较
并且根据您的 Q 您正在寻找 1,6,8 .可能这是有帮助的

SQL SERVER 2012 及之后

declare @temp table
(orderid int,
customerid int,
orderDate date
);

insert into @temp values  (1,           10,        '07/05/2014')
insert into @temp values  (2,           10,        '07/15/2014')
insert into @temp values  (3,           11,        '07/20/2014')
insert into @temp values  (4,           11,        '08/20/2014')
insert into @temp values  (5,           11,        '09/21/2014')
insert into @temp values  (6,           10,        '09/23/2014')
insert into @temp values  (7,           10,        '10/15/2014')
insert into @temp values  (8,           10,        '10/30/2014');

with cte as
(SELECT orderid,customerid,orderDate,
LAG(orderDate) OVER (ORDER BY orderid ) PreviousValue,
LEAD(orderDate) OVER (ORDER BY orderid) NextValue,
rownum = ROW_NUMBER() OVER (ORDER BY orderid) 
FROM @temp
WHERE customerid = 10) 

select orderid,customerid,orderDate from cte
where DATEDIFF ( day , PreviousValue  ,  orderDate) > 30 
or PreviousValue is null or NextValue is null

SQL SERVER 2005 及之后

WITH CTE AS (
SELECT
rownum = ROW_NUMBER() OVER (ORDER BY p.orderid),
p.orderid,
p.customerid,
p.orderDate
FROM @temp p
where p.customerid = 10)

SELECT CTE.orderid,CTE.customerid,CTE.orderDate,
prev.orderDate PreviousValue,
nex.orderDate NextValue
FROM CTE
LEFT JOIN CTE prev ON prev.rownum = CTE.rownum - 1
LEFT JOIN CTE nex ON nex.rownum = CTE.rownum + 1
where CTE.customerid = 10
 and
DATEDIFF ( day , prev.orderDate  ,  CTE.orderDate) > 30 
or prev.orderDate is null or nex.orderDate is null
GO