Dividir valores separados por comas en MySQL

Equipo de Datos de Sisense |Mejores prácticas de BI |3 min read |26 de enero de 2020

SQL es una de las herramientas más poderosas del analista. En SQL Superstar, te damos consejos prácticos para ayudarte a sacar el máximo partido a este versátil lenguaje y crear consultas bonitas y eficaces.

De vez en cuando, se toma una decisión rápida de almacenar los datos separados por comas, y el analista de SQL se ve obligado a recoger los pedazos durante el análisis. Tomemos un ejemplo del propio esquema de Sisense: Cada cuadro de mando de Sisense for Cloud Data Teams tiene una lista separada por comas de los usuarios que reciben ese cuadro de mando por correo electrónico cada día. Esto es lo que parece:

Digamos que queremos hacer un análisis sencillo: ¿Qué usuarios reciben más cuadros de mando por correo electrónico? Si estamos usando Postgres, regexp_split_to_table viene al rescate.

Los usuarios de MySQL, sin embargo, están en la oscuridad. En este post, mostraremos cómo dividir nuestra cadena separada por comas en una tabla de valores para facilitar el análisis en MySQL.

Cómo hacer una tabla de números

Para empezar, necesitaremos una tabla que contenga números al menos tan grandes como la longitud de nuestra lista más larga separada por comas. Nos gusta la función Views de Sisense para esto, pero en un apuro, una tabla temporal también funciona:

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

Unir nuestra tabla a los números

Lo siguiente que vamos a querer hacer es crear la estructura de nuestra tabla resultante. Necesitamos una fila por cada dirección de correo electrónico de cada lista.

Para ello, vamos a unir la tabla de números a nuestra tabla original de cuadros de mando. Usaremos los números para restringir el número de filas a la longitud de cada lista:

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

Tomemos esto por partes. Primero es char_length, que devuelve el número de caracteres de una cadena. replace(email_recipients, ‘,’, «) elimina las comas de email_recipients. Así que char_length(email_recipients) – char_length(replace(email_recipients, ‘,’, «)) cuenta las comas en email_recipients.

Al unir en el número de comas >= n – 1, ¡obtenemos exactamente el número de filas como hay email_recipients!

Aquí están los resultados:

Seleccionando cada elemento de la lista

Ahora tenemos la lista duplicada exactamente el número de veces correcto, y como extra, ¡tenemos una columna de números que podemos usar como índice del array!

Sólo tenemos que seleccionar el elemento de la lista que corresponde a n. Para ello, recurriremos a la práctica función substring_index de MySQL. Este es el 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 devuelve la subcadena que empieza o termina en la i’s ocurrencia del delimitador especificado, donde i es el tercer argumento. Lo usamos una vez con n para encontrar la enésima coma y seleccionamos toda la lista después de esa coma.

Luego lo llamamos de nuevo con -1 para encontrar la primera coma restante, y seleccionamos todo lo que hay a la izquierda de ésta. Con esta combinación, encontramos toda la cadena entre la enésima y la (n+1)ª coma. ¡Ese será el enésimo destinatario del correo electrónico!

Aquí tienes la tabla resultante:

Poniendo todo junto

Ahora que tenemos nuestros datos esquematizados, un simple grupo y recuento puede decirnos quiénes son los principales usuarios de la función de correo electrónico.

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

Esto nos da nuestros resultados:

¡Como podemos ver, Joel lidera el grupo!

Consulta nuestro whitepaper gratuito para saber más: