Splitting Comma-Separated Values In MySQL

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

Le SQL est l’un des outils les plus puissants de l’analyste. Dans SQL Superstar, nous vous donnons des conseils pratiques pour vous aider à tirer le meilleur parti de ce langage polyvalent et à créer des requêtes magnifiques et efficaces.

De temps en temps, on décide rapidement de stocker les données en les séparant par des virgules, et l’analyste SQL doit ramasser les morceaux pendant l’analyse. Prenons un exemple tiré du propre schéma de Sisense : Chaque tableau de bord de Sisense for Cloud Data Teams comporte une liste, séparée par des virgules, des utilisateurs qui reçoivent ce tableau de bord par courrier électronique chaque jour. Voici à quoi cela ressemble :

Disons que nous voulons faire une analyse simple : Quels utilisateurs reçoivent le plus de tableaux de bord par email ? Si nous utilisons Postgres, regexp_split_to_table vient à la rescousse.

Les utilisateurs de MySQL, cependant, sont dans le noir. Dans ce post, nous allons montrer comment diviser notre chaîne séparée par des virgules en une table de valeurs pour faciliter l’analyse dans MySQL.

Faire un tableau de chiffres

Pour commencer, nous aurons besoin d’un tableau qui contient des chiffres au moins aussi grands que la longueur de notre plus longue liste séparée par des virgules. Nous aimons la fonctionnalité Views de Sisense pour cela, mais à la rigueur, une table temporaire fonctionne également :

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

Joindre notre tableau aux numéros

La prochaine chose que nous voudrons faire est de créer la structure de notre tableau résultant. Nous avons besoin d’une ligne pour chaque adresse e-mail dans chaque liste.

Pour ce faire, joignons la table des numéros à notre table originale des tableaux de bord. Nous utiliserons les nombres pour restreindre le nombre de lignes à la longueur de chaque liste:

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

Prenons cela par morceaux. Le premier est char_length, qui renvoie le nombre de caractères dans une chaîne de caractères. replace(email_recipients, ‘,’, « ) supprime les virgules de email_recipients. Donc char_length(email_recipients) – char_length(replace(email_recipients, ‘,’, « )) compte les virgules dans email_recipients.

En joignant sur le nombre de virgules >= n – 1, nous obtenons exactement le nombre de lignes comme il y a email_recipients !

Voici les résultats :

Sélectionner chaque élément de la liste

Nous avons maintenant la liste dupliquée exactement le bon nombre de fois, et en prime, nous avons une colonne de chiffres que nous pouvons utiliser comme index de tableau !

Il ne nous reste plus qu’à sélectionner l’élément de la liste qui correspond à n. Pour cela, nous allons nous tourner vers la très pratique fonction substring_index de MySQL. Voici le 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 renvoie la sous-chaîne commençant ou se terminant à la i’ième occurrence du délimiteur spécifié, où i est le troisième argument. Nous l’utilisons une fois avec n pour trouver la nième virgule et sélectionner toute la liste après cette virgule.

Puis nous l’appelons à nouveau avec -1 pour trouver la première virgule restante, et sélectionner tout ce qui se trouve à gauche de celle-ci. Avec cette combinaison, nous trouvons la chaîne entière entre la nième et la (n+1)ième virgule. Ce sera le nième destinataire de l’email !

Voici le tableau résultant :

Mise en forme

Maintenant que nous avons nos données schématisées, un simple group-and-count peut nous dire qui sont les principaux utilisateurs de la fonction email !

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

Cela nous donne nos résultats:

Comme nous pouvons le constater, Joel est en tête du peloton !

Voyez notre livre blanc gratuit pour en savoir plus :

.