unpivot和PostgreSQL [英] unpivot and PostgreSQL

问题描述

PostgreSQL中是否有不可更改的等效功能?

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.

希望有帮助！

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