Il modo migliore per contare i record per intervalli di tempo arbitrari in Rails + Postgres

La mia app ha una tabella degli Events con Events con timestamp.

Devo riportare il conteggio degli eventi durante ciascuno degli intervalli di tempo N più recenti. Per rapporti diversi, l’intervallo può essere “ogni settimana” o “ogni giorno” o “ogni ora” o “ogni intervallo di 15 minuti”.

Ad esempio, un utente può visualizzare quanti ordini hanno ricevuto ogni settimana, giorno o ora o quarto d’ora.

1) La mia preferenza è di fare dynamicmente una singola query SQL (sto usando Postgres) che raggruppa per un intervallo di tempo arbitrario. C’è un modo per farlo?

2) Un modo semplice ma brutto di forza bruta consiste nel fare una singola query per tutti i record entro il timeframe di inizio / fine ordinati per data / ora, quindi avere un metodo manualmente per build un conteggio con qualsiasi intervallo.

3) Un altro approccio sarebbe aggiungere campi separati alla tabella degli eventi per ogni intervallo e memorizzare staticamente un the_week the_day , the_hour , e the_quarter_hour campo così prendo il ‘hit’ al momento della creazione del record (una volta) invece di ogni volta che riferire su quel campo.

Qual è la migliore pratica qui, dato che potrei modificare il modello e pre-memorizzare i dati dell’intervallo se necessario (anche se con la modica spesa di raddoppiare la larghezza della tabella)?

Fortunatamente stai usando PostgreSQL. La funzione window generate_series() è tua amica.

Test case

Data la seguente tabella di test (che dovresti fornire):

 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 ogni 7 minuti (più da 0 a 7 minuti, a caso).

Soluzione di base

Questa query conta gli eventi per qualsiasi intervallo di tempo arbitrario. 17 minuti nell’esempio:

 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 query recupera ts minimi e massimi dalla tabella di base per coprire l'intervallo di tempo completo. Puoi invece utilizzare un intervallo di tempo arbitrario.

  • Fornire qualsiasi intervallo di tempo, se necessario.

  • Produce una riga per ogni fascia oraria. Se nessun evento si è verificato durante quell'intervallo, il conteggio è 0 .

  • Assicurati di gestire correttamente il limite superiore e inferiore :

    • Risultati imprevisti dalla query SQL con timestamp BETWEEN
  • La funzione di finestra lead() ha una caratteristica spesso trascurata: può fornire un valore predefinito quando non esiste alcuna riga principale. Fornire 'infinity' nell'esempio. Altrimenti l'ultimo intervallo verrebbe tagliato con un NULL limite superiore.

Equivalente minimo

La query precedente utilizza una syntax CTE e lead() e dettagliata. Elegante e forse più facile da capire, ma un po 'più costoso. Ecco una versione più breve, più veloce, minimale:

 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; 

Esempio per "ogni 15 minuti della settimana passata" `

E formattazione 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; 

Ancora ORDER BY e GROUP BY sul valore di timestamp sottostante, non sulla stringa formattata. È più veloce e più affidabile.

db <> fiddle qui

Risposta correlata che produce un conteggio in esecuzione nell'arco di tempo:

  • PostgreSQL: conteggio delle righe in esecuzione per una query "per minuto"