按消息类型对行进行分组，每个消息组之间的持续时间(以分钟为单位)

Question

我有一个存储主机消息的表，我正在尝试按类型对消息进行分组并显示它们发生的分钟数。我需要显示每个组的最小消息日期和最大消息日期。我有以下查询，我能够按最小时间和消息时间的类型对消息进行分组，但是我需要最小时间是上一行的最大时间。

;WITH cte as (
SELECT CRS, WindowNumber, StateOfServiceReason,  DateOfEntry, 
ROW_NUMBER() OVER(PARTITION BY StateOfServiceReason ORDER BY CRS, Windownumber, DateOfEntry) AS gn,
ROW_NUMBER() OVER (ORDER BY CRS, WindowNumber, DateOfEntry) AS rn
FROM [dbo].[ServiceLog]
WHERE       1 = 1
AND         CRS = 'BBP'
AND         WindowNumber = 1), cte2 AS (
SELECT CRS, WindowNumber, StateOfServiceReason, DateOfEntry, gn, rn, gn - rn as gb
FROM cte ) , cte3 AS (
SELECT CRS, WindowNumber,StateOfServiceReason, MIN(DateOfEntry) AS MinMsgDate, MAX(DateOfEntry) As MaxMsgDate,
DATEDIFF(MINUTE, MIN(DateOfEntry), Max(DateOfEntry)) as TotalMinutes
FROM cte2
GROUP BY CRS, WindowNumber,StateOfServiceReason, gb)

我的输出如下所示：

CRS WindowNumber    StateOfServiceReason    MinMsgDate          MaxMsgDate      TotalMinutes
BBP 1               Unknown                 2017-07-01 00:07:46 2017-07-01 02:47:05 160
BBP 1               OperatorMaintenance     2017-07-01 02:47:05 2017-07-01 03:01:10 0
BBP 1               Unknown                 2017-07-01 03:01:10 2017-07-01 08:37:26 336
BBP 1               OperatorMaintenance     2017-07-01 08:37:26 2017-07-01 18:36:14 598

我希望它像这样

CRS WindowNumber    StateOfServiceReason    MinMsgDate          MaxMsgDate          TotalMinutes
BBP 1               Unknown                 01/07/2017 00:07:46 01/07/2017 02:47:06 xx
BBP 1               OperatorMaintenance     01/07/2017 02:47:06 01/07/2017 03:01:10 xx
BBP 1               Unknown                 01/07/2017 03:01:10 01/07/2017 08:37:27 xx
BBP 1               OperatorMaintenance     01/07/2017 08:37:27 01/07/2017 18:36:14 xx

我已经玩了一段时间的查询，任何帮助将不胜感激。

Answer 1

在 sqlserver 中，窗口函数LAG(column) OVER(order by x)可用于返回前一行中的列值，其中ORDER BY x定义了"上一个"行。请记住，您可能还需要PARTITION BY your_grouping以确保组中的第一行不会从另一个组中提取以前的值