你可以定义自己的聚合函数,然后使用窗口规范来获取每个阶段的聚合输出,而不是单个值。
因此,聚合是一种状态,以及一个转换函数来修改该状态以适用于每一行,并可选地提供一个最终化函数来将状态转换为输出值。对于这样的简单情况,只需要一个转换函数就足够了。
create function ema_func(numeric, numeric) returns numeric
language plpgsql as $$
declare
alpha numeric := 0.5;
begin
return case
when $1 is null then $2
else alpha * $2 + (1 - alpha) * $1
end;
end
$$;
create aggregate ema(basetype = numeric, sfunc = ema_func, stype = numeric);
这给了我:
steve@steve@[local] =# select x, ema(x, 0.1) over(w), ema(x, 0.2) over(w) from data window w as (order by n asc) limit 5;
x | ema | ema
44.988564 | 44.988564 | 44.988564
39.5634 | 44.4460476 | 43.9035312
38.605724 | 43.86201524 | 42.84396976
38.209646 | 43.296778316 | 41.917105008
44.541264 | 43.4212268844 | 42.4419368064
这些数字似乎与你在问题中添加的电子表格相匹配。
另外,您可以从语句中定义函数以将 alpha 作为参数传递:
create or replace function ema_func(state numeric, inval numeric, alpha numeric)
returns numeric
language plpgsql as $$
begin
return case
when state is null then inval
else alpha * inval + (1-alpha) * state
end;
end
$$;
create aggregate ema(numeric, numeric) (sfunc = ema_func, stype = numeric);
select x, ema(x, 0.5 ) over (order by n asc) from data
此外,这个函数实际上非常简单,完全不需要使用plpgsql语言来实现,因为它可以作为SQL函数实现,虽然你不能在其中按名称引用参数:
create or replace function ema_func(state numeric, inval numeric, alpha numeric)
returns numeric
language sql as $$
select case
when $1 is null then $2
else $3 * $2 + (1-$3) * $1
end
$$;