SQL Server 2016中生成每小时重置的账户唯一序列号

针对SQL Server 2016，要实现每个账户的序列号在1小时无活动后重置，可通过以下逻辑实现：

1. 合并日期时间字段：将FuelPurchase_Date和Fuel_TOD拼接为完整的datetime类型字段，方便计算时间间隔。
2. 标记分组起点：按账户分组、时间排序，用LAG函数获取上一条记录的时间，判断当前记录与上一条的时间差是否超过1小时，超过则标记为新分组的开始。
3. 生成分组ID：通过累积求和将连续的活动记录归为同一分组。
4. 生成序列号：在每个账户的分组内，用ROW_NUMBER()生成递增的序列号。

完整SQL代码如下：

WITH CTE_PurchaseWithDateTime AS (
    SELECT 
        Account_Number,
        FuelPurchase_Date,
        Fuel_TOD,
        -- 合并日期与时间为完整datetime
        CAST(FuelPurchase_Date AS DATETIME) + CAST(Fuel_TOD AS DATETIME) AS PurchaseDateTime
    FROM #TempFuelPurchase
),
CTE_Grouped AS (
    SELECT 
        Account_Number,
        FuelPurchase_Date,
        Fuel_TOD,
        PurchaseDateTime,
        -- 判断是否为新分组起点：无前置记录 或 与前置记录间隔超1小时
        CASE 
            WHEN LAG(PurchaseDateTime) OVER (PARTITION BY Account_Number ORDER BY PurchaseDateTime) IS NULL 
                OR DATEDIFF(MINUTE, LAG(PurchaseDateTime) OVER (PARTITION BY Account_Number ORDER BY PurchaseDateTime), PurchaseDateTime) > 60
            THEN 1
            ELSE 0
        END AS IsNewGroup
    FROM CTE_PurchaseWithDateTime
),
CTE_GroupID AS (
    SELECT 
        Account_Number,
        FuelPurchase_Date,
        Fuel_TOD,
        -- 累积求和生成分组ID
        SUM(IsNewGroup) OVER (PARTITION BY Account_Number ORDER BY PurchaseDateTime ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS GroupID
    FROM CTE_Grouped
)
SELECT 
    Account_Number,
    FuelPurchase_Date,
    Fuel_TOD,
    -- 分组内生成连续序列号
    ROW_NUMBER() OVER (PARTITION BY Account_Number, GroupID ORDER BY PurchaseDateTime) AS Seq
FROM CTE_GroupID
ORDER BY Account_Number, PurchaseDateTime;

关键说明： #

• 使用DATEDIFF(MINUTE, ...)判断时间差是否超过60分钟，精准匹配1小时重置的需求。
• SUM(IsNewGroup)的窗口范围确保同一连续活动周期内的记录被归为同一分组，SQL Server 2016完全支持该语法。
• 最终结果按账户和时间排序，与期望输出完全一致。

内容的提问来源于stack exchange，提问作者Chad Portman