In one of my tables I have the following structure,
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}]
I am calculating weighted average of x on application side, I would like to create a Generated Column called weighted_avg_x
that is updated every time data is appended to LOG
column. Two questions can it be done using generated columns also I am kind of lost on the jsonb notation to calculate sum(x*w)/sum(w)
?
CodePudding user response:
You need an immutable function that may be used to define generated column:
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;
The solution is possible in Postgres 12 . In earlier versions you need to define a trigger.
Test it in db<>fiddle.