SQL Server通过7个连续事务获得客户

Question

我正在尝试编写一个查询，将获得7个连续的交易给定一个CustomerKeys列表的客户。

我目前正在对SQL Server2008中有7亿条记录的客户事实表进行自联接。

这是我想出来的，但是运行起来需要很长时间。我有一个聚集索引(CustomerKey, TranDateKey)

SELECT 
    ct1.CustomerKey,ct1.TranDateKey
FROM
    CustomerTransactionFact ct1
INNER JOIN 
    #CRTCustomerList dl ON ct1.CustomerKey = dl.CustomerKey --temp table with customer list
INNER JOIN 
    dbo.CustomerTransactionFact ct2 ON ct1.CustomerKey = ct2.CustomerKey -- Same Customer
                                    AND ct2.TranDateKey >= ct1.TranDateKey 
                                    AND ct2.TranDateKey <= CONVERT(VARCHAR(8), (dateadd(d, 6, ct1.TranDateTime), 112) -- Consecutive Transactions in the last 7 days
WHERE  
    ct1.LogID >= 82800000
    AND ct2.LogID >= 82800000
    AND ct1.TranDateKey between dl.BeginTranDateKey and dl.EndTranDateKey
    AND ct2.TranDateKey between dl.BeginTranDateKey and dl.EndTranDateKey
GROUP BY   
    ct1.CustomerKey,ct1.TranDateKey
HAVING 
    COUNT(*) = 7

请帮助提高它的效率。在2008年有没有更好的方式来写这个查询？

Answer 1

您可以使用窗口函数来完成此操作，这应该会快得多。假设TranDateKey是一个数字，你可以从它中减去一个连续的数字，那么连续几天的差值是恒定的。

您可以将其放入如下查询中：

SELECT CustomerKey, MIN(TranDateKey), MAX(TranDateKey)
FROM (SELECT ct.CustomerKey, ct.TranDateKey,
             (ct.TranDateKey -
              DENSE_RANK() OVER (PARTITION BY ct.CustomerKey, ct.TranDateKey)
             ) as grp
      FROM CustomerTransactionFact ct INNER JOIN
           #CRTCustomerList dl
           ON ct.CustomerKey = dl.CustomerKey 
     ) t
GROUP BY CustomerKey, grp
HAVING COUNT(*) = 7;

如果日期键是其他键，可能有一种方法可以修改查询来处理它，但您可能必须连接到维度表。

Answer 2

对于COUNT(*) OVER (RANGE ...)来说，这将是一个完美的任务，但是SQL Server2008只支持有限的窗口聚合函数语法。

SELECT CustomerKey, MIN(TranDateKey), COUNT(*)
FROM 
 (
   SELECT CustomerKey, TranDateKey,
      dateadd(d,-ROW_NUMBER() 
                 OVER (PARTITION BY CustomerKey
                       ORDER BY TranDateKey),TranDateTime) AS dummyDate
   FROM CustomerTransactionFact 
 ) AS dt
GROUP BY CustomerKey, dummyDate
HAVING  COUNT(*) >= 7

dateadd计算每个客户所有日期的当前TranDateTime和Row_Number之间的差值。得到的dummyDate没有实际意义，但对于连续的日期来说是相同的无意义的日期。