使用PostgreSQL创建数据透视表 [英] Create a pivot table with PostgreSQL
本文介绍了使用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屋!
查看全文