计算JSONB键的加权平均值

发布于 2025-01-22 22:33:36 字数 349 浏览 2 评论 0原文

在我的一张表中，我具有以下结构，

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}]

我正在计算应用程序端的加权平均值，我想创建一个生成的列，称为withing_avg_x，每当数据附加到日志列。使用生成的列还可以完成两个问题，我也可以在JSONB符号上丢失，以计算sum（x*w）/sum（w）？

原文

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)?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

苍白女子 2025-01-29 22:33:36

您需要一个不可变的函数，该功能可用于定义生成的列：

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+中的解决方案。在早期版本中，您需要定义触发器。

在

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.

回复收藏 0 原文

~没有更多了~