Phenquestions
Un tableau croisé dynamique est un outil puissant pour estimer, compiler et examiner les données afin de trouver des modèles et des tendances encore plus facilement. Les tableaux croisés dynamiques peuvent être utilisés pour agréger, trier, organiser, réorganiser, regrouper, totaliser ou faire la moyenne des données dans un ensemble de données afin de vraiment comprendre les associations et les dépendances de données. L'utilisation d'un tableau croisé dynamique comme illustration est le moyen le plus simple de démontrer le fonctionnement de cette méthode. PostgreSQL 8.3 a été lancé il y a quelques années, et une nouvelle version nommée 'tablefunc' était ajouté. Tablefunc est un composant qui contient plusieurs méthodes qui produisent des tables (c'est-à-dire plusieurs lignes). Cette modification est livrée avec une gamme très intéressante de fonctionnalités. La méthode du tableau croisé, qui sera utilisée pour créer des tableaux croisés dynamiques, en fait partie. La méthode crosstab prend un argument textuel : une commande SQL qui renvoie des données brutes dans la première mise en page et renvoie un tableau dans la mise en page suivante.
Exemple de tableau croisé dynamique sans TableFunc :
Pour commencer à travailler sur le pivotement de PostgreSQL avec le module 'tablefunc', il faut essayer de faire un tableau croisé dynamique sans lui. Ouvrons donc le shell de ligne de commande PostgreSQL et fournissons les valeurs de paramètre pour le serveur, la base de données, le numéro de port, le nom d'utilisateur et le mot de passe requis. Laissez ces paramètres vides si vous souhaitez utiliser les paramètres sélectionnés par défaut.
Nous allons créer une nouvelle table nommée « Test » dans la base de données « test » avec certains champs, comme indiqué ci-dessous.
>> CREATE TABLE Test(Id int, name varchar(20), sal int, job varchar(20));
Après avoir créé une table, il est temps d'insérer des valeurs dans la table, comme indiqué dans la requête ci-dessous.
>> INSÉRER DANS Test (Id, nom, sal, travail) VALUES (11, 'Aqsa', 45000, 'Writer'), (11, 'Aqsa', 48000, 'Officier'), (11, 'Aqsa', 50000, 'Docteur'), (12, 'Raza', 40000, 'Officier'), (11, 'Raza', 60000, 'Docteur'), (12, 'Raza', 67000, 'Officier'), ( 13, 'Saeed', 85000, 'Écrivain'), (13, 'Saeed', 69000, 'Officier'), (13, 'Saeed', 90000, 'Docteur');
Vous pouvez voir que les données pertinentes ont été insérées avec succès. Vous pouvez voir que cette table a plus d'une des mêmes valeurs pour l'identifiant, le nom et le travail.
>> SÉLECTIONNER * DEPUIS l'entrée ;
Créons un tableau croisé dynamique, qui résumera l'enregistrement de la table 'Test' en utilisant la requête ci-dessous. La commande fusionne les mêmes valeurs de la colonne 'Id' et 'name' dans une ligne tout en prenant la somme des valeurs de la colonne 'salary' pour les mêmes données selon 'Id' et 'name'. Il indique également combien de fois une valeur s'est produite dans l'ensemble particulier de valeurs.
>> SELECT Id, name, sum(sal) sal, sum((job = 'Docteur')::int) Docteur, sum((job = 'Writer')::int) Writer, sum((job = 'Officier ')::int) "Officier" FROM Test GROUP BY Id, name;
Exemple de tableau croisé dynamique avec TableFunc :
Nous commencerons par expliquer notre point principal d'un point de vue réaliste, puis nous décrirons la création du tableau croisé dynamique en étapes que nous aimons. Donc, tout d'abord, il faut ajouter trois tableaux pour travailler sur un pivot. La première table que nous allons créer est « Maquillage », qui stockera des informations concernant les éléments essentiels du maquillage. Essayez la requête ci-dessous dans le shell de ligne de commande pour créer cette table.
>> CRÉER UNE TABLE SI N'EXISTE PAS Makeup(make_Id int PRIMARY KEY, p_name VARCHAR(100) NOT NULL);
Après la création de la table 'Maquillage', ajoutons-y quelques enregistrements. Nous exécuterons la requête ci-dessous dans le shell pour ajouter 10 enregistrements à cette table.
Nous devons créer une autre table nommée « utilisateurs » qui contiendra les enregistrements des utilisateurs utilisant ces produits. Exécutez la requête ci-dessous dans le shell pour créer cette table.
>> CRÉER UNE TABLE SI N'EXISTE PAS users(user_id int PRIMARY KEY, u_name varchar(100) NOT NULL);
Nous avons inséré les 20 enregistrements pour la table 'users' comme indiqué dans l'image ci-dessous.
Nous avons une autre table, 'makeup_user', qui contiendra les enregistrements mutuels des tables 'Makeup' et 'users'. Il a un autre champ, « prix », qui permettra d'économiser le prix du produit. Le tableau a été généré en utilisant la requête indiquée ci-dessous.
>> CREATE TABLE IF NOT EXISTS makeup_user( ID int PRIMARY KEY, Mid int NOT NULL REFERENCES Makeup(make_Id), Uid int NOT NULL REFERENCES users(user_id), price decimal (18,2));
Nous avons inséré un total de 56 enregistrements dans ce tableau, comme le montre l'image.
Créons une vue plus loin pour l'utiliser pour la génération d'un tableau croisé dynamique. Cette vue utilise INNER Join pour faire correspondre les valeurs de colonne de clé primaire des trois tables et récupérer le "nom", le "nom_produit" et le "coût" d'un produit à partir d'une table "clients"
>> CRÉER UNE VUE v_makeup_users COMME SÉLECTIONNÉ c.u_nom, p.p_name, pc.prix FROM utilisateurs c INNER JOIN make-up_user pc ON c.user_id = pc.Uid INNER JOIN Maquillage p ON pc.Milieu = p.make_Id;
Pour l'utiliser, vous devez d'abord installer le package tablefunc pour la base de données que vous souhaitez utiliser. Ce paquet est intégré à PostgreSQL 9.1 et versions ultérieures en exécutant la commande ci-dessous. Le package tablefunc a été activé pour vous maintenant.
>> CRÉER UNE EXTENSION S'IL N'EXISTE PAS tablefunc ;
Après avoir créé l'extension, il est temps d'utiliser la fonction Crosstab() pour créer un tableau croisé dynamique. Nous utiliserons donc la requête suivante dans le shell de ligne de commande pour le faire. Cette requête récupère d'abord l'enregistrement à partir de la « Vue » nouvellement créée. Ces enregistrements seront classés et regroupés par ordre croissant des colonnes 'u_name' et 'p_name'. Nous avons répertorié leur nom de maquillage pour chaque client qu'ils ont acheté et le coût total des produits achetés dans le tableau. Nous avons appliqué l'opérateur UNION ALL sur la colonne 'p_name' pour additionner séparément tous les produits achetés par un client. Cela résumera tous les coûts des produits achetés par un utilisateur en une seule valeur.
Notre tableau croisé dynamique est prêt et affiché dans l'image. Vous pouvez clairement voir que certains espaces de colonne sont vides sous chaque p_name car ils n'ont pas acheté ce produit particulier.
Conclusion:
Nous avons maintenant brillamment appris à créer un tableau croisé dynamique pour résumer les résultats des tableaux avec et sans l'utilisation du package Tablefunc.
