主页 / dba / 问题 / 265527
Accepted
feketegy
Asked: 2020-04-22 09:32:52 +0800 CST

如何创建来自 API 的事件的日期范围，其中打开状态表示日期范围，关闭状态表示范围之间的间隙？

  • 772

我想在 PostgreSQL 中存储一些具有打开或关闭状态的任意事件的日期/时间范围以及状态更改的日期/时间。

来自 API 的事件将具有以下单个事件的数据：

Request 1:
{
  id: 1,
  state: 'open',
  date: '2020-02-17T10:00:00Z'
}

Request 2:
{
  id: 1,
  state: 'close',
  date: '2020-02-17T10:10:00Z'
}

Request 3:
{
  id: 1,
  state: 'open',
  date: '2020-02-17T11:00:00Z'
}

请求可以按任何顺序出现，因此未来日期可以在过去日期之前出现，或者状态并不总是打开 -> 关闭 -> 打开 -> 关闭。例如，API 可以一个接一个地发送同一事件的打开状态。

我正在考虑使用tstzrange以下形式将这些数据保存在数据库中：

CREATE TABLE events (
  id int GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  event_id int,
  validity tstzrange
);

打开状态在有效性列中捕获，关闭状态是有效性列之间的间隙。例如，如果单个事件按此顺序具有以下状态和日期/时间（为简单起见仅使用时间）：

state          date/time
close          20:30
open           18:00
close          16:00
open           15:00
open           20:00
close          19:30

有效性行应如下所示：

id          event_id           validity
1           1                  [15:00, 16:00)
2           1                  [18:00, 19:30)
3           1                  [20:00, 20:30)

Event 1有状态open之间15:00 - 16:00，状态close之间16:00 - 18:00，状态open之间18:00 - 19:30等等。

为了直观地说明这一点：

有效性可视化

我的问题是事件没有按顺序出现，所以我不知道如何操纵各个有效性列来插入/更新这些行。

postgresql postgresql-12

1 个回答

  1. Best Answer

    我从算法的角度想出了如何将此类数据插入/更新到 PostgreSQL 日期范围中，这也可能对您有所帮助。

    由于这些数据是一个时间序列，最终当我们知道完整的数据集时，单个事件的打开、关闭状态之间不会有任何重叠。

    保持算法足够灵活，因为当我们不知道所有事件和打开/关闭状态时，需要考虑一些规则：

    如果有一个包含事件日期的有效性列，并且：

    1. 如果事件状态是open那么：

    1.1。如果包含有效性具有负无穷大 = 将包含有效性的下限更新为事件的日期。

    1.2. 如果在包含有效性之前还有另一个有效性行，这意味着包含有效性下限等于另一个有效性上限 = 将包含有效性的下限更新为事件的日期。

    1.3. 如果以上几点是错误的 = 将包含有效性拆分为两个单独的行：

    1.3.1。将包含有效性下限更新为事件的日期。1.3.2. 插入一个新行，其中下限是包含有效性的原始下限，上限是事件的日期。

    1. 如果事件状态是closethen（与打开状态规则相反）：

    2.1。如果包含有效性具有正无穷大 = 将包含有效性的上限更新为事件的日期。

    2.2. 如果在包含有效性之后严格存在另一个有效性行，则意味着包含有效性上限等于另一个有效性下限 = 将包含有效性的上限更新为事件的日期。

    2.3. 如果以上几点是错误的 = 将包含有效性拆分为两个单独的行：

    2.3.1。将包含有效性上限更新为事件的日期。

    2.3.2. 插入一个新行，其中上限是包含有效性的原始上限，下限是事件的日期。

    如果没有包含事件日期的有效范围，则：

    1. 如果事件状态是open然后插入一个新的有效性行，其下限设置为事件的日期，上限设置为正无穷大。

    2. 如果事件状态是close然后插入一个新的有效性行，上限设置为事件的日期，下限设置为负无穷大。

    这些规则为您提供了事件状态的最佳图像，其中open状态被捕获在有效性列本身中，close状态是有效性行之间的间隙。

    一旦有越来越多的数据进入，无论是历史数据还是当前数据，有效性列都会让您更清楚地了解事件状态随时间的变化。

    • 0

相关问题