sql - 使用 SQL 更新状态时间跨度并删除不需要的行

Question

假设我有一些服务器，它们不断地用它们的状态更新数据库。

我需要运行一些关于这些服务器状态的报告。稍微清理一下 table 真的很有帮助。

我为每个状态消息(开始时间和结束时间)获得 2 个时间戳。我想做的是获取具有相同状态的后续更新，然后删除它们。我想更新结束时间以反射(reflect)正确的间隔。

让我举例说明...

服务器状态表:

server   |    status    |     start_time      |       end_time
---------+------------+---------------------+---------------------
 web1    |  running     | 2013-06-04 00:00:00 | 2013-06-04 00:05:00
 web2    |  down        | 2013-06-04 00:01:00 | 2013-06-04 00:03:00
 web1    |  running     | 2013-06-04 00:05:00 | 2013-06-04 01:00:00
 msdb    |  idle        | 2013-06-04 00:02:00 | 2013-06-04 02:00:00
 web1    |  running     | 2013-06-04 01:00:00 | 2013-06-04 02:00:00
 web2    |  down        | 2013-06-04 00:03:00 | 2013-06-04 03:00:00
 web2    |  running     | 2013-06-04 03:00:00 | 2013-06-04 05:00:00
 web1    |  maintenance | 2013-06-04 02:00:00 | 2013-06-04 05:00:00
 web1    |  running     | 2013-06-04 05:00:00 | 2013-06-04 07:00:00

我希望我的表最终看起来像这样(在开始时间排序):

server   |    status    |     start_time      |       end_time
---------+------------+---------------------+---------------------
 web1    |  running     | 2013-06-04 00:00:00 | 2013-06-04 02:00:00
 web2    |  down        | 2013-06-04 00:01:00 | 2013-06-04 03:00:00
 msdb    |  idle        | 2013-06-04 00:02:00 | 2013-06-04 02:00:00
 web1    |  maintenance | 2013-06-04 02:00:00 | 2013-06-04 05:00:00
 web2    |  running     | 2013-06-04 03:00:00 | 2013-06-04 05:00:00
 web1    |  running     | 2013-06-04 05:00:00 | 2013-06-05 07:00:00

这让我确切地知道我的盒子何时改变了状态，然后当我在这些表上运行一些报告时，我可以在 SQL 中查询开始时间和结束时间之间的时间。

任何线索如何做到这一点？我假设我需要一个更新语句，然后是一个删除语句。如果需要，我可以添加行号，尽管它们目前不存在。这可能是必要的，所以我们可以排序，然后检查第 X 行的服务器和状态是否与第 X + 1 行相同。

运行 postgres 8.1(我知道，我知道。很快就会到 8.4)。

Answer 1

这是一个棘手的问题，因为对于同一个 (server, status) 有多个值组，所以一个简单的 GROUP BY 或 DISTINCT ( ON) 不会削减它。

然而，window function lag() (自 PostgreSQL 8.4 起可用)非常适合您的问题，使解决方案出奇地简单。

要在 SELECT 中获取您要查找的值:

SELECT server, status, start_time, end_time
FROM  (
   SELECT *, status IS DISTINCT FROM 
             lag(status) OVER (PARTITION BY server ORDER BY start_time) AS step
   FROM   server_status
   ) sub
WHERE  step
ORDER  BY start_time;

旧版:这也适用于 8.1。仅使用 8.4 进行测试。
相关子查询可能比窗口函数慢很多。

SELECT server, status, start_time, end_time
FROM   server_status s
WHERE ( 
   SELECT s1.status
   FROM   server_status s1
   WHERE  s1.server = s.server
   AND    s1.start_time < s.start_time
   ORDER  BY s1.start_time DESC
   LIMIT  1
   ) IS DISTINCT FROM s.status
ORDER  BY start_time;

->SQLfiddle for both
根据需要DELETE行:

DELETE FROM server_status s
USING (
   SELECT server, status, start_time
         ,status IS DISTINCT FROM
          lag(status) OVER (PARTITION BY server ORDER BY start_time) AS step
   FROM   server_status
   ) d
WHERE  s.server = d.server
AND    s.status = d.status
AND    s.start_time = d.start_time
AND    NOT d.step;

对于 8.1。仅使用 8.4 进行测试。

DELETE FROM server_status s
WHERE (   
   SELECT s1.status = s.status
   FROM   server_status s1
   WHERE  s1.server = s.server
   AND    s1.start_time < s.start_time
   ORDER  BY s1.start_time DESC
   LIMIT  1
   );

(server, start_time) 上的任何索引 将大大提高更大表的性能，任何这些查询。

您需要升级，仅出于安全原因。 (但为什么停在 8.4？直接转到当前版本。