相当于 PostgreSQL 中的 unpivot()

问题描述

PostgreSQL 中是否有 unpivot 等效函数?

Is there a unpivot equivalent function in PostgreSQL?

推荐答案

创建示例表:

CREATE TEMP TABLE foo (id int, a text, b text, c text);
INSERT INTO foo VALUES (1, 'ant', 'cat', 'chimp'), (2, 'grape', 'mint', 'basil');

您可以使用 UNION ALL 来取消透视"或取消交叉表":

You can 'unpivot' or 'uncrosstab' using UNION ALL:

SELECT id,
       'a' AS colname,
       a AS thing
FROM foo
UNION ALL
SELECT id,
       'b' AS colname, 
       b AS thing
FROM foo
UNION ALL
SELECT id, 
       'c' AS colname,
       c AS thing
FROM foo
ORDER BY id;

这会在 foo 上运行 3 个不同的子查询，一个用于我们想要反透视的每一列，并在一个表中返回来自每个子查询的每条记录.

This runs 3 different subqueries on foo, one for each column we want to unpivot, and returns, in one table, every record from each of the subqueries.

但这会扫描表 N 次，其中 N 是您要取消透视的列数.这是低效的，并且是一个大问题，例如，当您处理一个需要很长时间扫描的非常大的表时.

But that will scan the table N times, where N is the number of columns you want to unpivot. This is inefficient, and a big problem when, for example, you're working with a very large table that takes a long time to scan.

改为使用:

SELECT id,
       unnest(array['a', 'b', 'c']) AS colname,
       unnest(array[a, b, c]) AS thing
FROM foo
ORDER BY id;

这样更容易写，而且只会扫描表一次.

This is easier to write, and it will only scan the table once.

array[a, b, c] 返回一个数组对象，其中 a、b 和 c 的值作为它的元素.unnest(array[a, b, c]) 将数组的每个元素的结果分成一行.

array[a, b, c] returns an array object, with the values of a, b, and c as it's elements.unnest(array[a, b, c]) breaks the results into one row for each of the array's elements.

希望有帮助！

这篇关于相当于 PostgreSQL 中的 unpivot()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！