计算JSONB键数组的加权平均值

Question

在我的一个表中，我有以下结构:

ID  LOG 
1   [{"x":12,"W":1}, {"x":24,"W":2}]
2   [{"x":14,"W":1.02}, {"x":20,"W":2.2}, {"x":11,"W":1.022}, {"x":45,"W":62.2}]

我正在计算应用程序端x的加权平均值，我想创建一个名为weighted_avg_x的生成列，每当数据追加到LOG列时，该列都会更新。两个问题可以使用生成的列来完成，我也有点失去了jsonb符号来计算sum(x*w)/sum(w)?

Answer 1

需要一个不可变的函数来定义生成的列:

create or replace function get_weighted_avg_x(log jsonb)
returns numeric language sql immutable as
$$
select sum((elem->'x')::numeric* (elem->'W')::numeric) / sum((elem->'W')::numeric)
from jsonb_array_elements(log) as arr(elem)
$$;
alter table my_table add column weighted_avg_x numeric
generated always as (get_weighted_avg_x(log)) stored;

这个解决方案在Postgres 12+中是可能的。在早期的版本中，你需要定义一个触发器。

在db<>fiddle中测试。