postgresql - 计算同一张表中两列的平均值

Question

我有一个表(ttable)，看起来像这样:

id    debut     fin     value
1       2        4        10
2       4        6        0
3       6        8        12

我想更新 id n°2 的值，方法是将其设置为 id n°1 和 3 值的平均值，将它们加入某些字段(debut 和 fin)。我想得到这个:

id    debut     fin     value
1       2        4        10
2       4        6        11
3       6        8        12

... 其中 id °2 取 10 和 12 的平均值，因为它的初始值等于 id n°1 fin 值 (4) 并且它的 fin 值等于 id n°3 初始值 (6)。我在想 table 上的自连接，有点像这样:

update ttable
    set value = 
    avg(m1 + m2) as 
    (select t1.value as m1, t3.value as m2 
  from ttable t1, ttable t2, ttable t3 where t1.fin = t2.debut and where t2.fin = t3.debut) 

但这不起作用，因为我不能将它保存到一个函数中。我不知道该怎么做。有什么想法吗？

Answer 1

你可以试试lag/lead window functions .

update ttable as d
  set value = (s.lg + s.ld) / 2
from (
  select
    id,
    lag(value) over(order by id) as lg,
    lead(value) over(order by id) as ld
  from ttable
) as s
where d.id = s.id and
      d.value = 0 and
      s.lg is not null and
      s.ld is not null;

使用 SQL Fiddle 在线测试.

尝试 #2:

update ttable as d
  set value = (p.value + x.value) / 2
from ttable as p, ttable as x
where p.value > 0 and
      d.value = 0 and
      x.value > 0 and
      p.fin = d.debut and
      d.fin = x.debut;

使用 SQL Fiddle 在线测试.