这里有一种方法利用聚合函数作为窗口函数来实现。聚合函数将过去15分钟的观测值保存在数组中，并保持当前运行总数。状态转换函数将落后于15分钟窗口的元素从数组中移除，并推入最新的观测值。最终函数仅计算数组中的平均温度。
至于这是否有益，这取决于情况。它侧重于postgresql的plgpsql执行部分而不是数据库访问部分，我的个人经验是plpgsql不够快。如果您可以轻松地查找表格以查找每个观测值之前的15分钟行，则自我连接（如@danihp答案中）将做得很好。但是，这种方法可以处理来自某些更复杂源的观察值，其中这些查找不切实际。请根据自己的系统进行试验和比较。
create table observation(id int primary key, timestamps timestamp not null unique,
temperature numeric(5,2) not null);
create type rollavg_state as (memory observation[], total numeric(5,2));
create function rollavg_func(state rollavg_state, next_in observation) returns rollavg_state immutable language plpgsql as $$
declare
cutoff timestamp;
i int;
updated_memory observation[];
begin
raise debug 'rollavg_func: state=%, next_in=%', state, next_in;
cutoff := next_in.timestamps - '15 minutes'::interval;
i := array_lower(state.memory, 1);
raise debug 'cutoff is %', cutoff;
while i <= array_upper(state.memory, 1) and state.memory[i].timestamps < cutoff loop
raise debug 'shifting %', state.memory[i].timestamps;
i := i + 1;
state.total := state.total - state.memory[i].temperature;
end loop;
state.memory := array_append(state.memory[i:array_upper(state.memory, 1)], next_in);
state.total := coalesce(state.total, 0) + next_in.temperature;
return state;
end
$$;
create function rollavg_output(state rollavg_state) returns float8 immutable language plpgsql as $$
begin
raise debug 'rollavg_output: state=% len=%', state, array_length(state.memory, 1);
if array_length(state.memory, 1) > 0 then
return state.total / array_length(state.memory, 1);
else
return null;
end if;
end
$$;
create aggregate rollavg(observation) (sfunc = rollavg_func, finalfunc = rollavg_output, stype = rollavg_state);
select rollavg(observation) over (order by timestamps) from observation;
