如何修改SQL查询将同ID时间数据合并为起止时间单行展示

我在SQL中有一个单日期列，每个EventAssociationID对应两条数据，希望将其中较早的时间作为StartTime、较晚的作为EndTime，合并为单行展示，同时计算时间差。

我已编写查询计算同ID两条时间的秒数差，查询语句如下：

SELECT TOP (1000) 
    [EventAssociationID], [SourceName], [Message], 
    [EventTimeStamp], [Active], [Acked],
    -- TIMESTAMPDIFF(SECOND, [EventTimeStamp]) AS difference,
    DATEDIFF(second, [EventTimeStamp], pTimeStamp) AS TotalTime
FROM 
    (SELECT
         [EventTimeStamp], [EventAssociationID], [SourceName], 
         [Message], [Active], [Acked], 
         LAG([EventTimeStamp]) OVER (PARTITION BY [EventAssociationID] ORDER BY [EventTimeStamp] DESC) pTimeStamp
     FROM 
         [dbo].[Alarms] 
     WHERE
         [SourceName] = 'RSADD_ALM_105' 
         OR [SourceName] = 'RSADD_ALM_106') q

当前输出：

EventAssociationID   SourceName      Message      EventTimeStamp       Active Acked TotalTime 
 D1FBB8784      RSADD_ALM_105   TipperLight     '2022-12-07 00:14:34'    0     0     NULL
 D1FBB8784      RSADD_ALM_105   TipperLight     '2022-12-07 00:14:16'    1     0     18
 B6DA7FBD58     RSADD_ALM_106   Curtain         '2022-12-07 11:35:51'    0     0     NULL
 B6DA7FBD58     RSADD_ALM_106   Curtain         '2022-12-07 11:35:01'    1     0     50

期望输出：

EventAssociationID  SourceName     Message    StartTime           EndTime           TotalTime 
  D1FBB8784        RSADD_ALM_105 TipperLight '2022-12-07 00:14:16' '2022-12-07 00:14:34'    18
  B6DA7FBD58       RSADD_ALM_106 Curtain     '2022-12-07 11:35:01' '2022-12-07 11:35:51'    50

修改方案一：聚合函数分组（简洁高效） #

因为每个EventAssociationID固定对应两条数据，直接用聚合函数提取最早/最晚时间，同时计算时间差：

SELECT TOP (1000)
    [EventAssociationID],
    [SourceName],
    [Message],
    MIN([EventTimeStamp]) AS StartTime,
    MAX([EventTimeStamp]) AS EndTime,
    DATEDIFF(SECOND, MIN([EventTimeStamp]), MAX([EventTimeStamp])) AS TotalTime
FROM [dbo].[Alarms]
WHERE [SourceName] IN ('RSADD_ALM_105', 'RSADD_ALM_106')
GROUP BY [EventAssociationID], [SourceName], [Message]

修改方案二：基于现有查询调整 #

保留原查询的窗口函数逻辑，过滤掉无时间差的行并调整列映射：

SELECT TOP (1000)
    [EventAssociationID],
    [SourceName],
    [Message],
    [EventTimeStamp] AS StartTime,
    pTimeStamp AS EndTime,
    TotalTime
FROM (
    SELECT
         [EventTimeStamp],
         [EventAssociationID],
         [SourceName],
         [Message],
         LAG([EventTimeStamp]) OVER (PARTITION BY [EventAssociationID] ORDER BY [EventTimeStamp] DESC) pTimeStamp,
         DATEDIFF(SECOND, [EventTimeStamp], LAG([EventTimeStamp]) OVER (PARTITION BY [EventAssociationID] ORDER BY [EventTimeStamp] DESC)) AS TotalTime
     FROM [dbo].[Alarms]
     WHERE [SourceName] IN ('RSADD_ALM_105', 'RSADD_ALM_106')
) q
WHERE TotalTime IS NOT NULL

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