Implémentation de Pivot et Dynamic Pivot à l’aide de GROUP_CONCAT() dans MySQL / Blogs / Perficient

Introduction

Le pivotement des données est un aspect crucial de la manipulation des données, vous permettant de transformer des lignes en colonnes pour une meilleure analyse. Certaines bases de données (par exemple, SQL Server) prennent en charge nativement les pivots, mais MySQL ne prend malheureusement pas en charge la fonction pivot. La fonction GROUP_CONCAT de MySQL est un outil puissant qui peut être exploité à cette fin. Dans ce blog, nous approfondirons les concepts de Pivot et de Dynamic Pivot, en fournissant des exemples pratiques par étapes.

Comprendre le pivot :

Le pivotement implique la transformation de données basées sur des lignes dans un format basé sur des colonnes, ce qui facilite leur analyse et leur compréhension. La fonction GROUP_CONCAT de MySQL simplifie ce processus en concaténant les valeurs de plusieurs lignes en une seule chaîne.

Considérons un scénario dans lequel nous avons une table nommée données_ventes avec la structure suivante :

CREATE TABLE données_ventes (

id_produit INT,

mois VARCHAR(10),

chiffre d’affaires DECIMAL(10, 2)

);

Ingérons quelques exemples de données :

Pour faire pivoter les données afin de présenter les revenus mensuels de chaque produit, nous pouvons utiliser le GROUP_CONCAT fonction:

SÉLECTIONNER

id_produit,

GROUP_CONCAT(

CONCAT(mois, ‘:’, chiffre d’affaires) ORDER PAR mois

L’avenir du Big Data

Avec quelques conseils, vous pouvez créer une plateforme de données adaptée aux besoins de votre organisation et tirer le meilleur parti de votre capital de données.

Obtenez le guide

) AS revenus_mensuels

DE données_ventes

GROUPE PAR product_id ;

Cette requête regroupe les données par product_id et concatène les revenus mensuels, produisant un résultat où chaque ligne représente un produit unique avec ses revenus mensuels correspondants.

Pivot dynamique :

Dynamic Pivot fait passer le pivotement au niveau supérieur en permettant aux en-têtes de colonnes d’être déterminés dynamiquement au moment de l’exécution. Ceci est particulièrement utile lorsqu’il s’agit d’ensembles de données dont les valeurs des colonnes ne sont pas connues à l’avance.

Considérez un scénario dans lequel vous souhaitez faire pivoter les données de ventes de manière dynamique en fonction des mois distincts présents dans le tableau. Voici comment y parvenir :

DÉFINIR LA SESSION group_concat_max_len = 1000 ;

FIXER @sql = NULL ;

SÉLECTIONNER

GROUP_CONCAT(

CONCAT DISTINCTE(

‘MAX(CASE QUAND mois = « ‘, mois, ‘ » PUIS chiffre d’affaires END) AS « ‘, mois, ‘ »‘

)

)

DANS @sql

DE sales_data ;

FIXER @sql = CONCAT(

‘SELECT product_id, ‘, @sql, ‘ FROM sales_data GROUP BY product_id;’

);

PRÉPARER stmt FROM @sql ;

EXÉCUTER stmt ;

DÉSALLOCATION PREPARE stmt ;

Cette requête regroupe les données par product_id en fonction des mois distincts présents dans le sales_data tableau. Il prépare et exécute ensuite l’instruction, ce qui donne lieu à un tableau croisé dynamique avec des colonnes nommées dynamiquement d’après chaque mois distinct.

Conclusion:

Le pivotement et le pivotement dynamique sont des techniques puissantes dans MySQL qui peuvent améliorer considérablement l’analyse des données. L’utilisation de GROUP_CONCAT, ainsi qu’un classement approprié et une génération SQL dynamique, permettent des transformations flexibles et efficaces des données.