tablas - procedimientos almacenados en postgresql pdf
La mejor manera de contar registros por intervalos de tiempo arbitrarios en Rails+Postgres (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
tsmí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:
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 superiorNULL.
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:
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)?