历史数据查询

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。我想获取当前间隔中的第一个日期。

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

谢谢。

---

根据@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

以便更轻松地设置测试用例

Answer 2

select  min(date)
from    historical
where   status        =   2 
and     objectid      =   12
and     date > 
  (select max(date) 
    from  historical
    where status <> 2)