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

Question

我正在尝试为具有连续天数的客户记录分配行程#，并在连续天数中有休息时间(例如在当月晚些时候出现)的情况下增加行程 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 函数，甚至是递归表变量函数。我可以粘贴一些代码，但老实说，我的代码到目前为止还不起作用。如果这是一个简单的查询，但我只是没有正确地思考它，那就太好了。

提前谢谢您。

Answer 1

由于 Date 是 DATE(即没有小时)，因此您可以使用 DENSE_RANK() by Date - ROW_NUMBER() 天 将为连续天数提供一个常数值，例如；

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;

An SQLfiddle to test with .