根据 Postgres 中的审核事件查询最新状态

Question

我有下表包含"审核"事件(add、remove或update(：

CREATE TABLE test (
id INTEGER,
timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
action TEXT NOT NULL,
key TEXT NOT NULL,
value TEXT
);

INSERT INTO test(id, action, key, value) VALUES (1, 'add',    'name', 'Stack Overflow');
INSERT INTO test(id, action, key, value) VALUES (1, 'add',    'website', 'google.com');
INSERT INTO test(id, action, key, value) VALUES (1, 'update', 'name', 'google');
INSERT INTO test(id, action, key, value) VALUES (1, 'update', 'name', 'Google');
INSERT INTO test(id, action, key, value) VALUES (3, 'add', 'name', 'Facebook');
INSERT INTO test(id, action, key, value) VALUES (2, 'add',    'name', 'Amazon'); // row 5
INSERT INTO test(id, action, key) VALUES (2, 'remove', 'name');
INSERT INTO test(id, action, key, value) VALUES (2, 'add',    'name', 'Oracle');
INSERT INTO test(id, action, key, value) VALUES (1, 'update', 'name', 'Microsoft');
INSERT INTO test(id, action, key, value) VALUES (1, 'update', 'website', 'microsoft.com');
INSERT INTO test(id, action, key) VALUES (3, 'remove', 'name');

给定时间戳，我需要在任何时间点查询配置的"状态"。

即，

如果我使用第 5 行的时间戳查询表，我应该得到：

id, key       , value
1 , 'name'    , 'Google'
1 , 'website' , 'google.com'
2 , 'name'    , 'Amazon'
3 , 'name'    , 'Facebook'

如果我查询当前时间戳，我应该得到：

id, key       , value
1 , 'name'    , 'Microsoft'
1 , 'website' , 'microsoft.com'
2 , 'name'    , 'Oracle'

如何在Postgres中实现这一点(最好使用直接SQL(？

Answer 1

我会推荐distinct on：

select id, key, value
from (select distinct on (id, key) t.*
from test t
where timestamp < current_timestamp
order by id, key, timestamp desc
) t
where action <> 'remove';

我不知道"第 5 行"在您的上下文中是什么意思。 SQL 表表示无序集。 除非列指定了顺序或行号，否则没有"第 5 行"。

Answer 2

无需对表进行任何更改，您可以执行以下操作：

select t.id,t.key,t.value from test t,
(select id,key,max(timestamp) ts from test
group by id,key) mx
where t.id = mx.id and t.key=mx.key and t.timestamp = mx.ts
and t.value != 'remove'

但我强烈建议你添加一个自动增量主键，这样表之间的比较会快得多。