Negli articoli precedenti, BigQuery: clausola WITH e BigQuery: funzioni GIS e Geo Vis, abbiamo visto alcune delle funzionalità di BigQuery. In particolare, abbiamo analizzato il costrutto WITH per migliorare la leggibilità delle query e dividere il problema in sottoquery. Inoltre, mediante le funzioni GIS abbiamo visto come utilizzare i dati geografici sia per calcolare alcune metriche che per visualizzarli con Geo Vis. Le query che abbiamo scritto, però, non richiedevano delle analisi complesse.
In molti casi, invece, le analisi da effettuare richiedono di partizionare il set di dati iniziali in sottoinsiemi e, per ciascuno di essi, calcolare delle opportune statistiche. Per effettuare questo tipo di operazioni è necessario utilizzare le finestre analitiche.
Per comprendere al meglio la loro sintassi e il loro funzionamento, in questo articolo analizzeremo il dataset del bike sharing della città di Londra per estrarre alcune informazioni che possono essere utili per il gestore del servizio.
Dataset
Il dataset che verrà usato negli scenari successivi è relativo al servizio di bike sharing della città di Londra. Come già descritto nell’articolo BigQuery: funzioni GIS e Geo Vis, esso è composto da due tabelle, una relativa a tutti i viaggi percorsi dalle biciclette della flotta, l’altra contenente le informazioni specifiche delle stazioni (tra cui quelle geografiche).
Il numero di viaggi registrati all’interno del dataset è di circa 24 milioni relativi ai percorsi di un centinaio di stazioni.
Scenario 1: utilizzo delle stazioni
La prima analisi che vogliamo effettuare è relativa al tempo medio che le biciclette rimangono inattive in ogni stazione prima di essere prelevate da un altro ciclista. Questo potrebbe aiutare, ad esempio, il gestore del servizio ad evitare l’esaurimento delle scorte per le stazioni ad alta domanda. Oppure, il team del marketing potrebbe impostare un programma per incentivare il noleggio di biciclette dalle stazioni a più lento ricambio rispetto a quelle ad alta domanda. Gli usi possono essere veramente molteplici.
Come nell’esempio dell’articolo BigQuery: funzioni GIS e Geo Vis, utilizzeremo una tabella di staging per unire le informazioni delle stazioni a quelle dei viaggi. A questo punto, però, abbiamo bisogno di trovare un modo per “guardare indietro” e osservare la volta precedente che una bicicletta (identificata dal campo bike_id) è stata restituita a quella stazione. Trovato il metodo, sarà sufficiente eseguire la differenza tra i timestamp per ottenere il tempo di inattività.
Per guardare indietro nel tempo, possiamo usare la funzione LAG. Questa funzione restituisce, di default, il valore del campo passato come parametro della riga precedente. È possibile passare anche un parametro di offset per definire il numero di riga precedente da restituire. In questo caso passeremo il campo end_date della struttura bike. Affinché però il ritardo abbia senso per la nostra analisi, dobbiamo usare una funzione analitica per dividere logicamente i dati prima di effettuare il calcolo.
La funzione LAG dovrà essere applicata a diversi sottoinsiemi di dati. Per fare in modo che la funzione non venga applicata al risultato delle clausole WHERE e GROUP BY, ma ad una sua variante, è necessario specificare la clausola OVER. Al suo interno, per suddividere i dati in modo corretto, usiamo la clausola PARTITION BY. In questo caso partizioniamo i dati in base al bike_id ed ordiniamo i dati all’interno di ogni partizione in base alla data di inizio (start_date) dal più vecchio al più recente. Questo ci permette di attraversare i noleggi passati in modo accurato. In figura potete vedere una partizione e come la funzione LAG calcola il risultato.
Infine, applichiamo la funzione TIMESTAMP_DIFF tra l’attuale di inizio noleggio e l’ora di fine dell’ultimo noleggio, per ottenere il tempo in ore in cui la bicicletta è stata inattiva in quella stazione. Possiamo poi aggregare per ogni nome di stazione e calcolare la media dei tempi di inutilizzo. Per completezza calcoliamo anche il numero di noleggi e includiamo il numero di posti disponibili per ogni stazione e la data di installazione della stessa. Ovviamente, per visualizzare anche queste informazioni della stazione bisogna includere gli attributi relativi anche nella clausola di GROUP BY.
Per una maggiore leggibilità, possiamo ordinare i dati in base al tempo di inutilizzo e mostrare i primi 10 risultati.
La query risultante è la seguente.
WITH staging AS (
SELECT
STRUCT(
start_stn.name,
ST_GEOGPOINT(start_stn.longitude, start_stn.latitude) AS point,
start_stn.docks_count,
start_stn.install_date
) AS starting,
STRUCT(
end_stn.name,
ST_GEOGPOINT(end_stn.longitude, end_stn.latitude) AS point,
end_stn.docks_count,
end_stn.install_date
) AS ending,
STRUCT(
rental_id,
bike_id,
duration, -- seconds
ST_DISTANCE(
ST_GEOGPOINT(start_stn.longitude, start_stn.latitude),
ST_GEOGPOINT(end_stn.longitude, end_stn.latitude)
) AS distance, -- meters
ST_MAKELINE(
ST_GEOGPOINT(start_stn.longitude, start_stn.latitude),
ST_GEOGPOINT(end_stn.longitude, end_stn.latitude)
) AS trip_line, -- straight line (for GeoViz)
start_date,
end_date
) AS bike
FROM `bigquery-public-data.london_bicycles.cycle_stations` AS start_stn
LEFT JOIN `bigquery-public-data.london_bicycles.cycle_hire` AS b
ON start_stn.id = b.start_station_id
LEFT JOIN `bigquery-public-data.london_bicycles.cycle_stations` AS end_stn
ON end_stn.id = b.end_station_id
),
lag_end_date AS (
-- Find how long after one ride ends, another one begins (on average)
SELECT
starting.name,
starting.docks_count,
starting.install_date,
bike.bike_id,
LAG(bike.end_date) OVER (
PARTITION BY bike.bike_id
ORDER BY bike.start_date)
AS last_end_date,
bike.start_date,
bike.end_date
FROM staging
)
SELECT
name,
docks_count,
install_date,
COUNT(bike_id) AS total_trips,
ROUND(
AVG(
TIMESTAMP_DIFF(start_date, last_end_date, HOUR)
)
,2) AS time_at_station_hrs
FROM lag_end_date
GROUP BY
name,
docks_count,
install_date
HAVING total_trips > 0
ORDER BY time_at_station_hrs ASC -- fastest turnover first
LIMIT 10; Il risultato che otteniamo è riportato qui sotto.
Scenario 2: manutenzione delle bici
Il gestore del servizio di bike sharing deve anche mantenere in buono stato la sua flotta. Se una bici, infatti, risulta rotta, questa non verrà più utilizzata e, di conseguenza, rappresenterà una perdita per il gestore.
Possiamo basare la nostra analisi sull’utilizzo di ciascuna bicicletta. Quelle più usate avranno una probabilità maggiore di dover essere aggiustate e/o controllate. Partendo dalla query di staging per unire le informazioni dei viaggi con quelle delle stazioni, assegniamo un punteggio a ciascun viaggio di ciascuna bicicletta. Diamo un punteggio pari a uno al primo viaggio fatto, due per il secondo viaggio, e così via. Per fare ciò, usiamo la funzione RANK. Questa deve essere applicata a ciascuna partizione definita mediante l’identificativo della bicicletta (bike_id). Affinché il punteggio sia corretto, è necessario ordinare i dati all’interno di ciascuna partizione in base alla data di inizio del viaggio (bike.start_date).
Oltre a questo punteggio, calcoliamo anche la distanza e la durata cumulative per ogni bicicletta. Usiamo, quindi, la stessa partizione e lo stesso ordinamento per le due nuove misure. La durata, espressa in ore, verrà calcolata mediante la funzione SUM(bike.duration/60/60), mentre la distanza, espressa in km, mediante la funzione di aggregazione SUM(bike.distance/1000). Tutte queste informazioni le raggruppiamo in una struttura stats mediante la clausola STRUCT.
Il risultato è ciò che ci veniva richiesto. Risulta però alquanto dispendioso esaminare i 24 milioni di record per individuare le biciclette che necessitano di manutenzione. Ci serve quindi una classifica generale delle biciclette in base alle loro statistiche senza però perdere i dettagli calcolati fino a questo momento. Come possiamo procedere? La soluzione è l’utilizzo della tipologia di dati ARRAY.
Raggruppiamo il risultato della query precedente in base all’identificativo delle bici assegniamo un punteggio (funzione di RANK) in base alla distanza percorsa. Per ottenere questa informazione, dal momento che abbiamo solo i valori cumulativi, dobbiamo usare la funzione MAX. Il rank sarà quindi applicato sui dati ordinati in base ai valori restituiti dalla funzione MAX(stats.cumulative_distance_km). Questo tipo di finestra definisce una partizione logica e non di colonna come visto in precedenza.
Per definire un array è sufficiente usare la clausola ARRAY_AGG. In questo caso, associamo a ciascuna bicicletta le statistiche dettagliate di ciascun viaggio che abbiamo calcolato in precedenza. Possiamo anche ordinare i dai all’interno dell’array in base alla data di terminazione del viaggio e limitare il numero di elementi presenti al suo interno. Gli array sono estremamente utili in BigQuery poiché permettono di definire livelli multipli di granularità sui dati.
La query finale sarà la seguente.
WITH staging AS (
SELECT
STRUCT(
start_stn.name,
ST_GEOGPOINT(start_stn.longitude, start_stn.latitude) AS point,
start_stn.docks_count,
start_stn.install_date
) AS starting,
STRUCT(
end_stn.name,
ST_GEOGPOINT(end_stn.longitude, end_stn.latitude) AS point,
end_stn.docks_count,
end_stn.install_date
) AS ending,
STRUCT(
rental_id,
bike_id,
duration, -- seconds
ST_DISTANCE(
ST_GEOGPOINT(start_stn.longitude, start_stn.latitude),
ST_GEOGPOINT(end_stn.longitude, end_stn.latitude)
) AS distance, -- meters
ST_MAKELINE(
ST_GEOGPOINT(start_stn.longitude, start_stn.latitude),
ST_GEOGPOINT(end_stn.longitude, end_stn.latitude)
) AS trip_line, -- straight line (for GeoViz)
start_date,
end_date
) AS bike
FROM `bigquery-public-data.london_bicycles.cycle_stations` AS start_stn
LEFT JOIN `bigquery-public-data.london_bicycles.cycle_hire` AS b
ON start_stn.id = b.start_station_id
LEFT JOIN `bigquery-public-data.london_bicycles.cycle_stations` AS end_stn
ON end_stn.id = b.end_station_id
)
-- Collect key stats for each bike on total usage
, maintenance_stats AS (
SELECT
bike.bike_id,
STRUCT(
RANK() OVER(
PARTITION BY bike.bike_id
ORDER BY bike.start_date
) AS current_trip_number,
SUM(bike.duration/60/60) OVER(
PARTITION BY bike.bike_id
ORDER BY bike.start_date
) AS cumulative_duration_hr,
SUM(bike.distance/1000) OVER(
PARTITION BY bike.bike_id
ORDER BY bike.start_date
) AS cumulative_distance_km,
bike.start_date,
bike.end_date
) AS stats
FROM staging
)
SELECT
-- High level summary
RANK() OVER(
ORDER BY MAX(stats.cumulative_distance_km) DESC
) AS most_distance_km_rank,
bike_id,
MAX(stats.cumulative_distance_km) AS distance_travelled,
-- Detail within array (show 10 most recent rides)
ARRAY_AGG(stats ORDER BY stats.end_date DESC LIMIT 10) AS maint_stats
FROM maintenance_stats
GROUP BY bike_id
ORDER BY most_distance_km_rank
LIMIT 10; Mentre il risultato che otteniamo è quello riportato qui sotto. Attenzione che, nonostante sembrino diverse righe, tecnicamente è solo un record. Infatti, le celle a sinistra sono in grigio, il che significa che i valori iniziali sono validi per tutte le tuple dell’array.
