表DEVICE_STATUS
| Id | Status | Timestamp |
|---|---|---|
| 1 | Active | 2023-01-13T18.00.01.0187528 |
| 2 | Active | 2023-01-13T18.00.01.0187529 |
| 1 | Failed | 2023-01-13T18.00.01.0187530 |
| 3 | Active | 2023-01-13T18.00.01.0187531 |
| 1 | Failed | 2023-01-13T18.00.01.0187532 |
| 1 | Active | 2023-01-13T18.00.01.0187533 |
| 3 | Active | 2023-01-13T18.00.01.0187534 |
| 1 | Failed | 2023-01-13T18.00.01.0187535 |
| 4 | Failed | 2023-01-13T18.00.01.0187536 |
| 1 | Active | 2023-01-13T18.00.01.0187537 |
预期输出(需要生成SQL查询):
| ID | Fail_Begin | Fail_End |
|---|---|---|
| 1 | 2023-01-13T18.00.01.0187530 | 2023-01-13T18.00.01.0187532 |
| 1 | 2023-01-13T18.00.01.0187535 | 2023-01-13T18.00.01.0187535 |
| 4 | 2023-01-13T18.00.01.0187536 | 2023-01-13T18.00.01.0187536 |
基本上，对于每个ID，获取min(时间戳)和max(时间戳)，但对于该ID的连续记录.如果只有一条记录，则min=max，就像示例结果集中的第二条和第三条记录一样.
我try 过这个(以及它的各种子查询变体)
SELECT Id, min(Timestamp) AS Fail_Begin, max(Timestamp) AS Fail_End
FROM Device_Status
GROUP BY Id
但只需要按连续出现的记录分组，
因此，或许先加入地位变化的概念会有所帮助？从零开始作为初始状态，然后在每次下一条记录不同时将改变码加1，从而生成如下所示的中间结果...
表DEVICE_STATUS_With_Change_Column
| Id | Status | Change | Timestamp |
|---|---|---|---|
| 1 | Active | 0 | 2023-01-13T18.00.01.0187528 |
| 2 | Active | 0 | 2023-01-13T18.00.01.0187529 |
| 1 | Failed | 1 | 2023-01-13T18.00.01.0187530 |
| 3 | Active | 0 | 2023-01-13T18.00.01.0187531 |
| 1 | Failed | 1 | 2023-01-13T18.00.01.0187532 |
| 1 | Active | 2 | 2023-01-13T18.00.01.0187533 |
| 3 | Active | 0 | 2023-01-13T18.00.01.0187534 |
| 1 | Failed | 3 | 2023-01-13T18.00.01.0187535 |
| 4 | Failed | 0 | 2023-01-13T18.00.01.0187536 |
| 1 | Active | 4 | 2023-01-13T18.00.01.0187537 |
然后再做
SELECT Id, Change, min(Timestamp) AS Fail_Begin, max(Timestamp) AS Fail_End
FROM Device_Status_With_Change_Column
GROUP BY Id, Change
除了在编程语言中循环结果集之外，我还没有看到一个直接的SQL语句可以在没有中间表的情况下一举做到这一点，我也不知道如何计算列更改(在SQL中).