tablas - procedimientos almacenados en postgresql pdf

La mejor manera de contar registros por intervalos de tiempo arbitrarios en Rails+Postgres

Question

Mi aplicación tiene una tabla de Events con Events con marca de tiempo.

Necesito informar el conteo de eventos durante cada uno de los intervalos de tiempo N más recientes. Para diferentes informes, el intervalo puede ser "cada semana" o "cada día" o "cada hora" o "cada intervalo de 15 minutos".

Por ejemplo, un usuario puede mostrar cuántos pedidos recibió cada semana, día u hora o cuarto de hora.

1) Mi preferencia es realizar dinámicamente una sola consulta SQL (estoy usando Postgres) que se agrupa por un intervalo de tiempo arbitrario. ¿Hay una manera de hacerlo?

2) Una forma sencilla pero fea de fuerza bruta es hacer una sola consulta para todos los registros dentro del período de tiempo de inicio / finalización ordenados por marca de tiempo, y luego hacer que un método construya un conteo manualmente en cualquier intervalo.

3) Otro enfoque sería agregar campos separados a la tabla de eventos para cada intervalo y almacenar de forma estática un campo the_week the_day , the_hour , y the_quarter_hour , por lo que tomo el ''hit'' en el momento en que se crea el registro (una vez) en lugar de cada vez que informe sobre ese campo.

¿Cuál es la mejor práctica aquí, dado que podría modificar el modelo y los datos del intervalo de almacenamiento previo si fuera necesario (aunque a expensas de duplicar el ancho de la tabla)?

Answer 1

Por suerte, estás usando PostgreSQL. La función de ventana generate_series() es tu amigo.

Caso de prueba

Dada la siguiente tabla de prueba (que deberías haber proporcionado):

CREATE TABLE event(event_id serial, ts timestamp);
INSERT INTO event (ts)
SELECT generate_series(timestamp ''2018-05-01''
                     , timestamp ''2018-05-08''
                     , interval ''7 min'') + random() * interval ''7 min'';

Un evento por cada 7 minutos (más de 0 a 7 minutos, al azar).

Solucion basica

Esta consulta cuenta eventos para cualquier intervalo de tiempo arbitrario. 17 minutos en el ejemplo:

WITH grid AS (
   SELECT start_time
        , lead(start_time, 1, ''infinity'') OVER (ORDER BY start_time) AS end_time
   FROM  (
      SELECT generate_series(min(ts), max(ts), interval ''17 min'') AS start_time
      FROM   event
      ) sub
   )
SELECT start_time, count(e.ts) AS events
FROM   grid       g
LEFT   JOIN event e ON e.ts >= g.start_time
                   AND e.ts <  g.end_time
GROUP  BY start_time
ORDER  BY start_time;

• La consulta recupera ts mínimo y máximo de la tabla base para cubrir el rango de tiempo completo. Puede utilizar un rango de tiempo arbitrario en su lugar.

• Proporcionar cualquier intervalo de tiempo según sea necesario.

• Produce una fila para cada intervalo de tiempo. Si no ocurrió ningún evento durante ese intervalo, el conteo es 0 .

• Asegúrese de manejar los límites superior e inferior correctamente:

  • Resultados inesperados de la consulta SQL con marcas de tiempo BETWEEN

• La función de ventana lead() tiene una característica que a menudo se pasa por alto: puede proporcionar un valor predeterminado para cuando no existe una fila principal. Proporcionando ''infinity'' en el ejemplo. De lo contrario, el último intervalo se cortaría con un límite superior NULL .

Equivalente mínimo

La consulta anterior utiliza un CTE y una sintaxis detallada y de lead() . Elegante y tal vez más fácil de entender, pero un poco más caro. Aquí hay una versión más corta, más rápida y mínima:

SELECT start_time, count(e.ts) AS events
FROM  (SELECT generate_series(min(ts), max(ts), interval ''17 min'') FROM event) g(start_time)
LEFT   JOIN event e ON e.ts >= g.start_time
                   AND e.ts <  g.start_time + interval ''17 min''
GROUP  BY 1
ORDER  BY 1;

Ejemplo para "cada 15 minutos en la semana pasada" `

Y formatear con to_char() .

SELECT to_char(start_time, ''YYYY-MM-DD HH24:MI''), count(e.ts) AS events
FROM   generate_series(date_trunc(''day'', localtimestamp - interval ''7 days'')
                     , localtimestamp
                     , interval ''15 min'') g(start_time)
LEFT   JOIN event e ON e.ts >= g.start_time
                   AND e.ts <  g.start_time + interval ''15 min''
GROUP  BY start_time
ORDER  BY start_time;

Sigue ORDER BY y GROUP BY en el valor de marca de tiempo subyacente, no en la cadena con formato. Eso es más rápido y más confiable.

db <> violín here

Respuesta relacionada que produce una cuenta corriente durante el período de tiempo:

• PostgreSQL: cuenta corriente de filas para una consulta ''por minuto''