Comment créer des tableaux croisés dynamiques dans PostgreSQL à l'aide de la fonction crosstab() ?

Créez un tableau croisé dynamique à l'aide de PostgreSQL et de la fonction crosstab()

L'analyse des données implique souvent d'explorer les relations entre différentes variables. Les tableaux croisés dynamiques sont un outil puissant pour résumer et présenter les données, facilitant l'identification des tendances et des modèles. Cet article montre comment créer un tableau croisé dynamique à l'aide de PostgreSQL.

Question :

Supposons que vous ayez une table nommée listings dans PostgreSQL avec les colonnes suivantes :

• id
• neighborhood(quartier)
• bedrooms(nombre de chambres)
• price(Prix)

Vous devez créer une requête croisée qui affiche le prix moyen pour chaque quartier, avec le nombre de chambres en colonnes et les quartiers en lignes. Le résultat doit être au format suivant :

街区	0	1	2	3
市中心	189000	325000	-	450000
河景	250000	300000	350000	-

Solution :

Pour créer un tableau croisé dynamique dans PostgreSQL, vous pouvez utiliser les fonctions d'agrégation AVG() en combinaison avec les fonctions tablefunc fournies par le module crosstab(). Voici un guide étape par étape :

1. Calculez le prix moyen par nombre de chambres :

<code class="language-sql">SELECT neighborhood, bedrooms, AVG(price)
FROM listings
GROUP BY 1, 2
ORDER BY 1, 2;</code>

2. Créer une requête croisée :

<code class="language-sql">SELECT *
FROM crosstab(
   'SELECT neighborhood, bedrooms, AVG(price)::INT
    FROM listings
    GROUP BY 1, 2
    ORDER BY 1, 2;'

  , $$SELECT UNNEST('{0,1,2,3}'::INT[])::TEXT$$
   ) AS ct ("neighborhood" TEXT, "0" INT, "1" INT, "2" INT, "3" INT);</code>

Explication :

• La première requête calcule le prix moyen par nombre de chambres dans chaque quartier.
La fonction
• crosstab() prend en entrée les résultats de la première requête et crée un tableau croisé dynamique. La chaîne passée à la fonction crosstab() précise la valeur de la colonne à placer dans l'en-tête.
Le cast
• ::INT est utilisé pour convertir le résultat AVG() en un entier afin d'obtenir des valeurs arrondies dans la sortie.

Notes supplémentaires :

• Le module tablefunc doit être installé pour utiliser la fonction crosstab().
• Vous pouvez également utiliser la clause FILTER avec une fonction d'agrégation pour créer un tableau croisé dynamique, mais elle est généralement plus lente que la fonction crosstab().
• Il existe diverses autres méthodes et extensions dans PostgreSQL qui peuvent être utilisées pour créer des tableaux croisés dynamiques. Veuillez vous référer à la documentation pour trouver la solution la mieux adaptée à vos besoins spécifiques.

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!