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

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

字符串
然后将结果馈送到crosstab()函数，如以下相关答案中详细说明的那样：

• PostgreSQL交叉表查询

另一个使用filter实现的解决方案：

SELECT neighborhood,
   avg(price) FILTER (WHERE bedrooms = 0) AS "0",
   avg(price) FILTER (WHERE bedrooms = 1) AS "1",
   avg(price) FILTER (WHERE bedrooms = 2) AS "2",
   avg(price) FILTER (WHERE bedrooms = 3) AS "3"
FROM listings
GROUP BY neighborhood;

字符串

在Postgres中构建数据透视表的最好方法是CASE表达式。

SELECT neighborhood,
       round(avg((CASE WHEN bedrooms = 0 THEN price END)), 2) AS "0",
       round(avg((CASE WHEN bedrooms = 1 THEN price END)), 2) AS "1",
       round(avg((CASE WHEN bedrooms = 2 THEN price END)), 2) AS "2",
       round(avg((CASE WHEN bedrooms = 3 THEN price END)), 2) AS "3"
FROM listings
GROUP BY neighborhood;

字符串
在问题数据上运行此命令会产生

NEIGHBORHOOD                  0          1          2          3
-------------------- ---------- ---------- ---------- ----------
downtown                 256888     334000       NULL       NULL
riverview                  NULL     505000       NULL       NULL

型