sql - 使用 PostgreSQL 中的查询结果更新列

Question

我在 PostgreSQL 9.2 中有下表，其中包含时间戳:

gid [PK] (bigserial), timestamp_mes (timestamp without time zone), time_diff (interval)
1, 2012-01-23 11:03:40, 空
2, 2012-01-23 11:03:42, 空
3、2012-01-23 11:03:44，空

我添加了一个间隔列 (time_diff) 并希望用此查询产生的时差值填充它:

SELECT timestamp_mes - lag(timestamp_mes, 1) 
over (order by timestamp_mes) as diff
from gc_entretien.trace order by timestamp_mes

我尝试了以下查询来更新 time_diff 列，但没有成功:

UPDATE gc_entretien.trace set time_diff = 
(SELECT trace.timestamp_mes - lag(trace.timestamp_mes, 1) 
over (order by trace.timestamp_mes) 
from gc_entretien.trace order by timestamp_mes);

这会导致错误:

ERROR: more than one row returned by a subquery used as an expression

我应该如何使用时差查询产生的值来更新 time_diff 列？

Answer 1

像这样:

with new_values as (
   SELECT gid, 
          timestamp_mes - lag(timestamp_mes, 1) over (order by timestamp_mes) as diff
   from gc_entretien.trace 
)
update gc_entretien.trace as tr
  set time_diff = nv.diff
from new_values nv
where nv.gid = tr.gid;