sql - Postgresql tstzrange 交集总和

Question

我正在努力解决以下问题。我的 PostgreSQL 13 DB 中有以下表格。

( For the sake of this question I listed only relevant columns, and I replaced timestamps with integers!)

包含来自计算机的记录（可能有 6 条）（其中 500 条）的表格，格式如下：

cmp_id	状态	时间跨度（tstzrange）
cmp_1	一个	1 - 5
cmp_1	乙	5 - 7
cmp_1	C	7 - 14
cmp_1	乙	14 - 20
cmp_2	一个	2 - 8
cmp_2	C	8 - 9
cmp_2	乙	9 - 11

列出了这些计算机的维护表（只有 2 种类型）：

cmp_id	type_of_mnt	时间跨度（tstzrange）
cmp_1	M1	2 - 6
cmp_1	M1	8 - 9
cmp_2	M1	3 - 4
cmp_2	M1	5 - 6
cmp_2	M2	9 - 10

在维护时间内报告的状态无关紧要，但要计算维护。

因此，如果用户选择从 1 到 21 的时间范围，我的目标是获得以下聚合视图。数字代表状态的持续时间。

cmp_id	一个	乙	C	M1	M2	离线
cmp_1	1	7	5	5	0	1
cmp_2	4	1	1	2	1	10

对于所选的不同时间窗口，数字也必须正确。即使它们在维护或状态期间开始或结束。

它基本上是将交叉口外的持续时间与维护时间相加并添加维护持续时间。所有这些都是通过在 state 列上旋转来完成的。离线列是日志之间的差距的总和。在上面的示例中，没有任何方法可以使其更简单。或者如果用户选择超过了可用数据的剩余时间。

我一直在尝试关注这篇文章，但没有取得多大成功。我已经能够对交叉点进行一些计算，例如计算重叠状态的真实持续时间：

with ranges as (
    select
    l.state,
    upper(l.timespan) - lower(l.timespan) log_len,
    upper(m.timespan) - lower(m.timespan) mnt_len,
    upper(l.timespan * m.timespan) - lower(l.timespan * m.timespan) inters_len
    from logs l
    join maintenances m
    on l.timespan && m.timespan
    and l.cmp_id= m.cmp_id
    )
select state, log_len, mnt_len, inters_len, log_len - inters_len as total
from ranges;

这给了我相交日志和维护的长度，以及日志的正确长度。但我还需要添加不相交日志的长度并进行旋转......

为了让事情变得更复杂（如果我们能解决这个问题），有人要求在“工作时间”或外面显示这个。因此，这一切都将在 6-18 小时内显示，并且仅在工作日显示......还有一个假期日期表也被排除在外。

标签： sqlpostgresqldatetime