Comment puis-je faire pivoter efficacement les données dans SQL à l'aide de techniques SQL dynamiques ?

Utilisez le SQL dynamique pour implémenter efficacement le pivot de données SQL

Cet article explique comment utiliser la technologie SQL dynamique pour convertir des données tabulaires au format de tableau croisé dynamique, en se concentrant sur l'amélioration de l'efficacité et de la flexibilité de la conversion des données.

Requête initiale

La requête suivante utilise l'instruction CASE et GROUP BY pour implémenter le pivotement des données :

<code class="language-sql">SELECT bar, 
   MAX(CASE WHEN abc."row" = 1 THEN feh ELSE NULL END) AS "val1",
   MAX(CASE WHEN abc."row" = 2 THEN feh ELSE NULL END) AS "val2",
   MAX(CASE WHEN abc."row" = 3 THEN feh ELSE NULL END) AS "val3"
FROM
(
  SELECT bar, feh, row_number() OVER (partition by bar) as row
  FROM "Foo"
 ) abc
GROUP BY bar </code>

Cette méthode est efficace pour faire pivoter les données, mais est moins efficace et moins flexible lorsqu'il s'agit de grands ensembles de données.

Solution améliorée utilisant la fonction Crosstab

fournie par le module tablefunc de crosstab()PostgreSQL peut fournir une solution plus efficace et dynamique.

Installer le module tablefunc

Avant d'utiliser crosstab(), vous devez vous assurer que le module tablefunc a été installé dans la base de données PostgreSQL. Il vous suffit d'exécuter la commande suivante une fois par base de données :

<code class="language-sql">CREATE EXTENSION tablefunc;</code>

Implémentation du tableau croisé

Les crosstab requêtes suivantes peuvent être utilisées pour le pivotement :

<code class="language-sql">SELECT * FROM crosstab(
  'SELECT bar, 1 AS cat, feh
   FROM   tbl_org
   ORDER  BY bar, feh')
 AS ct (bar text, val1 int, val2 int, val3 int);  -- 可根据需要增加列</code>

Instructions :

• tbl_org est la table d'entrée.
La sous-requête dans
• crosstab() sélectionne bar, une catégorie factice cat et feh. La colonne cat sert uniquement d'espace réservé et sera ignorée.
La clause
• ORDER BY garantit que les valeurs sont dans le bon ordre.

Cette requête renverra les données dans le format de tableau croisé dynamique souhaité.

Tableau croisé dynamique

Afin de réaliser des applications plus dynamiques, nous pouvons utiliser des fonctions de fenêtre pour synthétiser les colonnes de catégories :

<code class="language-sql">SELECT * FROM crosstab(
   $$
   SELECT bar, val, feh
   FROM  (
      SELECT *, 'val' || row_number() OVER (PARTITION BY bar ORDER BY feh) AS val
      FROM tbl_org
      ) x
   ORDER BY 1, 2
   $$
 , $$VALUES ('val1'), ('val2'), ('val3')$$         -- 可根据需要增加列
) AS ct (bar text, val1 int, val2 int, val3 int);  -- 可根据需要增加列</code>

Cette requête crée dynamiquement des colonnes de catégories basées sur les valeurs du tableau.

Conclusion

Dans l'ensemble, la fonction crosstab() offre une solution plus efficace et flexible pour pivoter en SQL, simplifiant les requêtes et permettant une personnalisation flexible en fonction des besoins.

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!