How to Create Pivot Tables in PostgreSQL to Summarize Multidimensional Data?

Mastering Pivot Tables in PostgreSQL for Multidimensional Data Analysis

PostgreSQL offers powerful capabilities for summarizing and analyzing multidimensional data through pivot tables. This technique transforms complex datasets into easily digestible formats, revealing valuable insights.

Let's illustrate with a "listings" table containing columns like "neighborhood," "bedrooms," and "price." To determine the average price for each bedroom count, categorized by neighborhood, follow these steps:

1. Calculating Average Prices:

   Use the AVG() aggregate function to compute the average price for each unique combination of neighborhood and bedroom count:

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

2. Transforming Data with Crosstab:

   Leverage the crosstab() function (requires installing the tablefunc extension). This function pivots the data, setting neighborhoods as rows and bedroom counts as columns:

   <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[])$$
   ) AS ct ("neighborhood" TEXT, "0" INT, "1" INT, "2" INT, "3" INT);</code>

These steps efficiently generate PostgreSQL pivot tables, facilitating data-driven decisions and a deeper understanding of your data.

The above is the detailed content of How to Create Pivot Tables in PostgreSQL to Summarize Multidimensional Data?. For more information, please follow other related articles on the PHP Chinese website!