SQL 使用 PostgreSQL 创建数据透视表

Create a pivot table with PostgreSQL

提问by Avishai

Suppose I have a table in Postgres called listingsthat looks like this:

假设我在 Postgres 中有一个名为的表，listings如下所示：

id    neighborhood    bedrooms    price
1     downtown        0           256888
2     downtown        1           334000
3     riverview       1           505000
etc.

How do I write a crosstab query that shows the average price per bedrooms as the columns and neighborhoods as the rows?

我如何编写一个交叉表查询，将每间卧室的平均价格显示为列，将街区显示为行？

The output of the query should look something like this (numbers are made up, columns are the bedrooms):

查询的输出应该是这样的（数字是组成的，列是卧室）：

            0       1       2       3
riverton    250000  300000  350000  -
downtown    189000  325000  -       450000

回答by Erwin Brandstetter

First compute the average with the aggregate function avg():

首先使用聚合函数avg()计算平均值：

SELECT neighborhood, bedrooms, avg(price)
FROM   listings
GROUP  BY 1,2
ORDER  BY 1,2

Then feed the result to the crosstab()function as instructed in great detail in this related answer:

然后crosstab()按照此相关答案中的详细说明将结果提供给函数：

• PostgreSQL Crosstab Query

• PostgreSQL 交叉表查询

回答by Evan Allen

The best way to build pivot tables in Postgres is Case statements.

在 Postgres 中构建数据透视表的最佳方法是 Case 语句。

select neighborhood,
round(avg((case when bedroom = 0 then price else 0 end)),2) as "0",
round(avg((case when bedroom = 1 then price else 0 end)),2) as "1",
round(avg((case when bedroom = 2 then price else 0 end)),2) as "2",
round(avg((case when bedroom = 3 then price else 0 end)),2) as "3",
from listings
group by neighborhood;

This was my output

这是我的输出

NEIGHBORHOOD                  0          1          2          3
-------------------- ---------- ---------- ---------- ----------
downtown                      0     373.38     328.25     524.63
riverview                     0     256.83          0       1341
north                         0     199.15     507.85     986.31