时间戳

首页 > 解决方案 > 为时间点查询建模状态更改的最佳方法

postgresql - 为时间点查询建模状态更改的最佳方法

问题描述

我正在开发一个系统，该系统需要能够在历史的特定时间找到项目的“状态”。状态是二进制的（开或关）。在这种情况下，它是确定将一条带时间戳的数据定向（到特定的“键空间”）的位置，这由数据的时间戳决定。我很难决定对数据建模的最佳方法是什么。

方法 1 是使用 tstzrange ，其状态由范围的边界隐含：

create extension btree_gist;
create table core.range_director (
    range tstzrange,
    directee_id text,
    keyspace text,
    -- allow a directee to be directed to multiple keyspaces at once
    exclude using gist (directee_id with =, keyspace with =, range with &&)
);

insert into core.range_director values
    ('[2021-01-15 00:00:00 -0:00,2021-01-20 00:00:00 -0:00)', 'THING_ID', 'KEYSPACE_1'),
    ('[2021-01-15 00:00:00 -0:00,)', 'THING_ID', 'KEYSPACE_2');

select keyspace from core.range_director
    where directee_id = 'THING_ID' and range_director.range @> '2021-01-15'::timestamptz;
-- returns KEYSPACE_1 and KEYSPACE_2
select keyspace from core.range_director
    where directee_id = 'THING_ID' and range_director.range @> '2021-01-21'::timestamptz;
-- returns KEYSPACE_2

方法 2 是有明确的状态改变：

create table core.status_director (
    status_time timestamptz,
    status text,
    directee_id text,
    keyspace text
); -- not sure what pk to use for this method

insert into core.status_director values
    ('2021-01-15 00:00:00 -0:00','Open','THING_ID','KEYSPACE_1'),
    ('2021-01-20 00:00:00 -0:00','Closed','THING_ID','KEYSPACE_1'),
    ('2021-01-15 00:00:00 -0:00','Open','THING_ID','KEYSPACE_2');

select distinct on(keyspace) keyspace, status from core.status_director
    where directee_id = 'THING_ID'
    and status_time < '2021-01-16'
    order by keyspace, status_time desc;
-- returns KEYSPACE_1:Open KEYSPACE_2:Open

select distinct on(keyspace) keyspace, status from core.status_director
    where directee_id = 'THING_ID'
    and status_time < '2021-01-21'
    order by keyspace, status_time desc;
-- returns KEYSPACE_1:Closed, KEYSPACE_2:Open
-- so, client code has to ensure that it only directs to status=Open keyspaces

也许还有其他方法也可以，但这两种方法对我来说似乎最有意义。第一种方法的好处是查询非常简单，但缺点是您现在必须更新行以关闭状态，而在第二种方法中您可以发布新状态，这似乎更容易。

该表可能会增长到数千或数万行，但可能不会增长到数百万行（但最佳方法是否会根据预期的行数而变化？）。我有几个相似的表，它们具有相同的时间点“状态”查询，因此正确地为它们建立模型非常重要。

我的直觉是选择方法 1，但是是否有任何我没有想到的脚枪或性能考虑因素会促使用例转向方法 2（或我没有考虑过的其他方法？）

标签： postgresql

解决方案

方法 1 没有脚枪，只有巨大 的大炮。使用该方法如何确定当前状态。您需要扫描每个状态更改并为每一个切换状态，或者可能使用“count(*)%2”之类的东西，奇数给出一个状态甚至另一个状态。如果任何行被删除或数据被清除并且您不知道有多少状态事务会发生什么。使用方法 2，您可以检索最大日期并直接获取状态。
对于我自己，我会做方法 3。即方法 1 + 方法 2。是的，我会有状态的日期范围和状态值本身。这给了我复杂的历史分析，因为我拥有完整的历史记录，并且可以随时直接访问当前状态。

推荐阅读