使用下一行的值将列添加到表中 [英] Adding column to table with value from next row

问题描述

我在 PostgreSQL 中有一个带有时间戳列的表，我想修改该表以具有第二个时间戳列，并使用紧接着的时间戳值作为种子.有没有办法做到这一点?这些表相当大，因此相关的子查询可能会导致机器崩溃.

更具体地说，我想从这个到那个:

<前>+----+------+ +----+------+------+|ts |数据||ts |特 |数据|+----+------+ +----+------+------+|T |... |--> |T |你|... ||你|... ||你|V |... ||V |... ||V |空|... |+----+------+ +----+------+------+

基本上，我希望能够更好地处理时间点查询(即，给我时间 X 的数据).

解决方案

基本上我认为你可以在查询时检索时间戳，而不是将其存储在表中，但是如果你正在执行这样的操作并认为这这就是你所需要的:

您需要将该列添加到您的表格中:

ALTER TABLE tablename ADD COLUMN te timestamp;

然后使用 LEAD 窗口函数执行更新并提供值.

UPDATE 表名 tSET te = x.te从 (SELECT ts, Lead(ts, 1) OVER (order by ts) AS teFROM 表名 t2) X哪里 t.ts = x.ts

以下是使用示例整数数据如何工作的示例:SQL Fiddle.

对于 timestamp 数据类型值，它的表现完全相同.

I have a table in PostgreSQL with a timestamp column, and I want to modify the table to have a second timestamp column and seed it with the value of the immediately successive timestamp. Is there a way to do this? The tables are fairly large, so a correlated subquery might kill the machine.

More concretely, I want to go from this to that:

+----+------+           +----+------+------+
| ts | data |           | ts | te   | data |
+----+------+           +----+------+------+
| T  | ...  |    -->    | T  | U    | ...  |
| U  | ...  |           | U  | V    | ...  |
| V  | ...  |           | V  | null | ...  |
+----+------+           +----+------+------+

Basically, I want to be able to hand point in time queries much better (i.e., give me the data for time X).

解决方案

Basically I think you could just retrieve the timestamp at the query time, not storing it in the table, but if you're performing such action and think that this is what you need then:

You need to add that column to your table:

ALTER TABLE tablename ADD COLUMN te timestamp;

Then perform an update feeding the value with the use of LEAD window function.

UPDATE tablename t
SET te = x.te
FROM (
  SELECT ts, lead(ts, 1) OVER (order by ts) AS te
  FROM tablename t2
  ) x
WHERE t.ts = x.ts

Here's an example of how it works using sample integer data: SQL Fiddle.

It will perform exactly the same for timestamp data type values.

这篇关于使用下一行的值将列添加到表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！