sql - 使用 SQL 对具有共同状态的连续项目进行分组(包括虚拟数据)
问题描述
给定一个有时在组中重复状态的表(在本例中为“车辆”),我想将这些状态合并到一行中并聚合 status_seconds。数据看起来像这样(我将在下面包含一些 TSQL 以将虚拟数据选择到临时表中,以便于使用此示例)
例如,我想将该表中车辆 T101 的前三行合并为一行,其中 status_seconds = 1+1+2(4 秒)。对于虚拟数据,这些是需要合并的具有连续状态行的车辆。
请注意,在第 5-7 行中,T101 的旅行状态被 T102 状态分解。
这对我来说似乎是递归 CTE 的一个问题,但我发现它很难解决。
到目前为止,我已经能够识别出上述的锚节点。即对于每辆车,我可以识别车辆状态的最后一次出现。这是识别锚节点的虚拟数据和 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
解决方案
这是一个典型的间隙和岛屿问题,您希望将共享相同车辆和状态(岛屿)的“相邻”行组合在一起。
您不需要为此进行递归查询:窗口函数可以完成此操作。在这里,最简单的方法可能是使用行号之间的差异来识别组。
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)
您可以单独运行子查询并查看行号如何更改以了解更多信息。
对于您的示例数据,查询返回:
车辆名称 | 车辆状态 | 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 | 停车 | 2020-12-04 09:43:35.000 | 2020-12-04 09:43:35.000 | 10
推荐阅读
- python - 如何在 Django 中使用 for 循环将每个文件保存在 request.FILES 中?
- preg-match-all - 如何使用 Preg_match_all 从多个数据中获取单个值
- php - PHP字符串不连接
- javascript - 我应该如何使用节点在猫鼬的“组”模型中将新用户添加到用户列表中?
- javascript - 在onclick函数中获取Class的变量
- html - 如何将 FileReader 结果加载到 HTML5 视频中以实现跨浏览器和设备的最佳兼容性
- python - 带有 if 语句的 lambda 函数的语法无效
- c# - 为列表中的每个项目设置权重
- haskell - Haskell - 你如何制作一个包含 getLine 的循环?
- python - 如何使用一个主键将不同的 CSV 文件合并为一个新的 CSV