函数有问题,需要按两列分组并取另一列的平均值。
为什么下面的方法行不通?
CREATE OR REPLACE FUNCTION core.cal_average_load(
cell_load DOUBLE PRECISION,
id DOUBLE PRECISION,
s_id DOUBLE PRECISION
) RETURNS DOUBLE PRECISION
LANGUAGE plpgsql
AS $$
DECLARE out DOUBLE PRECISION;
BEGIN
out := (AVG(cell_load) OVER (PARTITION BY id, s_id));
RETURN out;
END;
$$;
上面返回的是与单元格加载值完全相同的平均加载值,没有任何聚合。
id s_id cell_load average_load
1 1 4 6
1 1 2 6
1 1 12 6
1 2 3 4
1 2 5 4
2 1 10 4.33
2 1 1 4.33
2 2 2 3.66
2 2 3 3.66
2 2 6 3.66
最佳答案
您正在为一行调用函数。使用窗口函数仅对具有多行的数据集才有意义。我认为您根本不需要函数,例如:
with my_table(id, s_id, cell_load) as (
values
(1, 1, 1),
(1, 1, 2),
(1, 2, 3),
(1, 2, 4),
(1, 2, 5)
)
select id, s_id, avg(cell_load) over (partition by id, s_id)
from my_table;
id | s_id | avg
----+------+--------------------
1 | 1 | 1.5000000000000000
1 | 1 | 1.5000000000000000
1 | 2 | 4.0000000000000000
1 | 2 | 4.0000000000000000
1 | 2 | 4.0000000000000000
(5 rows)
或:
select id, s_id, avg(cell_load)
from my_table
group by 1, 2;
id | s_id | avg
----+------+--------------------
1 | 1 | 1.5000000000000000
1 | 2 | 4.0000000000000000
(2 rows)
如果需要此功能,应如下所示:
create or replace function cal_average_load(
_id double precision,
_s_id double precision)
returns double precision
language sql as $$
select avg(cell_load)
from my_table
where id = _id and s_id = _s_id
$$;
关于sql - 按两列分组,并在PostgreSQL中查找另一列的平均值,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/45608893/