如何获取 MIN 时间戳和 MAX 时间戳之间的时间差

Question

我正在努力获得 MAX &特定时间范围的 MIN 时间戳。正如你在这里看到的：

我将使用 mysql 或 pl/sql

是否可以获得它？

例子：

Black block: 11/2/2022 05:45:28 ~ 11/2/2022 06:02:58
Red block: 13/2/2022 05:34:09 ~ 13/2/2022 06:32:44
Yellow block: 13/2/2022 10:28:06 ~ 13/2/2022 10:40:35

Answer 1

问题是：有可能得到它吗？

当然可以。我已经完成了，问题得到解答了吗？

好的，关于如何... :P

注意：

• 假设每个组以 Y 开头，以下一个 Y 之前的记录结束，并且按没有间隙的计数列排序。

  • 如果存在间隙，我们可以在（按 X order by ... 分区）上分配 row_number() 作为前面步骤的一部分，并使用它代替 count
  • 没有测试没有 N 的 Y 组的边缘情况。但我认为最小值/最大值将是相同的，并且会成功。

• 不是一个完整的解决方案，但确实提供了足够的示例来构建。这解决了您的分组问题并获得了最小值/最大值，但它并没有使用您的所有列/数据来执行此操作，而且我实际上并没有减去它们......但它确实给出了您的“示例”结果。

• 我作弊并简单地使用#而不是实际的时间戳，因为我很懒并且不想设置数据。

• 使用示例数据创建 CTE

• 创建 STEP1 CTE，它创建了一列数据，根据列表中的下一个“Y”定义每个块/组，我将其称为“NextMinCount”，更恰当地命名为“MyGrouping”

• 然后，一旦我们有了块/分组栏

• 我们使用查询来获取每个组内的最小值/最大值

样本数据： DEMO DB FIDDLE UK

+-----------+-------------+------------+--------+
| Equipment | Status_Flag | Time_Stamp | counts |
+-----------+-------------+------------+--------+
| EXPCA1Z43 | Y           |       1234 |      0 | <--Begn Grp 4
| EXPCA1Z43 | N           |       1235 |      1 |
| EXPCA1Z43 | N           |       1236 |      2 |
| EXPCA1Z43 | N           |       1237 |      3 |
| EXPCA1Z43 | N           |       1238 |      4 | <--End Grp 4
| EXPCA1Z43 | Y           |       1239 |      5 | <--Begin Grp 7
| EXPCA1Z43 | N           |       1240 |      6 |
| EXPCA1Z43 | N           |       1241 |      7 | <--End Grp 7
| EXPCA1Z43 | Y           |       1242 |      8 | <--Begin Grp 'Last'
| EXPCA1Z43 | N           |       1243 |      9 |
| EXPCA1Z43 | N           |       1244 |     10 |
| EXPCA1Z43 | N           |       1245 |     11 | <--End Grp 'Last'
+-----------+-------------+------------+--------+

假设组以 Y 开头并以下一个 Y 之前的行结束假设按计数列排序，没有间隙。

With CTE AS (SELECT 'EXPCA1Z43' Equipment, 'Y' Status_Flag, 1234 Time_Stamp, 0 counts UNION ALL
             SELECT 'EXPCA1Z43' Equipment, 'N' Status_Flag, 1235 Time_Stamp, 1 counts UNION ALL
             SELECT 'EXPCA1Z43' Equipment, 'N' Status_Flag, 1236 Time_Stamp, 2 counts UNION ALL
             SELECT 'EXPCA1Z43' Equipment, 'N' Status_Flag, 1237 Time_Stamp, 3 counts UNION ALL
             SELECT 'EXPCA1Z43' Equipment, 'N' Status_Flag, 1238 Time_Stamp, 4 counts UNION ALL
             SELECT 'EXPCA1Z43' Equipment, 'Y' Status_Flag, 1239 Time_Stamp, 5 counts UNION ALL
             SELECT 'EXPCA1Z43' Equipment, 'N' Status_Flag, 1240 Time_Stamp, 6 counts UNION ALL
             SELECT 'EXPCA1Z43' Equipment, 'N' Status_Flag, 1241 Time_Stamp, 7 counts UNION ALL
             SELECT 'EXPCA1Z43' Equipment, 'Y' Status_Flag, 1242 Time_Stamp, 8 counts UNION ALL
             SELECT 'EXPCA1Z43' Equipment, 'N' Status_Flag, 1243 Time_Stamp, 9 counts UNION ALL
             SELECT 'EXPCA1Z43' Equipment, 'N' Status_Flag, 1244 Time_Stamp, 10 counts UNION ALL
             SELECT 'EXPCA1Z43' Equipment, 'N' Status_Flag, 1245 Time_Stamp, 11 counts ),
STEP1 as (SELECT A.*, coalesce((SELECT MIN(Counts)-1 
                         FROM CTE B
                        WHERE A.Counts < B.Counts 
                          and B.Status_Flag='Y'),'LASTGROUP') NextMinCount
FROM CTE A)


SELECT nextMincount, Min(Time_Stamp) MinForGroup, max(Time_Stamp) maxForGroup  
FROM STEP1
GROUP BY nextMinCount

给我们：

上面定义分组的核心是这个内联选择：

coalesce((SELECT MIN(Counts)-1 
                             FROM CTE B
                            WHERE A.Counts < B.Counts 
                              and B.Status_Flag='Y'),'LASTGROUP') 

它只是识别最低的“Count”，即大于当前 count 的“Y”。从而定义组的范围。然后我们减去 1 以获得 Y 之前的行。我们使用合并来处理最后一个分组，该分组不会有下一个 Y 值，否则将是 NULL 名称；我们得到了第 1 步描述的第 1 步结果

第 1 步给出了我们：

+-----------+-------------+------------+--------+--------------+
| Equipment | Status_Flag | Time_Stamp | counts | NextMinCount | <--Should have called 
+-----------+-------------+------------+--------+--------------+    This MyGrouping
| EXPCA1Z43 | Y           |       1234 |      0 | 4            |
| EXPCA1Z43 | N           |       1235 |      1 | 4            |
| EXPCA1Z43 | N           |       1236 |      2 | 4            |
| EXPCA1Z43 | N           |       1237 |      3 | 4            |
| EXPCA1Z43 | N           |       1238 |      4 | 4            |
| EXPCA1Z43 | Y           |       1239 |      5 | 7            |
| EXPCA1Z43 | N           |       1240 |      6 | 7            |
| EXPCA1Z43 | N           |       1241 |      7 | 7            |
| EXPCA1Z43 | Y           |       1242 |      8 | LASTGROUP    |
| EXPCA1Z43 | N           |       1243 |      9 | LASTGROUP    |
| EXPCA1Z43 | N           |       1244 |     10 | LASTGROUP    |
| EXPCA1Z43 | N           |       1245 |     11 | LASTGROUP    |
+-----------+-------------+------------+--------+--------------+

最终查询给出了

+--------------+-------------+-------------+
| nextMincount | MinForGroup | maxForGroup |
+--------------+-------------+-------------+
| 4            |        1234 |        1238 |
| 7            |        1239 |        1241 |
| LASTGROUP    |        1242 |        1245 |
+--------------+-------------+-------------+

WHERE nextMinCount 等于您的数据块，如第 1 步结果所示。

• 4 = 你的黑色
• 7 = 你的红色
• 最后一组 = 你的黄色

我相信这就是你所追求的。