How to Create Pivot Tables in PostgreSQL Using the crosstab() Function?

Create a pivot table using PostgreSQL and the crosstab() function

Data analysis often involves exploring relationships between different variables. Pivot tables are a powerful tool for summarizing and presenting data, making it easy to identify trends and patterns. This article demonstrates how to create a pivot table using PostgreSQL.

Question:

Suppose you have a table named listings in PostgreSQL with the following columns:

• id
• neighborhood(neighborhood)
• bedrooms(number of bedrooms)
• price(Price)

You need to create a crosstab query that displays the average price for each neighborhood, with the number of bedrooms as columns and neighborhoods as rows. The output should be in the following format:

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

Solution:

To create a pivot table in PostgreSQL, you can use the AVG() aggregate functions in combination with the tablefunc functions provided by the crosstab() module. Here's a step-by-step guide:

1. Calculate the average price per number of bedrooms:

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

2. Create a crosstab query:

<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>

Explanation:

• The first query calculates the average price per number of bedrooms in each neighborhood.
The
• crosstab() function takes as input the results of the first query and creates a pivot table. The string passed to the crosstab() function specifies the column value to be placed in the header.
The
• ::INT cast is used to convert the AVG() result to an integer in order to get rounded values ​​in the output.

Additional notes:

• The tablefunc module must be installed to use the crosstab() function.
• You can also use the FILTER clause with an aggregate function to create a pivot table, but it is generally slower than the crosstab() function.
• There are various other methods and extensions in PostgreSQL that can be used to create pivot tables. Please refer to the documentation to find the solution that best suits your specific needs.

The above is the detailed content of How to Create Pivot Tables in PostgreSQL Using the crosstab() Function?. For more information, please follow other related articles on the PHP Chinese website!