如何使用 crosstab() 函数在 PostgreSQL 中创建数据透视表？

使用PostgreSQL和crosstab()函数创建透视表

数据分析经常涉及探索不同变量之间的关系。透视表是一种强大的工具，可以对数据进行汇总和呈现，从而轻松识别趋势和模式。本文演示如何使用PostgreSQL创建透视表。

问题：

假设您在PostgreSQL中有一个名为listings的表，包含以下列：

• id
• neighborhood（街区）
• bedrooms（卧室数量）
• price（价格）

您需要创建一个交叉表查询，显示每个街区的平均价格，其中卧室数量作为列，街区作为行。输出应采用以下格式：

街区	0	1	2	3
市中心	189000	325000	-	450000
河景	250000	300000	350000	-

解决方案：

要在PostgreSQL中创建透视表，您可以结合使用AVG()聚合函数和tablefunc模块提供的crosstab()函数。以下是分步指南：

1. 计算每个卧室数量的平均价格：

<code class="language-sql">SELECT neighborhood, bedrooms, AVG(price)
FROM listings
GROUP BY 1, 2
ORDER BY 1, 2;</code>

2. 创建交叉表查询：

<code class="language-sql">SELECT *
FROM crosstab(
   'SELECT neighborhood, bedrooms, AVG(price)::INT
    FROM listings
    GROUP BY 1, 2
    ORDER BY 1, 2;'

  , $$SELECT UNNEST('{0,1,2,3}'::INT[])::TEXT$$
   ) AS ct ("neighborhood" TEXT, "0" INT, "1" INT, "2" INT, "3" INT);</code>

解释：

• 第一个查询计算每个街区每个卧室数量的平均价格。
• crosstab()函数将第一个查询的结果作为输入，并创建一个透视表。传递给crosstab()函数的字符串指定要放在标题中的列值。
• ::INT强制转换用于将AVG()结果转换为整数，以便在输出中获得四舍五入的值。

附加说明：

• 必须安装tablefunc模块才能使用crosstab()函数。
• 您还可以将FILTER子句与聚合函数一起使用来创建透视表，但它通常比crosstab()函数慢。
• PostgreSQL中有多种其他方法和扩展可用于创建透视表。请参阅文档以找到最适合您特定需求的解决方案。

以上是如何使用 crosstab() 函数在 PostgreSQL 中创建数据透视表？的详细内容。更多信息请关注PHP中文网其他相关文章！