Comment simuler des tableaux croisés dynamiques dans MySQL à l'aide de UNION ALL et de l'agrégation ?

Simuler un tableau croisé dynamique dans MySQL : utiliser UNION ALL et les fonctions d'agrégation

Les tableaux croisés dynamiques jouent un rôle clé dans l'analyse des données et la génération de rapports, en convertissant les données en colonnes en données en lignes pour obtenir des visualisations plus compactes et plus perspicaces. Bien que MySQL ne fournisse pas directement les fonctions UNPIVOT et PIVOT, nous pouvons intelligemment combiner UNION ALL et agréger des fonctions pour obtenir des fonctions similaires.

Données non pivotantes

La première étape d'un tableau croisé dynamique consiste à développer les données ou à les convertir dans un format plus flexible. Cela implique de créer plusieurs lignes pour chaque colonne de données, chaque ligne représentant une colonne et sa valeur correspondante. Nous pouvons utiliser l'opérateur UNION ALL pour y parvenir :

<code class="language-sql">SELECT id, month, col1 AS value, 'col1' AS descrip
FROM yourTable
UNION ALL
SELECT id, month, col2 AS value, 'col2' AS descrip
FROM yourTable
UNION ALL
SELECT id, month, col3 AS value, 'col3' AS descrip
FROM yourTable
UNION ALL
SELECT id, month, col4 AS value, 'col4' AS descrip
FROM yourTable;</code>

Cette requête générera les résultats suivants :

id	month	value	descrip
101	Jan	A	col1
102	Feb	C	col1
101	Jan	B	col2
102	Feb	A	col2
101	Jan	NULL	col3
102	Feb	G	col3
101	Jan	B	col4
102	Feb	E	col4

Développer le pivotement des données

Une fois les données développées, nous pouvons les faire pivoter à l'aide de fonctions d'agrégation et d'instructions CASE pour créer le format de rapport souhaité :

<code class="language-sql">SELECT descrip,
  MAX(CASE WHEN month = 'Jan' THEN value ELSE 0 END) AS jan,
  MAX(CASE WHEN month = 'Feb' THEN value ELSE 0 END) AS feb
FROM (
    SELECT id, month, col1 AS value, 'col1' AS descrip
    FROM yourTable
    UNION ALL
    SELECT id, month, col2 AS value, 'col2' AS descrip
    FROM yourTable
    UNION ALL
    SELECT id, month, col3 AS value, 'col3' AS descrip
    FROM yourTable
    UNION ALL
    SELECT id, month, col4 AS value, 'col4' AS descrip
    FROM yourTable
) AS src
GROUP BY descrip;</code>

La requête ci-dessus produira les résultats suivants :

descrip	jan	feb
col1	A	C
col2	B	A
col3	0	G
col4	B	E

Ce résultat correspond au format du rapport requis. Grâce à ces technologies, nous pouvons implémenter efficacement les fonctions de tableau croisé dynamique dans MySQL et convertir facilement les données basées sur des colonnes en rapports basés sur des lignes, permettant ainsi une analyse et un reporting plus efficaces.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!