La query che ci hai fornito non è molto scalabile perché dovrà ancora eseguire la scansione dell'intero o di un ampio intervallo di righe. Hai contribuito a ridurre la quantità di dati restituiti, ma credo che aumenterà ancora il carico sul tuo database del necessario. Anche l'utilizzo di MOD non rifletterà i dati in modo accurato in alcuni casi. Ad esempio, se il punto dati che corrisponde alla clausola MOD è un valore anomalo, sarebbe fuorviante mostrare tale valore sul grafico. Invece dovresti usare un qualche tipo di media dei valori grezzi nel periodo di tempo.
Approccio alle viste materializzate
Vorrei utilizzare un set di visualizzazioni materializzate per aggregare i dati grezzi in medie . Ecco un esempio:
create materialized view
my_table_hourly_agg
as
select
min(app_id) as app_id,
avg(price) as price,
min(time) as time
from
my_table
group by
floor(time / (60 * 60 * 1000)) -- group raw data to the nearest hour
Questa soluzione ha il vantaggio che a granularità più elevate non è più necessario interrogare un ampio intervallo di righe. Poiché la vista è materializzata, significa che le righe sono memorizzate e che è possibile accedere come una tabella anziché una normale vista del database in cui la query viene eseguita ogni volta che viene utilizzata la vista. È possibile creare viste materializzate di granularità più ampia dalle viste materializzate di granularità inferiore.
Questa soluzione ha il rovescio della medaglia che la vista materializzata avrà una sorta di latenza di aggiornamento ad essa associata. Devi chiamare refresh materialized view per aggiornare i contenuti della vista materializzata. Puoi farlo su un programma in base alle tue esigenze. L'aggiornamento della visualizzazione potrebbe richiedere del tempo se il numero di righe è elevato.
Puoi provare a utilizzare le viste normali (utilizzando la stessa query di cui sopra), ma non sarà così scalabile.
Approccio tabelle aggregate
Penso che il design ideale - se possibile è creare tabelle per ciascuno dei periodi aggregati e riempirli quando i dati grezzi entrano nel tuo sistema. Ad esempio:
create table my_table_hourly_agg (
first_app_id integer primary key,
avg_price real,
start_time bigint,
num_samples integer, -- we need number of samples to produce a rolling average
constraint uniq_hour_agg unique (start_time) -- one row per hour
)
Quindi quando una nuova riga di dati grezzi entra nel tuo sistema, inserisci simultaneamente il punto dati e aggiorna le tabelle di aggregazione.
begin transaction
insert into
my_table
values ($app_id, $price, $time)
insert into
my_table_hourly_agg (first_app_id, avg_price, start_time, num_samples)
values ($app_id, $price, floor($time / (60 * 60 * 1000)), 1)
on conflict (start_time) -- if there is a conflict that means this is not the first data point for the given hour, and we should update the average price and the number of samples
do update
set avg_price = (avg_price * num_samples + $price) / (num_samples + 1)
set num_samples = num_samples + 1
commit
Con questo design i tuoi aggregati sono immediatamente aggiornati, e hai il vantaggio di query di query a bassa velocità (come le visualizzazioni materializzate), ma questo potenzialmente ingombra il tuo codice ed è molto importante proteggerti con le transazioni evitare che i tuoi aggregati non siano corretti. Potresti potenzialmente scrivere trigger di database per aggiornare le tue tabelle aggregate, ma non ho abbastanza tempo per mostrare un esempio.