在 PostgreSQL 中使用之前行的 id 更新表中的行

假設我有一張這樣的桌子：

| id | grp   | time_added | previous_id |
|----|-------|------------|-------------|
| 1  | 1     | 5          | null        |
| 2  | 1     | 8          | null        |
| 3  | 2     | 9          | null        |
| 4  | 1     | 12         | null        |
| 5  | 2     | 15         | null        |

（time_added 實際上是一個日期，但這裡使用數字是為了便於閱讀）

我想更新previous_id每一行的 ，使其等於id同一組中按 .grp排序的前一行的time_added。

這意味著執行更新查詢後，上表應如下所示：

| id | grp   | time_added | previous_id |
|----|-------|------------|-------------|
| 1  | 1     | 5          | null        |
| 2  | 1     | 8          | 1           |
| 3  | 2     | 9          | null        |
| 4  | 1     | 12         | 2           |
| 5  | 2     | 15         | 3           |

---

到目前為止，我有以下查詢：

UPDATE the_table r
SET previous_id = sub.id
FROM (
 SELECT id, time_added, grp
 FROM the_table
 ORDER BY time_added DESC
) sub
WHERE (
 r.grp = sub.grp
 AND sub.time_added < r.time_added
);

但是，我懷疑ORDER BY子查詢的 沒有做我想要的，並且previous_id被設置為它下面的隨機行的 id，而不是嚴格低於它的那個。

有關發生的事情的範例，請參見this fiddle。第 3 行的 aprevious_id為 1，而它應該為 2。

您可以使用 LAG() 函式獲得所需的結果。

select
   id,
   grp,
   time_added,
   lag(id) over (partition by grp order by grp, time_added) previous_id
from
   tbl;

您可以通過以下方式更新：

with x as
(
   select
       id,
       lag(id) over (partition by grp order by grp, time_added) previous_id
   from
       tbl
)
update tbl
set    
   previous_id = x.previous_id
from
   x
where
   x.id = tbl.id;

db<>在這裡擺弄

引用自：https://dba.stackexchange.com/questions/227300