如何对这些数据进行聚合/透视？

Question

我有一组记录，用于跟踪系统的可用性。示例数据：

System_ID   Event    DateOfEvent
1           Down     2010-05-01 13:20:10
1           Up       2010-05-01 13:25:19
1           Down     2010-05-05 10:12:12
1           Up       2010-05-06 14:10:16
2           Down     2010-05-05 20:22:22
2           Up       2010-05-06 23:20:26

如何将这些数据转换为以下布局？

System_Id   DowntimeStart         DowntimeInSeconds
1           2010-05-01 13:20:10   309
1           2010-05-05 10:12:12   100684
2           2010-05-05 20:22:22   97084

- Espo

Answer 1

您可以尝试这样做：

Select et1.System_ID, et1.[Date] as DateDown, et2.[Date] as DateUp, DATEDIFF(s, et1.[Date], et2.[Date]) as DownForSeconds
    From EventTable et1
    Left Join EventTable et2 On et1.System_ID = et2.System_ID and et2.[Event] = 'Up'
Where 
et1.[Event] = 'Down'
and et2.[Date] = (Select Top 1 [Date] From EventTable Where System_ID = et2.System_ID and [Date] > et1.[Date])

- user474407

---

@ESpo：感谢Espo指出旧查询的问题，但它存在一个错误，即无序/缺失的“Up”事件无法正确显示。我已经更正了最新的查询以解决这些问题。 - user474407