Come trovare il prossimo valore non nullo in SQL

È necessario trovare il prossimo valore nonNULL in una serie temporale, ma non sapete come fare. È possibile farlo in SQL? Sì, è possibile! Questo articolo vi mostrerà cosa fare.

Se lavorate con SQL, prima o poi vi troverete di fronte a NULL valori. La presenza di NULLs in un database è quasi inevitabile. Tuttavia, a volte si desidera evitarli nei report. Questo accade spesso quando si analizzano i dati delle serie temporali; i valori NULL significano che non ci sono dati disponibili. L'assenza di dati disponibili nella serie di dati di solito significa che qualche evento si è verificato o non si è verificato.

Quindi, come si fa a trovare il prossimo valore nonNULL in una serie temporale? Prima di rispondere al "come", concentriamoci sul "quando", ovvero "quando avrei bisogno di trovare il prossimo valore nonNULL?". Ecco uno scenario che fornirà una risposta.

Scenario

Supponiamo che stiate analizzando i dati per una piattaforma di freelance chiamata NoBoss che mette in contatto le aziende con i freelance. Le aziende sono alla ricerca di bravi freelance; i freelance sono alla ricerca di buoni posti di lavoro (o gigs, come si dice nel settore). Si lavora con la tabella log che contiene i dati dei freelance, i loro dati di accesso e alcune attività. Ecco le colonne della tabella:

• id - L'ID del record di log e la chiave primaria (PK) della tabella.
• user_id - L'ID dell'utente.
• first_name - Il nome dell'utente.
• last_name - Il cognome dell'utente.
• login_start - L'ora di inizio del login dell'utente.
• login_end - L'ora di fine accesso dell'utente.
• job_id - L'ID del lavoro a cui l'utente ha fatto domanda.
• job_name - Il nome del lavoro a cui l'utente si è candidato.
• category_id - L'ID della categoria del lavoro.
• category_name - Il nome della categoria del lavoro.

Il compito è quello di trovare gli ID e i nomi degli utenti. Inoltre, è necessario ottenere gli orari di inizio e fine del login degli utenti, insieme al lavoro a cui l'utente ha fatto domanda. Infine, è necessaria una nuova colonna denominata profile_category. La categoria del profilo di un utente è determinata dalla categoria del primo lavoro per cui l'utente si candida (ad esempio, se il freelance si candida per un lavoro di "Assistente virtuale", il suo valore category_name è "Assistente virtuale"). In questo modo, la piattaforma NoBoss può fornire ai suoi utenti report e statistiche che li confrontano con altri utenti della stessa categoria.

Sembra complicato? Forse all'inizio no. Ma se si osservano i dati della tabella log e capirete perché il vostro compito non è semplice. Prestate particolare attenzione alla colonna evidenziata.

Capite perché è così difficile? Questa tabella registra ogni accesso. In altre parole, questa tabella contiene una serie temporale, il che significa che ci possono essere record di log in cui l'utente non si è candidato ad alcun lavoro. È necessario escludere tutti i record con i valori NULL nella colonna job_id. Questa è la parte più semplice.

È inoltre necessario mostrare solo la categoria del primo lavoro. L'utente può essere connesso e non candidarsi al primo lavoro per mesi. Ciò significa che si dovranno saltare chissà quanti record con valori NULL prima di raggiungere il record nonNULL che corrisponde al primo lavoro.

Ricordate che per completare questo compito dovrete trovare tutti i valori nonNULL nella colonna job_id e solo il primo valore nonNULL della stessa colonna. Per risolvere questo problema, è necessario utilizzare le funzioni SQL window; se non le conoscete, consultate il nostro corso Window Functions .

Esempio di soluzione: SQL Window Functions

L'uso delle funzioni finestra non è l'unico modo per risolvere questo problema in SQL. Per questo motivo il codice che segue è solo un esempio. Tuttavia, l'uso delle funzioni finestra è il modo più elegante per trovare i valori nonNULL. Ecco quindi il codice:

SELECT	user_id,
		first_name,
		last_name,
		login_start,
		login_end,
		job_name,
		FIRST_VALUE(category_name) OVER (PARTITION BY user_id ORDER BY job_id ASC) AS profile_category
FROM log
WHERE job_name IS NOT NULL;

Cosa fa questo codice? Innanzitutto seleziona diverse colonne dalla tabella logniente di speciale. La parte più interessante è rappresentata dall'uso della funzione FIRST_VALUE() window. Questa funzione restituisce il primo valore di un insieme definito di dati. In questo caso, restituisce il primo valore della colonna category_name.

Tuttavia, non voglio che la prima categoria di lavoro scelta sia la categoria di ogni utente; il primo lavoro dell'utente viene utilizzato solo per quel particolare utente. Per ottenere questo risultato, ho utilizzato la funzione PARTITION BY. Questa definisce la finestra su cui la funzione FIRST_VALUE() lavorerà. Ciò significa che troverà il primo lavoro del primo utente; poi passerà all'utente successivo e troverà il suo primo lavoro e così via.

Si noti che questa operazione viene eseguita sulla colonna job_id in ordine crescente. Perché? Gli ID dei lavori sono assegnati in modo sequenziale ai lavori. Se si ordinano gli ID dei lavori in ordine crescente, significa che l'ID più basso sarà in cima e i valori NULL saranno in fondo. In questo modo, si evita di ottenere un NULL come primo valore nel risultato.

Infine, la clausola WHERE esclude tutti gli accessi per i quali l'utente non si è candidato a nessun lavoro.

Verifichiamo il codice per vedere se restituisce il risultato desiderato:

Il codice sembra funzionare perfettamente! Questo è solo un aspetto dell'analisi delle serie temporali in SQL. Per saperne di più, leggete questo articolo sull'analisi delle serie temporali. Non è necessario conoscere a memoria tutte le funzioni della finestra e la loro sintassi, soprattutto se non le si usa molto spesso. In questi casi, può essere utile il nostro foglio informativo SQL Window Functions . Qui troverete tutte le funzioni di finestra, la loro sintassi ed esempi che ne mostrano il funzionamento.

Attenzione nell'adattare questa query

La query precedente fornisce il quadro generale della soluzione e riesce a trovare il prossimo valore nonNULL. È possibile adattare facilmente questo codice alla serie temporale che si sta analizzando. Tuttavia, è bene prestare attenzione!

La cosa più importante è conoscere i propri dati. Capire se la serie di dati contiene i valori NULL e il loro significato. Nell'esempio precedente, era necessario sapere che l'utente può essere connesso e non aver mai fatto domanda per un lavoro. Per questi dati, avere i valori di NULL non è un errore; è un'informazione che porta a determinate conclusioni.

C'è un altro esempio dell'importanza di conoscere i propri dati. Sapevo che i valori di job_id sono assegnati in modo sequenziale, non casuale. Ho usato questa conoscenza per ordinare i dati e quindi eliminare NULLs dal risultato. È utile anche sapere come vengono trattati i valori NULL quando si ordinano i dati. A seconda dell'ordine, appariranno come primi o ultimi valori nella tabella.

È facile pensare che si possa usare la funzione LAST_VALUE() invece di FIRST_VALUE() e ottenere gli stessi risultati semplicemente ordinando i dati in modo diverso. Vediamo se funziona!

La logica comune dice che basta prendere la nostra query precedente e scriverla in questo modo:

SELECT	user_id,
		first_name,
		last_name,
		login_start,
		login_end,
		job_name,
		LAST_VALUE(category_name) OVER (PARTITION BY user_id ORDER BY job_id DESC ) AS profile_category
FROM log
WHERE job_name IS NOT NULL;

L'unica differenza è in questa riga: LAST_VALUE(category_name) OVER (PARTITION BY user_id ORDER BY job_id DESC ) AS profile_category. Invece di FIRST_VALUE(), ho usato la funzione LAST_VALUE(). L'altra differenza è che la funzione verrà eseguita su job_id in ordine decrescente.

Eseguiamo il codice e analizziamo il risultato per l'utente Suzy Cinque:

In profile_category dovrebbe esserci solo "Analisi dei dati". Perché? Perché Suzy Cinque si è candidata per la prima volta al lavoro di "Analista di dati per lo scrub dei dati". Tuttavia, questa colonna ora mostra anche la categoria "Scrittura". Guardate più da vicino. Gli altri due lavori a cui Suzy Cinque si è candidata sono "Correttore di bozze" e "Ghostwriter per biografie". Entrambi appartengono alla categoria "Scrittura".

Questo codice fornisce solo la categoria del lavoro corrente. Un correttore di bozze è un lavoro di scrittura. Anche un ghostwriter è un lavoro di scrittura. L'analista di dati è, beh, un lavoro di analisi dei dati. Perché è successo? Perché la finestra predefinita è RANGE UNBOUNDED PRECEDING quando si usa ORDER BY. Ciò significa che LAST_VALUE() prenderà in considerazione solo i valori compresi tra la prima riga e la riga corrente.

Non disperate: c'è un modo per farlo funzionare! Il trucco consiste nel definire correttamente la cornice della finestra:

SELECT	user_id,
		first_name,
		last_name,
		login_start,
		login_end,
		job_name,
		LAST_VALUE(category_name) OVER (PARTITION BY user_id ORDER BY job_id DESC ROWS BETWEEN UNBOUNDED PRECEDING
      AND UNBOUNDED FOLLOWING ) AS profile_category
FROM log
WHERE job_name IS NOT NULL;

Il codice modificato prenderà ora in considerazione tutti i valori compresi tra la prima e l'ultima riga. Ciò si ottiene con la seguente clausola: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Guardate il nuovo risultato per Suzy Cinque:

Ora il risultato è di nuovo corretto. Il primo lavoro a cui Suzy Cinque si è candidata è "Analista di dati per lo scrub dei dati"; per questo motivo la categoria del suo profilo sarà sempre "Analisi di dati".

Trovare il prossimo valore nonNULL è solo un aspetto dell'analisi di una serie temporale. Per acquisire maggiore familiarità con le serie temporali e le funzioni finestra, provate a esercitarvi sui dati COVID-19 reali, come in questo articolo.

A proposito di dati temporali, potrebbe essere necessario calcolare la lunghezza di una serie temporale. Non preoccupatevi, ecco un articolo che vi insegna come utilizzare le funzioni finestra per calcolare la lunghezza di una serie.

Volete più aiuto per lavorare con i valori non nulli?

Questo scenario con la piattaforma NoBoss è solo un esempio di come trovare i prossimi valori nonNULL. Utilizzate questo scenario e il codice che ho illustrato come base per un ulteriore apprendimento e pratica. Il nostro corso Window Functions vi darà più struttura e vi aiuterà a conoscere tutte le altre funzioni della finestra. Se siete interessati, ecco tutte le informazioni necessarie sul corso Window Functions , fornite dal nostro Chief Content Officer.

Mettete in pratica ciò che avete imparato qui e buona fortuna nel trovare la strada tra i valori nonNULL nei dati delle serie temporali!