Como alterar o formulário de tabela para converter linhas em colunas no MySQL

O MySQL é um dos sistemas de gerenciamento de banco de dados relacionais mais populares do mundo. O MySQL nos permite armazenar, gerenciar e analisar dados em forma de tabela. Uma tabela consiste em linhas e colunas que representam entidades e atributos de dados. No entanto, às vezes precisamos alterar a forma da tabela para converter linhas em colunas ou vice-versa. Isso é chamado de transposição de tabela.

As tabelas de transposição podem ser úteis por vários motivos, tais como:

• Simplifique a visualização e a compreensão de dados
• Otimizar o desempenho da consulta e do índice
• Adapte as estruturas de dados às necessidades dos negócios ou dos aplicativos
• Executar operações de agregação ou cálculo em dados

No entanto, o MySQL não tem uma função interna para transpor tabelas diretamente. Portanto, precisamos usar certas técnicas ou truques para alcançar esse objetivo. Neste artigo, discutiremos várias maneiras de alterar a forma de uma tabela para converter linhas em colunas no MySQL, juntamente com exemplos e explicações.

Método 1: Usando a função de agregação e a cláusula GROUP BY

Uma maneira de alterar a forma de uma tabela para converter linhas em colunas no MySQL é usar a função de agregação e a cláusula GROUP BY. Uma função de agregação é uma função que toma um conjunto de valores como entrada e retorna um único valor como saída. Algumas funções de agregação comumente usadas no MySQL são:

• SOMA(): Calcula o número total de um conjunto de valores
• AVG(): Calcula a média de um conjunto de valores
• MIN(): Devolve o menor valor de um conjunto de valores
• MAX(): Devolve o maior valor de um conjunto de valores
• COUNT(): Conta o número de valores presentes em um conjunto de valores

Uma cláusula GROUP BY é uma cláusula usada para agrupar linhas que têm o mesmo valor em uma ou mais colunas. A cláusula GROUP BY é normalmente usada em conjunto com uma função de agregação para calcular valores agregados para cada grupo.

Para alterar o formulário de tabela para converter linhas em colunas usando a função de agregação e a cláusula GROUP BY, precisamos executar as seguintes etapas:

• Especifique a coluna que será a nova linha na tabela de resultados. Esta coluna é geralmente uma coluna que tem valores diferentes e não muitos. Por exemplo, colunas de ano, mês, categoria, tipo, etc.
• Especifique a coluna que será a nova coluna na tabela de resultados. Essa coluna geralmente é uma coluna que tem o mesmo valor ou o valor limitado para cada linha. Por exemplo, colunas de nome, produto, marca, etc.
• Especifique a função de agregação que será usada para calcular o valor de cada nova combinação de linha e coluna. Essa função de agregação depende do tipo de dados e da finalidade de nossa análise. Por exemplo, SUM(), AVG(), MIN(), MAX(), etc.
• Crie uma consulta SQL que use a função de agregação e a cláusula GROUP BY para retornar a tabela de resultados desejada.

Vejamos um exemplo para entender melhor esse caminho. Suponha que tenhamos a seguinte tabela que armazena dados de vendas de produtos para cada mês e categoria:

Esta tabela tem três colunas: mês, categoria e vendas. Queremos alterar a forma dessa tabela para converter linhas em colunas, para que possamos ver o total de vendas para cada categoria e mês em uma tabela. A tabela de resultados que queremos é a seguinte:

Para atingir esse objetivo, podemos usar o caminho 1 com os seguintes passos:

• Especifique a coluna que será a nova linha na tabela de resultados. Nesse caso, a coluna de categoria será uma nova linha, pois tem valores diferentes e não muitos (A, B, C).
• Especifique a coluna que será a nova coluna na tabela de resultados. Nesse caso, a coluna mês será a nova coluna, pois tem o mesmo valor ou valor limitado para cada linha (jan, fev, mar).
• Especifique a função de agregação que será usada para calcular o valor de cada nova combinação de linha e coluna. Nesse caso, usaremos a função SOMA() para calcular o total de vendas para cada categoria e mês.
• Crie uma consulta SQL que use a função de agregação e a cláusula GROUP BY para retornar a tabela de resultados desejada. As consultas SQL que podemos usar são as seguintes:

SELECT
category
SUM(CASE WHEN month = 'Jan' THEN sales ELSE 0 END) AS Jan,
SUM(CASE WHEN month = 'Feb' THEN sales ELSE 0 END) AS Feb,
SUM(CASE WHEN month = 'Mar' THEN sales ELSE 0 END) AS Mar
FROM
sales_table
GROUP BY
category;

Esta consulta faz o seguinte:

• Selecione a coluna de categoria na tabela sales_table
• Use as funções SOMA() e CASO para calcular o total de vendas para cada categoria e mês. CASE é uma instrução condicional que retorna um valor com base em uma determinada condição. Nesta consulta, usamos CASE para verificar o valor mensal e retornar o valor de venda, se apropriado, ou 0 se não. Por exemplo, SUM(CASE WHEN month = ‘Jan’ THEN sales ELSE 0 END) retornará o total de vendas de janeiro para cada categoria, ou 0 se não houver vendas para esse mês.
• Dê um nome de alias para cada nova coluna usando AS. Alias é um nome alternativo que podemos dar a uma coluna ou tabela para facilitar a escrita e a leitura de uma consulta. Nesta consulta, damos um nome de alias para cada nova coluna com um nome de mês correspondente, como Jan, Feb e Mar.
• Use a cláusula GROUP BY para agrupar linhas pela coluna de categoria. A cláusula GROUP BY cria uma linha para cada valor exclusivo na coluna de categoria e calcula um valor agregado para cada outra coluna.

Essa consulta irá gerar a tabela de resultados que desejamos, como mostrado acima.

Caminho 2: Usando a função Pivot

Outra maneira de alterar a forma de uma tabela para converter linhas em colunas no MySQL é usar a função de pivô. A função de pivô é uma função que nos permite alterar a forma de uma tabela girando dados de linhas em colunas ou vice-versa. A função de pivô pode ser usada para transpor tabelas de forma fácil e rápida.

No entanto, o MySQL não tem uma função de pivô integrada que possamos usar diretamente. Portanto, precisamos usar certas extensões ou plugins para adicionar funções dinâmicas ao MySQL. Uma das extensões populares e frequentemente usadas é a Flexviews. Flexviews é uma extensão que fornece funções de atualização pivot, unpivot e incremental para MySQL. O Flexviews pode ser baixado e instalado em seu site oficial: https://github.com/greenlion/swanhart-tools/tree/master/flexviews

Para alterar a forma de uma tabela para converter linhas em colunas usando a função de pivô, precisamos fazer as seguintes etapas:

• Instale e habilite a extensão Flexviews no MySQL. Siga as instruções de instalação fornecidas em seu site oficial.
• Crie uma tabela temporária que contenha os dados que queremos moldar. Tabelas temporárias são tabelas que só existem durante uma sessão do MySQL e são excluídas automaticamente quando a sessão termina. As tabelas temporárias podem ser criadas usando o comando CREATE TEMPORARY TABLE. Por exemplo, podemos criar uma tabela temporária com o nome sales_temp contendo dados da tabela sales_table com o seguinte comando:

CREATE TEMPORARY TABLE sales_temp AS
SELECT * FROM sales_table;

• Crie um modo de exibição que contenha a definição da função de pivô que queremos usar. Um modo de exibição é um objeto de banco de dados que armazena os resultados de uma consulta SQL como uma tabela virtual. Os modos de exibição podem ser criados usando o comando CREATE VIEW. Por exemplo, podemos criar uma exibição com o nome pivot_view que contém a definição da função dinâmica para alterar a forma da tabela sales_temp com o seguinte comando:

CREATE VIEW pivot_view AS
SELECT
FV$Pivot(
'sales_temp', -- o nome da tabela temporária que contém os dados
'category', -- o nome da coluna que será a nova linha
'month', -- o nome da coluna que se tornará a nova coluna
'sales', -- o nome da coluna que contém o valor a ser calculado
'SUM', -- o nome da função de agregação a ser usada
NULL -- nomes de coluna adicionais a serem incluídos na tabela de resultados (opcional)
) AS pivot_result; -- nome do alias para o resultado da função de pivô

• Execute uma consulta SQL para chamar a função de pivô do modo de exibição que foi criado. As consultas SQL que podemos usar são as seguintes:

SELECT * FROM pivot_view;

Essa consulta irá gerar a tabela de resultados que desejamos, como mostrado acima.

Caminho 3: Usando as funções Unpivot e Pivot

A terceira maneira de alterar a forma de uma tabela para converter linhas em colunas no MySQL é usar as funções unpivot e pivot. A função não pivot é a função oposta da função pivot, que altera a forma da tabela girando dados de colunas em linhas. A função unpivot pode ser usada para transpor tabelas de forma mais flexível e dinâmica.

No entanto, o MySQL também não tem uma função unpivot integrada que podemos usar diretamente. Portanto, também precisamos usar certas extensões ou plugins para adicionar funcionalidade unpivot ao MySQL. Uma das extensões populares e frequentemente usadas é a Flexviews que discutimos anteriormente. O Flexviews fornece uma função não pivotada, além de funções de atualização incremental e pivot para MySQL.

Para alterar a forma da tabela para converter linhas em colunas usando as funções unpivot e pivot, precisamos fazer as seguintes etapas:

• Instale e habilite a extensão Flexviews no MySQL, se isso não tiver sido feito antes. Siga as instruções de instalação fornecidas em seu site oficial
• Crie uma tabela temporária que contenha os dados que queremos moldar, se ainda não existiam antes. As tabelas temporárias podem ser criadas usando o comando CREATE TEMPORARY TABLE. Por exemplo, podemos criar uma tabela temporária com o nome sales_temp contendo dados da tabela sales_table com o seguinte comando:

CREATE TEMPORARY TABLE sales_temp AS
SELECT * FROM sales_table;

• Crie um modo de exibição que contenha a definição da função unpivot que desejamos usar. Um modo de exibição é um objeto de banco de dados que armazena os resultados de uma consulta SQL como uma tabela virtual. Os modos de exibição podem ser criados usando o comando CREATE VIEW. Por exemplo, podemos criar uma exibição com o nome unpivot_view que contém uma definição de função não pivot para alterar a forma de uma tabela sales_temp com o seguinte comando:

CREATE VIEW unpivot_view AS
SELECT
FV$Unpivot(
'sales_temp', -- o nome da tabela temporária que contém os dados
'category', -- o nome da coluna que permanecerá na linha
'month', -- o nome da nova coluna que armazenará o nome da coluna antiga
'sales', -- o nome da nova coluna que armazenará os valores da coluna antiga
'month, sales' -- o nome da coluna antiga a ser convertida em uma linha
) AS unpivot_result; -- nome do alias para resultados da função não pivot

• Crie outro modo de exibição que contenha a definição da função de pivô que desejamos usar. Os modos de exibição podem ser criados usando o comando CREATE VIEW. Por exemplo, podemos criar uma exibição com o nome pivot_view que contém a definição da função dinâmica para alterar a forma da tabela unpivot_view com o seguinte comando:

CREATE VIEW pivot_view AS
SELECT
FV$Pivot(
'unpivot_view', -- o nome de exibição que contém os dados
'category', -- o nome da coluna que será a nova linha
'month', -- o nome da coluna que se tornará a nova coluna
'sales', -- o nome da coluna que contém o valor a ser calculado
'SUM', -- o nome da função de agregação a ser usada
NULL -- nomes de coluna adicionais a serem incluídos na tabela de resultados (opcional)
) AS pivot_result; -- nome do alias para o resultado da função de pivô

• Execute uma consulta SQL para chamar a função de pivô do modo de exibição que foi criado. As consultas SQL que podemos usar são as seguintes:

SELECT * FROM pivot_view;

Essa consulta irá gerar a tabela de resultados que desejamos, como mostrado acima.

Conclusão

Neste artigo, discutimos várias maneiras de alterar a forma de uma tabela para converter linhas em colunas no MySQL, juntamente com exemplos e explicações. Algumas das maneiras que podemos usar são:

• Usando funções de agregação e cláusulas GROUP BY
• Usar a função de pivô
• Usar as funções unpivot e pivot

Cada método tem suas vantagens e desvantagens, dependendo da estrutura de dados, da finalidade da análise e de nossas preferências. Podemos escolher a forma que melhor se adapte às nossas necessidades e condições.

Espero que este artigo seja útil e possa ajudá-lo na transposição de tabelas no MySQL. Obrigado por ler este artigo até o final.