使用 PostgreSQL 创建数据透视表 [英] Create a pivot table with PostgreSQL
本文介绍了使用 PostgreSQL 创建数据透视表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
假设我在 Postgres 中有一个名为 listings
的表,如下所示:
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():
SELECT neighborhood, bedrooms, avg(price)
FROM listings
GROUP BY 1,2
ORDER BY 1,2
然后按照相关答案中的详细说明将结果提供给 crosstab()
函数:
Then feed the result to the crosstab()
function as instructed in great detail in this related answer:
这篇关于使用 PostgreSQL 创建数据透视表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文