我已经为这个问题苦苦挣扎了几天。我有一台机器可能会出错。在数据库中,我有错误出现的开始和结束时间(以 unix 时间为单位(,以及错误类型(数字从 5-12(。我遇到的问题是多个错误可能同时发生(和重叠(。
我的表如下所示:
id| type | from | to
1| 6 | 1417179933 | 1417180006
2| 6 | 1417180035 | 1417180065
3| 9 | 1417180304 | 1417180409
4| 6 | 1417180662 | 1417184364
5| 8 | 1417180662 | 1417186832
6| 9 | 1417180662 | 1417184364
7| 12 | 1417180662 | 1417184364
8| 6 | 1417184364 | 1417186832
9| 9 | 1417184364 | 1417188054
我需要找到这台机器的错误总持续时间。我无法将上表的所有差异相加,因为在同一时间间隔内可能会出现两个或多个错误。记录按升序排序。
我的猜测是将每个记录(开始和结束时间(与以前的记录进行比较,然后找到以秒为单位的差异。但是,此表会随着时间的推移而增长,搜索它是一个问题。
在PHP或MySQL中是否有一种聪明的方法可以找到机器不工作的总时间,可能是几分钟?
区间求和的一般方法,考虑到潜在的重叠,假设区间按其较低的值排序。
2 间隔案例
当[a,b]
和[c,d]
相加两个区间时,(d-c) + (b-a)
计算它们的重叠两次。
如果重叠不为零,则其值为
min(b,d) - max(a,c)
。由于您在间隔开始时对项目进行了排序,因此您知道max(a,c) == c
.如果重叠为 0,则
a <= b <= c <= d
min(b,d) == b
、max(a,c) == c
和min(b,d) - max(a,c) == b - c <= 0
。但是,您想删除0
。
因此,d-c + b-a - max(0,min(b,d)-c)
一个通用公式
泛化到更多间隔
要推广到两个以上的间隔,只需考虑当您将新的间隔[c,d]
添加到任意数量的先前间隔时,您将添加(d-c)
并且计数两次的重叠介于[c,d]
和所有先前间隔的并集之间。
其起始值对间隔进行排序,因此只需考虑此联合的最后一个连续间隔,因此对于您来说,最后一个连续的停机时间。
如果 [a,b]
是上一个连续间隔,并且您刚刚添加了[c,d]
:
- 如果
[a,b]
和[c,d]
重叠,则最后一个连续间隔将变为[a, max(b,d)]
,因为这是[a,b]
和[c,d]
的并集 - 如果
[a,b]
和[c,d]
不重叠,则您的最后一个连续间隔将变为[c, d]
(注意:我们有max(b,d) == b
(
由于a < c
由于排序间隔,因此间隔重叠c < b
在代码中
这可能比mysql更容易在php中实现。在伪代码中,假设每一行返回一个(开始,结束(错误间隔,并且[a,b]
是最后一个已知的连续间隔:
(a,b) = get_first_row();
downtime = b-a;
while( (c,d) = get_next_row() )
{
downtime += d-c - max(0, min(d,b)-c);
a = c < b ? a : c;
b = max(b,d);
}
您可以在此处看到此代码成功运行: https://3v4l.org/Q2phs
非数据库方法。可能,它可能已针对数据库进行了修改。
Start Finish
10 13
12 15
16 18
将开始时间和完成时间连接到一个带有开始标志的排序列表或数组中。
Time IsStart
10 Yes
12 Yes
13 No
15 No
16 Yes
....
使活动计数器= 0,浏览列表。
如果 IsStart,则递增活动计数器,否则
递减当活动计数器变为> 0 时,错误间隔开始,
当活动计数器变为 = 0 时,错误间隔结束。
Time ActCnt
10 1 //error state begins
12 2 //it continues
13 1 //still continues
15 0 //the end! T = 15-10 = 5
16 1 //new error state begins
从 Peterm 中窃取一个想法 计算总时间,不包括 MySQL 中的重叠时间和中断
SELECT SUM(seconds) total
FROM
(
SELECT MAX(to_date) - MIN(from_date) seconds
FROM
(
SELECT from_date, to_date,
@g := IF(@e BETWEEN from_date AND to_date OR to_date <= @e, @g, @g + 1) g,
@e := to_date
FROM my_table CROSS JOIN
(
SELECT @g := 0, @e := NULL
) i
ORDER BY from_date, to_date
) q
GROUP BY g
) q;
这是MBO的答案变成了php代码。
function sumDateTimeDurations(array $datePairs){
$list = [];
$numlength = strlen((string)(count($datePairs)*2));
$keyCtr = pow(10 ,$numlength);//Keep the keys unique and sortable
foreach($datePairs as $pair){
$list[$pair[0]->getTimestamp() . ($keyCtr++)] = [$pair[0]->getTimestamp(),true]; //true = start
$list[$pair[1]->getTimestamp() . ($keyCtr++)] = [$pair[1]->getTimestamp(),false]; //false = end
}
ksort($list);
$activeCounter = 0;
$totalSeconds = 0;
$lastStart = null;
foreach($list as $ele){
$ele[1]?$activeCounter++:$activeCounter--;
if ($ele[1] && $activeCounter == 1){
$lastStart = $ele[0];
}
if (!$ele[1] && $activeCounter == 0){
$totalSeconds += ($ele[0]) - $lastStart;
}
}
return $totalSeconds;
}
例:
$datePairs[] = [new DateTime("2022-07-18 11:31:22.141183", new DateTimeZone("UTC")),new DateTime("2022-07-18 11:32:22.141183", new DateTimeZone("UTC"))];
$datePairs[] = [new DateTime("2022-07-18 11:31:52.141183", new DateTimeZone("UTC")),new DateTime("2022-07-18 11:32:23.141183", new DateTimeZone("UTC"))];
$seconds = sumDateTimeDurations($datePairs);