Equipa de Dados do Sisense | Melhores Práticas do BI |3 min read |January 26, 2020
SQL é uma das ferramentas mais poderosas do analista. Em SQL Superstar, damos-lhe conselhos accionáveis para o ajudar a tirar o máximo partido desta linguagem versátil e criar consultas bonitas e eficazes.
De vez em quando, é tomada uma decisão rápida de armazenar dados de forma separada por vírgulas, e o analista SQL é deixado a apanhar as peças durante a análise. Tomemos um exemplo do próprio esquema de Sisense: Cada painel da Sisense for Cloud Data Teams tem uma lista separada por vírgulas de utilizadores que recebem esse painel todos os dias por e-mail. Eis o que parece:
Vamos dizer que queremos fazer uma análise simples: Quais os utilizadores que recebem mais painéis de controlo por correio electrónico? Se estamos a utilizar Postgres, regexp_split_to_table vem em socorro.
Utilizadores do MySQL, no entanto, estão no escuro. Neste post, mostraremos como dividir a nossa string separada por vírgulas numa tabela de valores para uma análise mais fácil no MySQL.
Fazer uma tabela de números
Para começar, precisaremos de uma tabela que contenha números pelo menos tão grandes como o comprimento da nossa lista mais longa separada por vírgulas. Gostamos da funcionalidade Sisense’s Views para isto, mas numa pitada, uma tabela temporária também funciona:
create temporary table numbers as ( select 1 as n union select 2 as n union select 3 as n ...)
Joining Our Table To Numbers
A próxima coisa que queremos fazer é criar a estrutura da nossa tabela resultante. Precisamos de uma linha para cada endereço de correio electrónico em cada lista.
Para isso, vamos juntar a tabela de números à nossa tabela original de painéis de instrumentos. Vamos usar os números para restringir o número de linhas ao comprimento de cada lista:
select * from dashboardsjoin numbers on char_length(email_recipients) - char_length(replace(email_recipients, ',', '')) >= n - 1P>Deixemos fazer isto em pedaços. Primeiro é char_length, que devolve o número de caracteres numa cadeia. replace(email_recipients, ‘,’, “) remove as vírgulas de email_recipients. Assim char_length(email_recipients) – char_length(replace(email_recipients, ‘,’, “)) conta as vírgulas em email_recipients.
p> Ao juntar o número de vírgulas >= n – 1, obtemos exactamente o número de filas, uma vez que existem email_recipients!
Aqui estão os resultados:
Selecionando Cada Item da Lista
Agora temos a lista duplicada exactamente o número certo de vezes, e como bónus, temos uma coluna de números que podemos usar como um índice de matriz!
Só precisamos de seleccionar o item da lista que corresponde a n. Para isso, vamos recorrer à função de substring_index do MySQL. Aqui está o 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 - 1substring_index retorna o substring começando ou terminando na i’th ocorrência do delimitador especificado, onde i é o terceiro argumento. Utilizamo-lo uma vez com n para encontrar a nª vírgula e seleccionar toda a lista após essa vírgula.
Então chamamo-lo de novo com -1 para encontrar a primeira vírgula restante, e seleccionar tudo à esquerda dessa vírgula. Com esta combinação, encontramos toda a cadeia entre a n e (n+1)a vírgula. Esse será o enésimo destinatário de correio electrónico!
Aqui está a tabela resultante:
Pondo tudo junto
Agora temos o nosso esquema de dados, um simples grupo e contagem pode dizer-nos quem são os principais utilizadores da funcionalidade de 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 1Isto dá-nos os nossos resultados:
Como podemos ver, Joel está a liderar o pacote!
P>Cheque o nosso whitepaper gratuito para saber mais: