在SQL Server中为具有连续日期的记录分配计数器，并且仅在日期不连续时递增

Question

我正在尝试为具有连续天数的客户的记录分配Trip #，如果他们在连续天数中有中断，则增加Trip ID，例如在本月晚些时候。数据结构如下：

CustomerID    Date
1             2014-01-01
1             2014-01-02
1             2014-01-04
2             2014-01-01
2             2014-01-05
2             2014-01-06
2             2014-01-08

基于上述示例数据集的期望输出为：

CustomerID    Date          Trip
1             2014-01-01    1
1             2014-01-02    1
1             2014-01-04    2
2             2014-01-01    1
2             2014-01-05    2
2             2014-01-06    2
2             2014-01-08    3

因此，如果该客户的日期是背靠背的，则视为相同的行程，并且具有相同的行程#。有没有一种方法可以在SQL Server中做到这一点？我使用的是MSSQL 2012。

我最初的想法是使用LAG、ROW_NUMBER或OVER/PARTITION BY函数，甚至是Recursive Table Variable Function。我可以粘贴一些代码，但老实说，我的代码到目前为止还不起作用。如果这是一个简单的查询，但我只是没有正确思考，那就太好了。

提前谢谢。

Answer 1

由于Date是DATE（即没有小时），您可以使用DENSE_RANK()乘以Date - ROW_NUMBER() days，这将为连续的天数提供一个常数值，类似于；

WITH cte AS (
  SELECT CustomerID, Date, 
    DATEADD(DAY, 
            -ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY Date), 
            Date) dt
  FROM trips
)
SELECT CustomerID, Date, 
  DENSE_RANK() OVER (PARTITION BY CustomerID ORDER BY dt)
FROM cte;

要测试的SQLfiddle。