sql - 查询历史数据

Question

我正在尝试在 postgresql 数据库中进行查询，但我不知道如何进行查询。我有一个历史表，用于存储对象的状态、日期和其他数据。像这样:

id objectid    date    status    ....
----------------------------
9  12          date1   2
8  12          date2   2
7  12          date3   2    <-- This is the date where the last status was set
6  12          date4   1
5  12          date5   1
4  12          date6   6
3  12          date7   6
2  12          date8   2
1  12          date9   2

我需要获取为系统中的所有对象 (objectid) 设置当前状态(每个对象的最后一组)的日期。所以在这个例子中(我只包含了对象 12 的信息来简化)如果它们按时间顺序排列(date9 是最旧的，date1 是最早的)当前状态是 2，我想得到 date3 这是母鸡这个状态是最后一次设置。请注意，状态 2 之前已设置，但随后更改为 6、1，然后再次更改为 2。我想获取当前间隔中的第一个日期。

谁能告诉我如何构造这个查询或要走的路？

谢谢。

---

UPDATE 根据@Unreason 的回答查询，这样它就可以连接到包含 objectid 引用的对象的表

SELECT objectid, max(date)
FROM (
    SELECT objectid, status, date, lag(status) OVER window1, last_value(status) OVER window1 last_status
    FROM historical
    WINDOW window1 AS ( PARTITION BY objectid ORDER BY date) 
    ) x 
WHERE (status <> lag OR lag IS NULL)
AND status = last_status
GROUP BY objectid

Answer 1

有很多方法可以做到这一点，我想到了窗口函数

有点像

SELECT max(date)
FROM (
      SELECT status, date, lag(status) OVER window1, last_value(status) OVER window1 last_status
      FROM historical
      WHERE objectid = 12
      WINDOW window1 AS ( ORDER BY date) 
      ) x 
WHERE (status <> lag OR lag IS NULL)
      AND status = last_status;

注意事项:

• 应避免使用关键字作为字段名称(例如滞后和日期)
• 还有许多其他方法可以编写此查询
• 目前它适用于一个对象 (objectid = 12)，但可以对其进行修改以返回每个对象的最后状态的日期

编辑
测试数据

CREATE TABLE historical (
    id integer,
    objectid integer,
    date date,
    status integer
);

INSERT INTO historical VALUES (1, 12, '2000-01-01', 2);
INSERT INTO historical VALUES (2, 12, '2001-01-01', 2);
INSERT INTO historical VALUES (3, 12, '2002-01-01', 6);
INSERT INTO historical VALUES (4, 12, '2003-01-01', 6);
INSERT INTO historical VALUES (5, 12, '2004-01-01', 1);
INSERT INTO historical VALUES (6, 12, '2005-01-01', 1);
INSERT INTO historical VALUES (7, 12, '2006-01-01', 2);
INSERT INTO historical VALUES (8, 12, '2007-01-01', 2);
INSERT INTO historical VALUES (9, 12, '2008-01-01', 2);

将来您可能想发布您的结果

pg_dump -t table_name --inserts

所以更容易设置测试用例