时间戳

首页 > 解决方案 > SQL 按行分组列

sql-server - SQL 按行分组列

问题描述

努力使用 SQL 查询将我拥有的数据转换为所需的格式。我有一个机器事件日志，并希望将开始和停止时间以及事件结果关联到同一行。由于 SQLServer 的版本，我无法使用 LAG。任何帮助表示赞赏。

当前数据集：

+----------+----------+------------+------------------------------+---------------------+
| MACHINE  | EVENT_ID | EVENT_CODE |           DATE_TIME          | EVENT_DESCRIPTOR    |
+----------+----------+------------+------------------------------+---------------------+
| 1        | 1        | 1          | 2020-08-06 14:59:26          | SCAN : START : z1 : |
| 1        | 2        | 6          | 2020-08-06 15:00:18          | SCAN : END : z1 :   |
| 1        | 3        | 1          | 2020-08-06 15:00:45          | SCAN : START : z1 : |
| 1        | 4        | 5          | 2020-08-06 15:01:54          | SCAN : ABORT : z1 : |
| 2        | 5        | 1          | 2020-08-06 15:02:15          | SCAN : START : z1 : |
| 2        | 6        | 6          | 2020-08-06 15:05:07          | SCAN : END : z1 :   |
| 1        | 7        | 1          | 2020-08-06 15:05:13          | NEST : START : z1 : |
| 1        | 8        | 6          | 2020-08-06 15:05:22          | NEST : END : z1 :   |
| 1        | 9        | 1          | 2020-08-06 15:07:17          | CUT : START : z1 :  |
| 1        | 10       | 6          | 2020-08-06 15:10:40          | CUT : END : z1 :    |
+----------+----------+------------+------------------------------+---------------------+

我试图达到的结果：

+----------+------------------------------+------------------------------+----------+
| Machine  | SCAN:START:Z1 _TIME          | SCAN:STOP_OR_ABORT:Z1 _TIME  | OUTCOME  |
+----------+------------------------------+------------------------------+----------+
| 1        | Thu Aug 06 14:59:26 BST 2020 | 2020-08-06 15:00:18          | END      |
| 1        | Thu Aug 06 15:00:45 BST 2020 | 2020-08-06 15:01:54          | ABORT    |
| 1        | Thu Aug 06 15:02:15 BST 2020 | 2020-08-06 15:05:07          | END      |
+----------+------------------------------+------------------------------+----------+

标签： sql-servergroup-by

解决方案

您可以选择开始事件并将结束事件作为子查询加入，例如以外部应用的形式。

select L1.Machine, L1.date_time as Start, L2.datetime as Stop_Or_Abort,
       case L2.Event_Id when 5 then 'ABORT' when 6 then 'END' end as Outcome
from MyLogs L1 
     outer apply (select top 1 L2.date_time, L2.Event_Code
                  from MyLogs L2
                  where L2.Machine = L1.Machine and                         
                        L2.Event_ID > L1.Event_ID and
                        L2.Event_Code in (5, 6) 
                  order by L2.Event_ID) as L2
where L1.Event_Descriptor Like 'SCAN%' and
      L1.Event_Code = 1

推荐阅读