TSQL查找连续3个月内发生的订单

Question

请帮助我生成以下查询。假设我有客户表和订单表。

客户表

CustID CustName

1      AA     
2      BB
3      CC
4      DD  

订单表

OrderID  OrderDate          CustID
100      01-JAN-2000        1  
101      05-FEB-2000        1     
102      10-MAR-2000        1 
103      01-NOV-2000        2    
104      05-APR-2001        2 
105      07-MAR-2002        2
106      01-JUL-2003        1
107      01-SEP-2004        4
108      01-APR-2005        4
109      01-MAY-2006        3 
110      05-MAY-2007        1  
111      07-JUN-2007        1
112      06-JUL-2007        1 

我想找出连续三个月下过订单的客户。 （允许使用SQL Server 2005和2008进行查询）。

期望的输出是：

CustName      Year   OrderDate   

    AA        2000  01-JAN-2000       
    AA        2000  05-FEB-2000
    AA        2000  10-MAR-2000

    AA        2007  05-MAY-2007        
    AA        2007  07-JUN-2007        
    AA        2007  06-JUL-2007         

Answer 1

编辑：摆脱了MAX() OVER (PARTITION BY ...)，因为这似乎会降低性能。

;WITH cte AS ( 
SELECT    CustID  ,
          OrderDate,
          DATEPART(YEAR, OrderDate)*12 + DATEPART(MONTH, OrderDate) AS YM
 FROM     Orders
 ),
 cte1 AS ( 
SELECT    CustID  ,
          OrderDate,
          YM,
          YM - DENSE_RANK() OVER (PARTITION BY CustID ORDER BY YM) AS G
 FROM     cte
 ),
 cte2 As
 (
 SELECT CustID  ,
          MIN(OrderDate) AS Mn,
          MAX(OrderDate) AS Mx
 FROM cte1
GROUP BY CustID, G
HAVING MAX(YM)-MIN(YM) >=2 
 )
SELECT     c.CustName, o.OrderDate, YEAR(o.OrderDate) AS YEAR
FROM         Customers AS c INNER JOIN
                      Orders AS o ON c.CustID = o.CustID
INNER JOIN  cte2 c2 ON c2.CustID = o.CustID and o.OrderDate between Mn and Mx
order by c.CustName, o.OrderDate

Answer 2

这是我的版本。我确实只是出于好奇才提出这个问题，以展示思考问题的另一种方式。事实证明它比这更有用，因为它的性能甚至比马丁·史密斯（Martin Smith）很酷的“分组岛屿”解决方案还要好。不过，一旦他摆脱了一些过于昂贵的聚合窗口函数并进行了真正的聚合，他的查询就开始起作用了。

解决方案 1： 运行 3 个月或更长时间，通过检查前后 1 个月并使用半连接来完成。

WITH Months AS (
   SELECT DISTINCT
      O.CustID,
      Grp = DateDiff(Month, '20000101', O.OrderDate)
   FROM
      CustOrder O
), Anchors AS (
   SELECT
      M.CustID,
      Ind = M.Grp + X.Offset
   FROM
      Months M
      CROSS JOIN (
         SELECT -1 UNION ALL SELECT 0 UNION ALL SELECT 1
      ) X (Offset)
   GROUP BY
      M.CustID,
      M.Grp + X.Offset
   HAVING
      Count(*) = 3
)
SELECT
   C.CustName,
   [Year] = Year(OrderDate),
   O.OrderDate
FROM
   Cust C
   INNER JOIN CustOrder O ON C.CustID = O.CustID
WHERE
   EXISTS (
      SELECT 1
      FROM
         Anchors A
      WHERE
         O.CustID = A.CustID
         AND O.OrderDate >= DateAdd(Month, A.Ind, '19991201')
         AND O.OrderDate < DateAdd(Month, A.Ind, '20000301')
   )
ORDER BY
   C.CustName,
   OrderDate;

解决方案 2： 精确的 3 个月模式。如果运行时间为 4 个月或更长时间，则排除这些值。这是通过检查提前两个月和落后两个月来完成的（本质上是寻找模式 N，Y，Y，Y，N）。

WITH Months AS (
   SELECT DISTINCT
      O.CustID,
      Grp = DateDiff(Month, '20000101', O.OrderDate)
   FROM
      CustOrder O
), Anchors AS (
   SELECT
      M.CustID,
      Ind = M.Grp + X.Offset
   FROM
      Months M
      CROSS JOIN (
         SELECT -2 UNION ALL SELECT -1 UNION ALL SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2
      ) X (Offset)
   GROUP BY
      M.CustID,
      M.Grp + X.Offset
   HAVING
      Count(*) = 3
      AND Min(X.Offset) = -1
      AND Max(X.Offset) = 1
)
SELECT
   C.CustName,
   [Year] = Year(OrderDate),
   O.OrderDate
FROM
   Cust C
   INNER JOIN CustOrder O ON C.CustID = O.CustID
   INNER JOIN Anchors A
      ON O.CustID = A.CustID
      AND O.OrderDate >= DateAdd(Month, A.Ind, '19991201')
      AND O.OrderDate < DateAdd(Month, A.Ind, '20000301')
ORDER BY
   C.CustName,
   OrderDate;

如果其他人想玩的话，这是我的表加载脚本：

IF Object_ID('CustOrder', 'U') IS NOT NULL DROP TABLE CustOrder
IF Object_ID('Cust', 'U') IS NOT NULL DROP TABLE Cust
GO
SET NOCOUNT ON
CREATE TABLE Cust (
  CustID int identity(1,1) NOT NULL PRIMARY KEY CLUSTERED,
  CustName varchar(100) UNIQUE
)

CREATE TABLE CustOrder (
   OrderID int identity(100, 1) NOT NULL PRIMARY KEY CLUSTERED,
   CustID int NOT NULL FOREIGN KEY REFERENCES Cust (CustID),
   OrderDate smalldatetime NOT NULL
)

DECLARE @i int
SET @i = 1000
WHILE @i > 0 BEGIN
   WITH N AS (
      SELECT
         Nm =
            Char(Abs(Checksum(NewID())) % 26 + 65)
            + Char(Abs(Checksum(NewID())) % 26 + 97)
            + Char(Abs(Checksum(NewID())) % 26 + 97)
            + Char(Abs(Checksum(NewID())) % 26 + 97)
            + Char(Abs(Checksum(NewID())) % 26 + 97)
            + Char(Abs(Checksum(NewID())) % 26 + 97)
   )
   INSERT Cust
   SELECT N.Nm
   FROM N
   WHERE NOT EXISTS (
      SELECT 1
      FROM Cust C
      WHERE
         N.Nm = C.CustName
   )

   SET @i = @i - @@RowCount
END
WHILE @i < 50000 BEGIN
   INSERT CustOrder
   SELECT TOP (50000 - @i)
      Abs(Checksum(NewID())) % 1000 + 1,
      DateAdd(Day, Abs(Checksum(NewID())) % 10000, '19900101')
   FROM master.dbo.spt_values
   SET @i = @i + @@RowCount
END

性能

以下是 3 个月或以上查询的一些性能测试结果：

Query     CPU   Reads Duration
Martin 1  2297 299412   2348 
Martin 2   625    285    809
Denis     3641    401   3855
Erik      1855  94727   2077

这只是每个运行一次，但数字是颇具代表性。事实证明，您的查询的性能毕竟并没有那么糟糕，丹尼斯。马丁的查询轻而易举地击败了其他查询，但首先使用的是他修复的一些过于昂贵的窗口函数策略。

当然，正如我所指出的，当客户在同一天有两个订单时，丹尼斯的查询不会提取正确的行，因此除非他修复了这一问题，否则他的查询不会出现争用。

此外，不同的指数也可能会带来变化。我不知道。

Answer 3

干得好：

select distinct
 CustName
,year(OrderDate) [Year]
,OrderDate
from 
(
select 
 o2.OrderDate [prev]
,o1.OrderDate [curr]
,o3.OrderDate [next]
,c.CustName
from [order] o1 
join [order] o2 on o1.CustId = o2.CustId and datediff(mm, o2.OrderDate, o1.OrderDate) = 1
join [order] o3 on o1.CustId = o3.CustId and o2.OrderId <> o3.OrderId and datediff(mm, o3.OrderDate, o1.OrderDate) = -1
join Customer c on c.CustId = o1.CustId
) t
unpivot
(
    OrderDate for [DateName] in ([prev], [curr], [next])
)
unpvt
order by CustName, OrderDate

Answer 4

这是我的看法。

select 100 as OrderID,convert(datetime,'01-JAN-2000') OrderDate,    1  as CustID  into #tmp union
    select 101,convert(datetime,'05-FEB-2000'),        1 union
    select 102,convert(datetime,'10-MAR-2000'),        1 union
    select 103,convert(datetime,'01-NOV-2000'),        2 union   
    select 104,convert(datetime,'05-APR-2001'),        2 union
    select 105,convert(datetime,'07-MAR-2002'),        2 union
    select 106,convert(datetime,'01-JUL-2003'),        1 union
    select 107,convert(datetime,'01-SEP-2004'),        4 union
    select 108,convert(datetime,'01-APR-2005'),        4 union
    select 109,convert(datetime,'01-MAY-2006'),        3 union
    select 110,convert(datetime,'05-MAY-2007'),        1 union 
    select 111,convert(datetime,'07-JUN-2007'),        1 union
    select 112,convert(datetime,'06-JUL-2007'),        1 


    ;with cte as
    (
        select
            *   
            ,convert(int,convert(char(6),orderdate,112)) - dense_rank() over(partition by custid order by orderdate) as g
        from #tmp
    ),
    cte2 as 
    (
    select 
        CustID
        ,g  
    from cte a
    group by CustID, g
    having count(g)>=3
    )
    select
        a.CustID
        ,Yr=Year(OrderDate)
        ,OrderDate
    from cte2 a join cte b
        on a.CustID=b.CustID and a.g=b.g