sirve - que tal es postgresql

Promedio móvil basado en marcas de tiempo en PostgreSQL

Question

Quería realizar promedios móviles a través de marcas de tiempo. Tengo dos columnas: temperatura y marcas de tiempo (fecha y hora) y quiero realizar el promedio móvil en función de cada 15 minutos de observaciones de temperatura sucesivas. En otras palabras, seleccionando datos para realizar el promedio basado en un intervalo de tiempo de 15 minutos. Además, es posible tener diferentes números de observaciones para diferentes secuencias de tiempo. Quise decir que todos los tamaños de ventana son iguales (15 minutos) pero es posible tener diferentes números de observaciones en cada ventana. Por ejemplo: para una primera ventana tenemos que calcular el promedio de n observación y para la segunda ventana calcular el promedio de la observación para n + 5 observación.

Muestra de datos:

ID Timestamps Temperature 1 2007-09-14 22:56:12 5.39 2 2007-09-14 22:58:12 5.34 3 2007-09-14 23:00:12 5.16 4 2007-09-14 23:02:12 5.54 5 2007-09-14 23:04:12 5.30 6 2007-09-14 23:06:12 5.20 7 2007-09-14 23:10:12 5.39 8 2007-09-14 23:12:12 5.34 9 2007-09-14 23:20:12 5.16 10 2007-09-14 23:24:12 5.54 11 2007-09-14 23:30:12 5.30 12 2007-09-14 23:33:12 5.20 13 2007-09-14 23:40:12 5.39 14 2007-09-14 23:42:12 5.34 15 2007-09-14 23:44:12 5.16 16 2007-09-14 23:50:12 5.54 17 2007-09-14 23:52:12 5.30 18 2007-09-14 23:57:12 5.20

Retos principales:

Cómo puedo aprender el código para discriminar cada 15 minutos, mientras que no hay intervalos de tiempo exactos de 15 minutos debido a la frecuencia de muestreo diferente.

Answer 1

Aquí hay un enfoque que utiliza la facilidad para usar una función de agregación como una función de ventana. La función agregada conserva los últimos 15 minutos de observaciones en una matriz, junto con el total acumulado actual. La función de transición de estado desplaza los elementos del conjunto que se ha quedado atrás de la ventana de 15 minutos y presiona sobre la observación más reciente. La función final simplemente calcula la temperatura media en la matriz.

Ahora, si esto es un beneficio o no ... depende. Se centra en la parte de ejecución de plgpsql de postgresql en lugar de la parte de acceso a la base de datos, y mi propia experiencia es que plpgsql no es rápido. Si puede hacer búsquedas fácilmente de vuelta a la tabla para buscar las filas de 15 minutos anteriores para cada observación, una auto-unión (como en la respuesta @danihp) le irá bien. Sin embargo, este enfoque puede tratar las observaciones provenientes de una fuente más compleja, donde esas búsquedas no son prácticas. Como siempre, pruebe y compare en su propio sistema.

-- based on using this table definition
create table observation(id int primary key, timestamps timestamp not null unique,
                         temperature numeric(5,2) not null);

-- note that I''m reusing the table structure as a type for the state here
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);

-- referring to just a table name means a tuple value of the row as a whole, whose type is the table type
-- the aggregate relies on inputs arriving in ascending timestamp order
select rollavg(observation) over (order by timestamps) from observation;

Answer 2

Puede unirse a su mesa consigo mismo:

select l1.id, avg( l2.Temperature )
from l l1
inner join l l2 
   on l2.id <= l1.id and
      l2.Timestamps + interval ''15 minutes'' > l1.Timestamps
group by l1.id
order by id
;

Resultados :

| ID |            AVG |
-----------------------
|  1 |           5.39 |
|  2 |          5.365 |
|  3 | 5.296666666667 |
|  4 |         5.3575 |
|  5 |          5.346 |
|  6 | 5.321666666667 |
|  7 | 5.331428571429 |

Aviso: solo se hace ''trabajo duro''. Debería unir el resultado con la tabla original o anexar nuevas columnas para consultar. No sé tu consulta final necesaria. Adapte esta solución o solicite más ayuda.

Answer 3

Suponiendo que desea reiniciar el promedio móvil después de cada intervalo de 15 minutos:

select id, 
       temp,
       avg(temp) over (partition by group_nr order by time_read) as rolling_avg
from (       
  select id, 
         temp,
         time_read, 
         interval_group,
         id - row_number() over (partition by interval_group order by time_read) as group_nr
  from (
    select id, 
           time_read, 
           ''epoch''::timestamp + ''900 seconds''::interval * (extract(epoch from time_read)::int4 / 900) as interval_group,
           temp
    from readings
  ) t1
) t2
order by time_read;

Se basa en la solución de Depesz para agrupar por "intervalos de tiempo":

Aquí hay un ejemplo de SQLFiddle: http://sqlfiddle.com/#!1/0f3f0/2