python - Postgresql：使用时间条件插入新记录时更新旧记录

Question

所以，我有一个 postgresql 表，它不断附加不同项目的新记录

item      period                     cost    cost_diff
---------------------------------------------------------
 bag    2019-03-15T18:15:00.000Z     100         0
 shoe   2019-03-15T18:15:00.000Z     200         0

因此，当记录进来时，它们的 cost_diff 将为 0。但是当新的记录像这样进来时

item      period                     cost    cost_diff
---------------------------------------------------------
 bag    2019-03-15T18:15:00.000Z     100         0
 shoe   2019-03-15T18:15:00.000Z     200         0
 bag    2019-03-15T18:30:00.000Z     150         0
 shoe   2019-03-15T18:45:00.000Z     300         0

旧记录的 cost_diff 将使用 (new cost - old cost) 更新，但当且仅当时间段是在 0、15、30 和时间插入数据的下一个 15 分钟时才会更新45 分钟。

item      period                     cost    cost_diff
---------------------------------------------------------
 bag    2019-03-15T18:15:00.000Z     100        50 (150-100)
 shoe   2019-03-15T18:15:00.000Z     200         0 (no update)
 bag    2019-03-15T18:30:00.000Z     150         0
 shoe   2019-03-15T18:45:00.000Z     300         0

上表显示插入了具有 15 分钟范围 (18:15->18:30) 的袋子的新记录，因此周期为 18:15 的袋子行会将 cost_diff 列从 18:30 的成本更新为 50减去从 18:15 开始的成本，这将是 150 - 50 = 100。虽然旧鞋行不会更新（仍为 0），因为进入的新鞋记录不是接下来的 15 分钟（18:15->18 :45) 并且会在时间为 18:30 的鞋行插入表中时更新，以此类推其他记录（有很多项目，不只是显示和包，如图所示）。

那么，我如何根据这个问题创建一个查询，因为记录会不断进入这个表，这可以纯粹使用 sql 查询完成还是我需要使用 python 来帮助解决这个问题（我正在做一个 etl 管道其中该任务包含在转换过程中）

谢谢

标签： pythonsqlpostgresqletl

Answer 1

您可以通过查询来做到这一点。使用lead()：

select t.*,
       (case when lead(period) over (partition by item order by period) < period + interval '15 minute'
             then lead(cost) over (partition by item order by period) - cost
             else 0
       ) as cost_diff
from t;