Alternative dynamique au pivot avec boîtier et groupe par

bien qu'il s'agisse d'une vieille question, je voudrais ajouter une autre solution rendue possible par les récentes améliorations de PostgreSQL. Cette solution atteint le même objectif de retourner un résultat structuré à partir d'un ensemble de données dynamiques sans utiliser la fonction crosstab du tout. en d'autres termes, il s'agit d'un bon exemple de réexamen des hypothèses non intentionnelles et implicites qui nous empêchent de trouver de nouvelles solutions à des problèmes anciens. ;)

à illustrez, vous avez demandé une méthode pour transposer les données avec la structure suivante:

id    feh    bar
1     10     A
2     20     A
3      3     B
4      4     B
5      5     C
6      6     D
7      7     D
8      8     D

dans ce format:

bar  val1   val2   val3
A     10     20 
B      3      4 
C      5        
D      6      7     8

la solution conventionnelle est une approche intelligente (et incroyablement bien informée) pour créer des requêtes dynamiques croisées qui est expliquée en détail exquis dans la réponse D'Erwin Brandstetter.

cependant, si votre cas d'utilisation est suffisamment flexible pour accepter un résultat légèrement différent format, puis une autre solution est possible qui gère les pivots dynamiques magnifiquement. Cette technique, que j'ai appris ici

• tables à Pivot dynamique avec JSON et PostgreSQL

utilise la nouvelle fonction jsonb_object_agg de PostgreSQL pour construire des données pivotées sur la mouche sous la forme d'un objet JSON.

j'utiliserai le test plus simple de M. Brandstetter cas" pour illustrer:

CREATE TEMP TABLE tbl (row_name text, attrib text, val int);
INSERT INTO tbl (row_name, attrib, val) VALUES
   ('A', 'val1', 10)
 , ('A', 'val2', 20)
 , ('B', 'val1', 3)
 , ('B', 'val2', 4)
 , ('C', 'val1', 5)
 , ('D', 'val3', 8)
 , ('D', 'val1', 6)
 , ('D', 'val2', 7);

en utilisant la fonction jsonb_object_agg , nous pouvons créer le jeu de résultats pivotés requis avec cette beauté de pithy:

SELECT
  row_name AS bar,
  json_object_agg(attrib, val) AS data
FROM tbl
GROUP BY row_name
ORDER BY row_name;

qui produit:

 bar |                  data                  
-----+----------------------------------------
 A   | { "val1" : 10, "val2" : 20 }
 B   | { "val1" : 3, "val2" : 4 }
 C   | { "val1" : 5 }
 D   | { "val3" : 8, "val1" : 6, "val2" : 7 }

comme vous pouvez le voir, cette fonction fonctionne en créant des paires clé/valeur dans l'objet JSON à partir des colonnes attrib et value dans les données de l'échantillon, toutes regroupées par row_name .

bien que ce l'ensemble des résultats semble évidemment différent, je crois qu'il satisfera en fait de nombreux (sinon la plupart) cas d'utilisation dans le monde réel, en particulier ceux où les données nécessitent un pivot généré dynamiquement, ou lorsque les données résultantes sont consommées par une application mère (par exemple, doit être reformaté pour la transmission dans une réponse http).

avantages de cette approche:

• syntaxe plus claire. je pense que tout le monde d'accord que la syntaxe pour cette approche est beaucoup plus propre et plus facile à comprendre que même les exemples les plus basiques crosstab.

• Complètement dynamique. aucune information sur les données sous-jacentes ne doit être spécifiée au préalable. Ni les noms des colonnes ni leurs types de données ne doivent être connus à l'avance.

• manipule un grand nombre de colonnes. Puisque les données pivotées sont sauvegardées sous forme d'une seule colonne jsonb, vous ne pourrez pas courir contre la limite de colonne de PostgreSQL (≤1600 colonnes, je crois). Il y a encore une limite, mais je crois que c'est la même que pour les champs de texte: 1 Go par objet JSON créé (corrigez-moi si je me trompe). C'est beaucoup de paires clé/valeur!

• traitement simplifié des données. je crois que la création de données JSON dans le DB simplifiera (et accélérer probablement) le processus de conversion des données dans les applications parent. (Vous noterez que les données entières dans notre exemple de cas de test ont été correctement stockées comme telles dans les objets JSON résultants. PostgreSQL gère cela en convertissant automatiquement ses types de données intrinsèques en JSON conformément à la spécification JSON. Cela éliminera la nécessité de lancer manuellement les données transmises aux applications mères: tout cela peut être délégué à l'analyseur JSON natif de l'application.

différences (et inconvénients possibles):

• ça a l'air différent. il est indéniable que les résultats de cette approche semblent différents. L'objet JSON n'est pas aussi joli que l'ensemble de résultats crosstab; cependant, les différences sont purement cosmétiques. La même information est produite--et dans un format qui est probablement plus convivial pour la consommation par les parents application.

• clés manquantes. les valeurs manquantes dans l'approche crosstab sont remplies avec des NULL, alors que les objets JSON manquent simplement les clés applicables. Vous aurez à décider pour vous même si c'est un compromis acceptable pour votre cas d'utilisation. Il me semble que toute tentative d'aborder ce problème dans PostgreSQL compliquera grandement le processus et impliquera probablement une certaine introspection sous la forme de questions supplémentaires.

• commande de Clés est pas conservé. Je ne sais pas si cela peut être abordé dans PostgreSQL, mais cette question est surtout esthétique aussi, puisque toute application parent sont peu susceptibles de compter sur l'ordre de clé, ou ont la capacité de déterminer l'ordre de clé approprié par d'autres moyens. Le cas le plus défavorable exigera probablement seulement une requête supplémentaire de la base de données.

Conclusion

je suis très curieux d'entendre les opinions des autres (en particulier @Erwinbrandstetter's) sur cette approche, surtout en ce qui concerne la performance. Quand J'ai découvert cette approche sur le blog D'Andrew Bender, c'était comme se faire frapper sur le côté de la tête. Quelle belle façon d'aborder un problème difficile à PostrgeSQL. Il a résolu mon cas d'utilisation parfaitement, et je crois qu'il va également servir à de nombreuses autres.

c'est pour compléter @Damian bonne réponse. J'ai déjà suggéré l'approche JSON dans d'autres réponses avant la fonction json_object_agg . Cela demande juste plus de travail avec l'ensemble d'outils précédent.

deux des inconvénients possibles cités ne sont vraiment pas. L'ordre aléatoire des touches est trivialement corrigé si nécessaire. Les touches manquantes, le cas échéant, prennent une quantité presque insignifiante de code à adresser:

select
    row_name as bar,
    json_object_agg(attrib, val order by attrib) as data
from
    tbl
    right join
    (
        (select distinct row_name from tbl) a
        cross join
        (select distinct attrib from tbl) b
    ) c using (row_name, attrib)
group by row_name
order by row_name
;
 bar |                     data                     
-----+----------------------------------------------
 a   | { "val1" : 10, "val2" : 20, "val3" : null }
 b   | { "val1" : 3, "val2" : 4, "val3" : null }
 c   | { "val1" : 5, "val2" : null, "val3" : null }
 d   | { "val1" : 6, "val2" : 7, "val3" : 8 }

pour un consommateur de requête finale qui comprend JSON il n'y a aucun inconvénient. Le seul est qu'il ne peut pas être consommé comme une source de table.

dans votre cas, je suppose qu'un tableau est bon. violon SQL

select
    bar,
    feh || array_fill(null::int, array[c - array_length(feh, 1)]) feh
from
    (
        select bar, array_agg(feh) feh
        from foo
        group by bar
    ) s
    cross join (
        select count(*)::int c
        from foo
        group by bar
        order by c desc limit 1
    ) c(c)
;
 bar |      feh      
-----+---------------
 A   | {10,20,NULL}
 B   | {3,4,NULL}
 C   | {5,NULL,NULL}
 D   | {6,7,8}

je suis désolé de revenir dans le passé, mais la solution" Dynamic Crosstab " renvoie une table de résultat erronée. Ainsi, les valeurs valN sont par erreur "alignées à gauche" et ne correspondent pas aux noms des colonnes. Lorsque la table d'entrée a des "trous" dans les valeurs, par exemple "C" A val1 et val3 mais pas val2. Cela produit une erreur: la valeur de val3 sera portée dans la colonne val2 (c.-à-d. la colonne libre suivante) dans le tableau final.

CREATE TEMP TABLE tbl (row_name text, attrib text, val int); 
INSERT INTO tbl (row_name, attrib, val) VALUES ('C', 'val1', 5) ('C', 'val3', 7);

SELECT * FROM crosstab('SELECT row_name, attrib, val FROM tbl 
ORDER BY 1,2') AS ct (row_name text, val1 int, val2 int, val3 int);

row_name|val1|val2|val3
 C      |   5|  7 |

pour retourner corriger les cellules avec "trous" dans la colonne de droite, la requête crosstab nécessite un 2nd SELECT dans la crosstab, quelque chose comme ce "crosstab('SELECT row_name, attrib, val FROM tbl ORDER BY 1,2', 'select distinct row_name from tbl order by 1')"