Comment puis-je faire pivoter efficacement les données sur plusieurs colonnes à l'aide de tablefunc de PostgreSQL ?

Utilisez Tablefunc pour les données pivot multi-colonnes

Lors du traitement de données contenant plusieurs attributs et mesures, il peut être nécessaire de les convertir du format long au format large pour une analyse efficace. La fonctionnalité tablefunc de PostgreSQL fournit une solution pratique pour de telles conversions. Cependant, il est important de comprendre ses limites lorsque vous travaillez avec plusieurs colonnes pivot.

Dans une réponse à une requête précédente, un utilisateur a demandé des conseils sur l'utilisation de tablefunc pour le pivotement, mais a rencontré des défis lorsqu'il travaillait avec plusieurs colonnes pivot. Étant donné que tablefunc attend des colonnes supplémentaires cohérentes pour chaque nom de ligne, la requête d'origine génère des données incomplètes.

Problème résolu

Pour résoudre ce problème, veillez à respecter l'ordre spécifié par tablefunc :

1. Nom de la ligne : Cette colonne doit toujours apparaître en premier.
2. Colonnes supplémentaires (facultatif) : Toute colonne supplémentaire doit figurer après la colonne du nom de la ligne si nécessaire.
3. Catégorie et Valeur (deux dernières colonnes) : Les colonnes Catégorie et Valeur du pivot doivent être dans cet ordre que les deux dernières colonnes.

Mise en œuvre

Dans l'exemple donné, le résultat souhaité nécessite de pivoter sur deux colonnes (entité et statut). Pour ce faire, la requête a été modifiée comme suit :

<code class="language-sql">SELECT *
FROM crosstab(
   'SELECT entity, timeof, status, ct
    FROM t4
    ORDER BY 1'
 , 'VALUES (1), (0)'
   ) AS ct (
      "Attribute" character
    , "Section" timestamp
    , "status_1" int
    , "status_0" int
      );</code>

En utilisant l'entité comme nom de ligne et en échangeant l'ordre du temps et de l'entité, la requête pivote avec succès sur plusieurs colonnes.

Variations avec différents réglages

Pour la configuration mentionnée dans la réponse, où les données sont triées par localt et Entity , la requête modifiée est la suivante :

<code class="language-sql">SELECT localt, entity
     , msrmnt01, msrmnt02, msrmnt03, msrmnt04, msrmnt05  -- , more?
FROM crosstab(
  'SELECT dense_rank() OVER (ORDER BY localt, entity)::int AS row_name
        , localt, entity -- additional columns
        , msrmnt, val
   FROM test
-- WHERE  ???   -- instead of LIMIT at the end
   ORDER BY localt, entity, msrmnt
-- LIMIT ???'   -- instead of LIMIT at the end
, 'SELECT generate_series(1,5)'  -- more?
   ) AS ct (row_name int, localt timestamp, entity int
          , msrmnt01 float8, msrmnt02 float8, msrmnt03 float8, msrmnt04 float8, msrmnt05 float8 -- , more?
            )
LIMIT 1000  -- ?!</code>

Cette requête utilise dense_rank() pour générer des noms de lignes proxy et inclut une clause WHERE facultative pour filtrer les données avant le traitement. De plus, la condition LIMIT a été supprimée de la sous-requête pour améliorer les performances en traitant uniquement les lignes nécessaires.

Conclusion

En comprenant les limitations et en suivant l'ordre spécifié par tablefunc, vous pouvez efficacement pivoter sur plusieurs colonnes, même pour de grands ensembles de données. N'oubliez pas d'optimiser les requêtes en utilisant les clauses WHERE ou les conditions LIMIT appropriées pour éviter un traitement inutile.

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!