sql - 如何在单独的列中更新 SQL 查询的结果

Question

我使用以下 PostgreSQL 查询来查找具有不同源 ID 的结束时间和下一个开始时间之间的时差。

SELECT obu_id, trip_id, start_time, end_time, dwell_time
     , end_time - LEAD(start_time) OVER(PARTITION BY obu_id ORDER BY start_time) AS diff 
 FROM clean_trips_byobu;

我想做的是用 diff 中显示的结果更新 dwell_time 列(临时结果，它没有存储在任何地方)并插入一个值“-1”而不是空格。请引用以下截图。

期望的结果:

obu_id trip_id  start_time            end_time              dwell_time  diff
2      135736   2004-10-29 15:22:14   2004-10-29 16:13:37   -02:01:42   -02:01:42
2      135738   2004-10-29 18:15:19   2004-10-29 18:28:37   -1
3      137826   2005-03-17 17:56:41   2005-03-17 18:02:31   -00:05:30   -00:05:30 
3      137826   2005-03-17 18:08:01   2005-03-17 18:12:10   -00:07:51   -00:07:51
3      137826   2005-03-17 18:20:01   2005-03-17 18:27:51   -14:51:24   -14:51:24

Answer 1

我有根据地猜测您可能想要什么:

UPDATE clean_trips_byobu c
SET    dwell_time = COALESCE(u.diff, interval '-1 hour')
FROM  (
   SELECT id, ref, start_time, end_time
        , end_time - lead(start_time) OVER (PARTITION BY id ORDER BY start_time) AS diff
   FROM   clean_trips_byobu
   ) u
WHERE (c.id, c.ref, c.start_time, c.end_time)
    = (u.id, u.ref, u.start_time, u.end_time)
AND c.dwell_time IS DISTINCT FROM COALESCE(u.diff, interval '-1 hour');

• 使用COALESCE()将 NULL 替换为默认的 interval。
• 在您的 UPDATE 中将查询用作子查询。加入一组独特的专栏。
• 如果您在 diff 中已有值，请添加最后一行以防止空更新。

您应该有一种比 4 列的组合更简单的方法来识别行。我建议使用代理主键。可以用这行代码完成:

ALTER TABLE clean_trips_byobu ADD COLUMN ctb_id serial PRIMARY KEY;

参见:

• Do I need a primary key for my table, which has a UNIQUE (composite 4-columns), one of which can be NULL?