sort - select array_agg postgresql

Agrupar por intervalos de datos

Question

Tengo una sola tabla que almacena el uso del ancho de banda en la red durante un período de tiempo. Una columna contendrá la fecha y hora (clave principal) y otra columna registrará el ancho de banda. Los datos se graban cada minuto. Tendremos otras columnas registrando otros datos en ese momento.

Si el usuario solicita los datos en intervalos de 15 minutos (dentro de un período de 24 horas con fecha de inicio y finalización), ¿es posible con una única consulta obtener los datos que necesito o tendré que escribir un procedimiento / cursor almacenado para hacer esto? ? Los usuarios pueden luego solicitar datos de intervalos de 5 minutos, etc.

Lo más probable es que esté usando Postgres, pero ¿hay otras opciones de NOSQL que serían mejores?

¿Algunas ideas?

Answer 1

select
    date_trunc(''hour'', d) + 
    (((extract(minute from d)::integer / 5 * 5)::text) || '' minute'')::interval
    as "from",
    date_trunc(''hour'', d) + 
    ((((extract(minute from d)::integer / 5 + 1) * 5)::text) || '' minute'')::interval
    - ''1 second''::interval
    as "to",
    sum(random() * 1000) as bandwidth
from 
    generate_series(''2012-01-01'', ''2012-01-31'', ''1 minute''::interval) s(d)
group by 1, 2
order by 1, 2
;

Eso durante 5 minutos varía. Por 15 minutos divide por 15.

Answer 2

WITH t AS (
   SELECT ts, (random()*100)::int AS bandwidth
   FROM   generate_series(''2012-09-01'', ''2012-09-04'', ''1 minute''::interval) ts
   )

SELECT date_trunc(''hour'', ts) AS hour_stump
      ,(extract(minute FROM ts)::int / 15) AS min15_slot
      ,count(*) AS rows_in_timeslice               -- optional
      ,sum(bandwidth) AS sum_bandwidth
FROM   t
WHERE  ts >= ''2012-09-02 00:00:00+02''::timestamptz -- user''s time range
AND    ts <  ''2012-09-03 00:00:00+02''::timestamptz -- careful with borders 
GROUP  BY 1, 2
ORDER  BY 1, 2;

El CTE t proporciona datos como su tabla podría contener: una marca de tiempo ts por minuto con un número de bandwidth . (No necesita esa parte, en su lugar trabaja con su mesa).

Aquí hay una solución muy similar para una pregunta muy similar, con una explicación detallada de cómo funciona esta agregación en particular:

• date_trunc Intervalo de 5 minutos en PostgreSQL

Aquí hay una solución similar para una pregunta similar sobre sumas en ejecución , con explicación detallada y enlaces para las diversas funciones utilizadas:

• PostgreSQL: conteo de filas en ejecución para una consulta ''por minuto''

Pregunta adicional en comentario

WITH -- same as above ...

SELECT DISTINCT ON (1,2)
       date_trunc(''hour'', ts) AS hour_stump
      ,(extract(minute FROM ts)::int / 15) AS min15_slot
      ,bandwidth AS bandwith_sample_at_min15
FROM   t
WHERE  ts >= ''2012-09-02 00:00:00+02''::timestamptz
AND    ts <  ''2012-09-03 00:00:00+02''::timestamptz
ORDER  BY 1, 2, ts DESC;

Recupera una muestra no agregada por intervalo de 15 minutos, desde la última fila disponible en la ventana. Este será el minuto 15 si la fila no falta. Las partes cruciales son DISTINCT ON y ORDER BY .
Más información sobre la técnica utilizada aquí:

• Seleccione la primera fila en cada grupo GROUP BY?