Come risolvere le date mancanti per l’analisi delle serie temporali

Risolvere date mancanti per analisi serie temporali

Impara come utilizzare le TVF in BigQuery per generare senza sforzo intervalli di date per l’analisi delle serie storiche.

Il mio obiettivo per questo articolo è aiutarti a capire le TVF e come utilizzarle attraverso un esempio che affronta il problema delle date mancanti comune nell’analisi delle serie storiche.

In alcune situazioni, le date senza dati sono importanti e devono essere mostrate/incluse nel tuo dataset. Ad esempio:

• Le aziende possono trarre vantaggio dall’identificare i giorni senza vendite al dettaglio. Questi giorni sono influenzati dalle festività o dai cambiamenti nel comportamento dei clienti.
• Trovare le date mancanti nei dati aiuta a migliorare la qualità dei dati rivelando anomalie o valori anomali causati da malfunzionamenti del sistema o da una cattura dati incompleta. Mostrare le date mancanti è uno strumento utile per raggiungere questo obiettivo.

Queste date mancanti possono causare problemi per l’analisi e la visualizzazione. Pertanto, è necessaria una soluzione che garantisca la presenza di tutte le date nell’output, anche se non ci sono dati corrispondenti.

• Apple entra nella corsa dell’IA generativa con il chatbot ‘AppleGPT
• Nuovo metodo di intelligenza artificiale di Microsoft per prevedere come si muovono e funzionano le molecole
• Ricercatori del KAIST e di Google hanno introdotto la distillazione del punteggio collaborativo (CSD) un metodo di intelligenza artificiale che estende il singolare del modello di diffusione del testo-all’immagine per una sintesi visiva coerente.

Alla fine di questo articolo, avrai la tua TVF personale, in grado di generare tutto questo…

Da una singola riga di codice!

Verremo a trattare:

• Come generare date per colmare le lacune nei dati
• Come creare una TVF e l’uso dei parametri
• Come chiamare una TVF
• Esamineremo l’ampliamento del nostro generatore di date per una maggiore flessibilità.
• Infine, condividerò come è possibile accedere alla mia TVF e ti presenterò un progetto open-source chiamato BigFunctions.

Il Problema

Considera questo scenario: hai eseguito una query che fornisce i risultati delle risposte totali al sondaggio per data delle ultime quattro settimane. Poi, porti i risultati in Google Sheets per visualizzare rapidamente i dati.

Il grafico sopra non evidenzia alcun dato mancante; appare esattamente come previsto. Anche se scegli di visualizzare tutte le date sull’asse x, puoi perdonarti se non noti i due giorni mancanti a luglio.

Come Possiamo Risolvere Questo Problema

Prima di entrare nell’argomento delle TVF, parliamo del mio metodo per risolvere un problema del genere e perché l’ho incluso in una TVF.

Per risolvere questo problema, creo ciò che mi piace chiamare un Asse delle Date. Questa colonna di date/settimane/mesi, qualsiasi periodo tu abbia bisogno, viene costruita separatamente dal dataset che stai analizzando. In questo modo, le date sono indipendenti e non dipendono dall’esistenza dei dati.

La creazione dell’asse delle date è abbastanza semplice, anche se può risultare noiosa se devi crearne frequentemente uno.

Ecco un semplice esempio che genera date dal 19 giugno al 16 luglio 2023.

WITH date_axis as (SELECT  datesFROM  UNNEST(generate_date_array("2023-06-19","2023-07-16")) as dates)SELECT  datesFROM  date_axis

La funzione generate_date_array è la parte chiave di questo codice, ma come suggerisce il nome della funzione, l’output viene restituito come un array. Pertanto, dobbiamo “spianare” (flatten) questo array per il passaggio successivo.

L’asse delle date esiste in una CTE (Common Table Expression) poiché dobbiamo trattarlo come una tabella separata per eseguire una left join con i nostri dati effettivi sulla lista delle date.

WITH date_axis as (SELECT  datesFROM  UNNEST(generate_date_array("2023-06-19","2023-07-16")) as dates)SELECT  dates,  responses as original_responses,  ifnull(responses,0) as new_responsesFROM  date_axis as axisLEFT JOIN  `spreadsheep-20220603.Case_Studies.survey_responses` as survey  ON axis.dates = survey.date

Come puoi vedere sopra, abbiamo dei valori null per il 1° e il 2 luglio nella nostra tabella di survey_responses perché queste date non esistono. Utilizzando l’asse delle date, possiamo facilmente individuarli e gestirli in modo appropriato, che in questo scenario significa sostituire i valori null con 0.

Ridisegnando i nostri dati aggiornati, ora possiamo catturare la mancanza di risposte all’inizio di luglio.

Cosa è esattamente una TVF?

TVF è l’abbreviazione di Table-Valued Function (Funzione con Valore Tabella). Come le UDF (User-Defined Function, Funzioni Definite dall’Utente), permettono di specificare una serie di operazioni che verranno eseguite ogni volta che la funzione personalizzata viene chiamata.

La differenza tra le due è che la UDF restituisce un risultato per ogni riga nel dataset, mentre la TVF restituisce un’intera tabella.

Potresti chiederti qual è il punto se l’approccio CTE fa perfettamente il lavoro. Bene, in una TVF possiamo ampliare le funzionalità e la riutilizzabilità della funzione dell’asse delle date e semplificare il nostro codice.

Esistono molti modi creativi e utili per utilizzare le TVF e in questo articolo ne useremo una per generare un asse delle date.

Creazione di una TVF

CREATE OR REPLACE TABLE FUNCTION `spreadsheep-20220603.Case_Studies.generate_dates`(start_date DATE, end_date DATE)AS (SELECT  datesFROM  UNNEST(generate_date_array(start_date,end_date)) as dates)

Creare la TVF è semplice; inizia con create or replace table function seguito da dove nel tuo progetto vuoi salvare la TVF. Quindi, puoi aggiungere i parametri, ne abbiamo aggiunti due in questo esempio.

start_date DATE, end_date DATE

Come mostrato di seguito, questi due parametri sostituiscono i valori statici che abbiamo aggiunto alla funzione generate_date_array.

unnest(generate_date_array(start_date,end_date)) as dates

Quando la tua TVF è creata, puoi chiamare la tua nuova funzione come se fosse una tabella. Nota che ho aggiunto delle parentesi alla fine della clausola FROM per specificare quali valori voglio che la TVF utilizzi, con il 1° luglio come data di inizio e il 7 luglio come data di fine.

SELECT   dates FROM   `spreadsheep-20220603.Case_Studies.generate_dates`("2023-07-01", "2023-07-07")

Ora possiamo aggiornare la nostra query originale per utilizzare la nuova TVF.

WITH date_axis as (SELECT   dates FROM   `spreadsheep-20220603.Case_Studies.generate_dates`("2023-06-19", "2023-07-16"))SELECT  dates,  responses as original_responses,  ifnull(responses,0) as new_responsesFROM  date_axis as axisLEFT JOIN  `spreadsheep-20220603.Case_Studies.survey_responses` as survey  ON axis.dates = survey.date

Approfondimento sulla TVF

Finora la funzione è piuttosto limitata perché fornisce solo le date. E se volessimo le date di inizio settimana in cui la settimana inizia di domenica, o se volessimo le date di inizio e fine trimestre degli ultimi anni?

Anche se potremmo aggiungere quella logica nella nostra CTE che chiama la TVF, gestiamola invece nella TVF in modo che sia disponibile ogni volta che ne abbiamo bisogno.

La mia versione finale aggiunge alcune altre possibilità a seconda che tu abbia bisogno di intervalli di date settimanali, mensili o trimestrali.

CREATE OR REPLACE TABLE FUNCTION `spreadsheep-20220603.Case_Studies.generate_dates`(start_date DATE, end_date DATE)OPTIONS (description="Genera una tabella di date") AS ((select  date,  format_date("%a", date) as giorno_della_settimana,  date_trunc(date, week(monday)) as inizio_settimana_lunedì,  date_trunc(date, week(monday)) + 6 as fine_settimana_lunedì,  date_trunc(date, week(sunday)) as inizio_settimana_domenica,  date_trunc(date, week(sunday)) + 6 as fine_settimana_domenica,  date_trunc(date, month) as inizio_mese,  date_add(date_trunc(date, month), interval 1 month) - 1 as fine_mese,  date_trunc(date, quarter) as inizio_trimestre,  date_add(date_trunc(date, quarter), interval 1 quarter) - 1 as fine_trimestre,from unnest(  generate_date_array(    start_date,    end_date  )) as date));

In questo modo otteniamo l’output che abbiamo visto all’inizio dell’articolo, dove una singola riga di query può generare date di anni, insieme alle loro parti settimanali, mensili e trimestrali.

Come bonus, questa funzione che abbiamo creato non interroga alcun dato effettivo. Significa che è completamente gratuita da eseguire e anche estremamente veloce.

Anche la generazione di date dal 1820 ad oggi è richiesta solo 1 secondo.

SELECT * FROM `spreadsheep-20220603.Case_Studies.generate_dates`("1820-07-01","2023-07-15")

Accesso alla mia TVF

Per risparmiare tempo, non è necessario creare questa TVF nel tuo progetto; puoi utilizzare la versione pubblica, che esiste nel progetto open source BigFunctions.

Per aggiungere BigFunctions al tuo progetto, puoi utilizzare la funzione di aggiunta dell’esploratore e poi “star a project by name” come mostrato di seguito.

Queste funzioni sono disponibili in ogni regione e all’interno di ogni dataset troverai generate_dates sotto Routines. Prova il codice qui sotto!

SELECT * FROM `bigfunctions.europe_west2.generate_dates`("2022-01-01", "2023-01-01");

Puoi trovare ulteriori dettagli su BigFunctions qui, che è pieno di fantastiche funzioni personalizzate, alcune delle quali utilizzano anche Python per eseguire cose interessanti. Dai un’occhiata se usi BigQuery nel tuo ruolo quotidiano.

Questo conclude l’articolo. Se hai domande, non esitare a commentare e risponderò il prima possibile.

Scrivo frequentemente articoli su BigQuery e Looker Studio. Se sei interessato, considera di seguirmi qui su VoAGI per altro!

Tutte le immagini, salvo diversa indicazione, sono dell’autore.

Rimani elegante, gente! Tom