使用PostgreSQL创建数据透视表 [英] Create a pivot table with PostgreSQL

查看:511
本文介绍了使用PostgreSQL创建数据透视表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

假设我在Postgres中有一个名为 listing 的表格,如下所示:

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

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


推荐答案

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

First compute the average with the aggregate function 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:

  • PostgreSQL Crosstab Query

这篇关于使用PostgreSQL创建数据透视表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆