sql - 使用 SQL 对共享相同状态的连续项目进行分组(包括虚拟数据)

Question

给定一个有时在组内重复状态的表(在本例中为“车辆”)，我想将这些状态合并为一行并聚合 status_seconds。数据看起来像这样(我将在下面包含一些 TSQL 以将虚拟数据选择到临时表中，以便轻松处理此示例)

例如，我想将车辆 T101 表的前三行合并为一行，status_seconds = 1+1+2(4 秒)。对于虚拟数据，这些是具有需要合并的连续状态行的车辆。

请注意，在第 5-7 行中，T101 的行驶状态被 T102 状态分解。
这对我来说似乎是递归 CTE 的一个问题，但我发现它很难解决。
到目前为止，我已经能够识别上述的 anchor 节点。 IE。对于每辆车，我可以确定车辆状态的最后一次出现。这是虚拟数据和标识 anchor 节点的 CTE。

CREATE TABLE ##vehiclesAndStates
(
id INT,
vehicle_name VARCHAR(30),
vehicle_status VARCHAR(30),
status_end_time DATETIME, 
status_seconds INT
)
INSERT INTO ##vehiclesAndStates VALUES 
     (100, 'T101', 'STOPPED', '2020-12-04 09:43:18.000',  1)
    ,(801, 'T101', 'STOPPED', '2020-12-04 09:43:19.000', 1)
    ,(745, 'T101', 'STOPPED', '2020-12-04 09:43:20.000', 2)
    ,(925, 'T101', 'TURNING', '2020-12-04 09:43:22.000', 1)
    ,(626, 'T101', 'TRAVELLING', '2020-12-04 09:43:23.000', 10)
    ,(401, 'T102', 'STOPPED', '2020-12-04 09:43:23.000', 10)    
    ,(201, 'T101', 'TRAVELLING', '2020-12-04 09:43:33.000', 1)
    ,(808, 'T102', 'STOPPPED', '2020-12-04 09:43:33.000', 3)
    ,(707, 'T102', 'STOPPPED', '2020-12-04 09:43:35.000', 7)
    ,(888, 'T101', 'TURNING', '2020-12-04 09:43:34.000', 1)
    ,(42, 'T101', 'STOPPED', '2020-12-04 09:43:35.000', 3)
    ,(2, 'T102', 'PARKED', '2020-12-04 09:43:35.000', 10)
    ,(911, 'T101', 'TRAVELLING', '2020-12-04 09:43:35.000', 1)

SELECT * FROM ##vehiclesAndStates


-- identify anchor nodes: rows where the previous status for a vehicle was different 

;with cte_AnchorNodes as 
  (
  
SELECT i.*
  FROM (
    SELECT 
         a.ID
        ,a.vehicle_name
        ,a.vehicle_status
        ,a.status_end_time
        ,a.status_seconds   
        ,previous_vehicle_status = LAG(a.vehicle_status,1) OVER (
            ORDER BY a.vehicle_name, a.status_end_time
        )
        ,previous_ID = LAG(a.ID,1) OVER (
            ORDER BY a.vehicle_name, a.status_end_time
        )
    FROM 
        ##vehiclesAndStates a
    ) i
    WHERE i.vehicle_status <> IsNull(i.previous_vehicle_status, 'Handle Nulls')
)

结果

但是，我正在努力使递归 CTE 工作:
--Select * From cte_AnchorNodes a order by a.vehicle_name, a.status_end_time

,cteRecursiveStatuses (Id, VehicleName, VehicleStatus, StatusEndTime, recursionDepth)  AS
  (
    SELECT  
             a.ID
            ,a.vehicle_name
            ,a.vehicle_status
            ,a.status_end_time
            ,0 recursionDepth 
      FROM  cte_AnchorNodes a

    UNION ALL

    SELECT 
            ?? 
    FROM 
            ##vehiclesAndStates b
    JOIN 
            cteRecursiveStatuses r ON r.Id = ??
     AND b.vehicle_status = r.VehicleStatus
  ) 

Select * From cteRecursiveStatuses

DROP TABLE ##vehiclesAndStates

Answer 1

这是一个典型的间隙和岛屿问题，您希望将共享相同车辆和状态(岛屿)的“相邻”行组合在一起。
您不需要为此进行递归查询:窗口函数可以完成此操作。在这里，最简单的方法可能是使用行号之间的差异来识别组。

select vehicle_name, vehicle_status, 
    min(status_end_time) as min_status_end_time, 
    max(status_end_time) as max_status_end_time, 
    sum(status_seconds)  as sum_status_seconds 
from (
    select vs.*, 
        row_number() over(partition by vehicle_name order by status_end_time) rn1,
        row_number() over(partition by vehicle_name, vehicle_status order by status_end_time) rn2
    from ##vehiclesAndStates vs
) t
group by vehicle_name, vehicle_status, rn1 - rn2
order by vehicle_name, min(status_end_time)

您可以单独运行子查询并查看行号如何变化以了解更多信息。
对于您的样本数据， the query returns :
车辆名称 |车辆状态| min_status_end_time | max_status_end_time | sum_status_seconds
:----------- | :------------- | :-------------- | :-------------- | -----------------:
T101 |停止 | 2020-12-04 09:43:18.000 | 2020-12-04 09:43:20.000 | 4
T101 |车削 | 2020-12-04 09:43:22.000 | 2020-12-04 09:43:22.000 | 1
T101 |旅行 | 2020-12-04 09:43:23.000 | 2020-12-04 09:43:33.000 | 11
T101 |车削 | 2020-12-04 09:43:34.000 | 2020-12-04 09:43:34.000 | 1
T101 |旅行 | 2020-12-04 09:43:35.000 | 2020-12-04 09:43:35.000 | 1
T101 |停止 | 2020-12-04 09:43:35.000 | 2020-12-04 09:43:35.000 | 3
T102 |停止 | 2020-12-04 09:43:23.000 | 2020-12-04 09:43:23.000 | 10
T102 |停止| 2020-12-04 09:43:33.000 | 2020-12-04 09:43:35.000 | 10
T102 | parking | 2020-12-04 09:43:35.000 | 2020-12-04 09:43:35.000 | 10