Splitting Comma-Separated Values In MySQL

Sisense Data Team | BI Best Practices |3 min read |January 26, 2020

SQL è uno degli strumenti più potenti per gli analisti. In SQL Superstar, vi diamo consigli utili per aiutarvi a ottenere il massimo da questo linguaggio versatile e creare query belle ed efficaci.

Ogni tanto, viene presa la decisione di memorizzare i dati in modo separato da virgole e l’analista SQL viene lasciato a raccogliere i pezzi durante l’analisi. Prendiamo un esempio dallo schema di Sisense: Ogni dashboard di Sisense for Cloud Data Teams ha una lista separata da virgole di utenti che ricevono quel dashboard via email ogni giorno. Ecco come appare:

Diciamo che vogliamo fare una semplice analisi: Quali utenti ricevono più dashboard via e-mail? Se stiamo usando Postgres, regexp_split_to_table viene in soccorso.

Gli utenti MySQL, invece, sono al buio. In questo post, mostreremo come dividere la nostra stringa separata da virgole in una tabella di valori per una più facile analisi in MySQL.

Fare una tabella di numeri

Per iniziare, abbiamo bisogno di una tabella che contenga numeri grandi almeno quanto la lunghezza della nostra lista più lunga separata da virgole. Ci piace la funzione Views di Sisense per questo, ma in un pizzico, funziona anche una tabella temporanea:

create temporary table numbers as ( select 1 as n union select 2 as n union select 3 as n ...)

Unendo la nostra tabella ai numeri

La prossima cosa che vogliamo fare è creare la struttura della nostra tabella. Abbiamo bisogno di una riga per ogni indirizzo email in ogni lista.

Per fare questo, uniamo la tabella dei numeri alla nostra tabella originale di dashboard. Useremo i numeri per limitare il numero di righe alla lunghezza di ogni lista:

select * from dashboardsjoin numbers on char_length(email_recipients) - char_length(replace(email_recipients, ',', '')) >= n - 1

Prendiamo questo in pezzi. Il primo è char_length, che restituisce il numero di caratteri in una stringa. replace(email_recipients, ‘,’, “) rimuove le virgole da email_recipients. Quindi char_length(email_recipients) – char_length(replace(email_recipients, ‘,’, “)) conta le virgole in email_recipients.

Unendo il numero di virgole >= n – 1, otteniamo esattamente il numero di righe di email_recipients!

Ecco i risultati:

Selezionando ogni elemento della lista

Ora abbiamo la lista duplicata esattamente il giusto numero di volte, e come bonus, abbiamo una colonna di numeri che possiamo usare come indice di un array!

Abbiamo solo bisogno di selezionare l’elemento della lista che corrisponde a n. Per questo, ci rivolgeremo alla comoda funzione substring_index di MySQL. Ecco l’SQL:

select id, substring_index( substring_index(email_recipients, ',', n), ',', -1 ) as emailfrom dashboardsjoin numbers on char_length(email_recipients) - char_length(replace(email_recipients, ',', '')) >= n - 1

substring_index restituisce la sottostringa che inizia o finisce all’i’th occorrenza del delimitatore specificato, dove i è il terzo argomento. Lo usiamo una volta con n per trovare l’ennesima virgola e selezionare l’intera lista dopo quella virgola.

Poi lo chiamiamo di nuovo con -1 per trovare la prima virgola rimanente, e selezionare tutto ciò che sta a sinistra di quella. Con questa combinazione, troviamo l’intera stringa tra l’ennesima e la (n+1)terza virgola. Quello sarà l’ennesimo destinatario dell’email!

Ecco la tabella risultante:

Mettere tutto insieme

Ora che abbiamo schematizzato i nostri dati, un semplice group-and-count ci può dire chi sono i primi utenti della funzione e-mail!

select email, count(1) from ( select id, substring_index( substring_index(email_recipients, ',', n), ',', -1 ) as email from dashboards join numbers on char_length(email_recipients) - char_length(replace(email_recipients, ',', '')) >= n - 1) email_recipients_by_dashboardgroup by 1

Questo ci dà i nostri risultati:

Come possiamo vedere, Joel è in testa!

Guarda il nostro whitepaper gratuito per saperne di più: