逆透视和 PostgreSQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1128737/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
unpivot and PostgreSQL
提问by Stew
Is there a unpivot equivalent function in PostgreSQL?
PostgreSQL 中是否有逆透视等效函数？
回答by Stew
Create an example table:
创建一个示例表：
CREATE TEMP TABLE foo (id int, a text, b text, c text);
INSERT INTO foo VALUES (1, 'ant', 'cat', 'chimp'), (2, 'grape', 'mint', 'basil');
You can 'unpivot' or 'uncrosstab' using UNION ALL:
您可以使用 UNION ALL 'unpivot' 或 'uncrosstab'：
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;
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.
这在 上运行了 3 个不同的子查询foo，一个用于我们要反透视的每一列，并在一个表中返回每个子查询的每条记录。
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.
但这将扫描表 N 次，其中 N 是您要取消透视的列数。这是低效的，并且是一个大问题，例如，当您正在处理一个需要很长时间扫描的非常大的表时。
Instead, use:
相反，使用：
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]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.
array[a, b, c]返回一个数组对象，其中 a、b 和 c 的值作为它的元素。
unnest(array[a, b, c])将数组的每个元素的结果分成一行。
Hope that helps!
希望有帮助！
回答by Lukasz Szozda
You could use VALUES()and JOIN LATERALto unpivot the columns.
您可以使用VALUES()和JOIN LATERAL来取消旋转列。
Sample data:
样本数据：
CREATE TABLE test(id int, a INT, b INT, c INT);
INSERT INTO test(id,a,b,c) VALUES (1,11,12,13),(2,21,22,23),(3,31,32,33);
Query:
询问：
SELECT t.id, s.col_name, s.col_value
FROM test t
JOIN LATERAL(VALUES('a',t.a),('b',t.b),('c',t.c)) s(col_name, col_value) ON TRUE;
Using this approach it is possible to unpivot multiple groups of columns at once.
使用这种方法可以一次对多组列进行反旋转。
EDIT
编辑
Using Zack'ssuggestion:
使用扎克的建议：
SELECT t.id, col_name, col_value
FROM test t
CROSS JOIN LATERAL (VALUES('a', t.a),('b', t.b),('c',t.c)) s(col_name, col_value);
<=>
SELECT t.id, col_name, col_value
FROM test t
,LATERAL (VALUES('a', t.a),('b', t.b),('c',t.c)) s(col_name, col_value);
回答by camacnei
FYI for those of us looking for how to unpivot in RedShift.
仅供我们这些寻找如何在 RedShift 中逆向旋转的人使用。
The long form solution given by Stewappears to be the only way to accomplish this.
Stew 给出的长格式解决方案似乎是实现这一目标的唯一方法。
For those who cannot see it there, here is the text pasted below:
对于那些看不到它的人，这里是粘贴在下面的文字：
We do not have built-in functions that will do pivot or unpivot. However, you can always write SQL to do that.
create table sales (regionid integer, q1 integer, q2 integer, q3 integer, q4 integer); insert into sales values (1,10,12,14,16), (2,20,22,24,26); select * from sales order by regionid; regionid | q1 | q2 | q3 | q4 ----------+----+----+----+---- 1 | 10 | 12 | 14 | 16 2 | 20 | 22 | 24 | 26 (2 rows)pivot query
create table sales_pivoted (regionid, quarter, sales) as select regionid, 'Q1', q1 from sales UNION ALL select regionid, 'Q2', q2 from sales UNION ALL select regionid, 'Q3', q3 from sales UNION ALL select regionid, 'Q4', q4 from sales ; select * from sales_pivoted order by regionid, quarter; regionid | quarter | sales ----------+---------+------- 1 | Q1 | 10 1 | Q2 | 12 1 | Q3 | 14 1 | Q4 | 16 2 | Q1 | 20 2 | Q2 | 22 2 | Q3 | 24 2 | Q4 | 26 (8 rows)unpivot query
select regionid, sum(Q1) as Q1, sum(Q2) as Q2, sum(Q3) as Q3, sum(Q4) as Q4 from (select regionid, case quarter when 'Q1' then sales else 0 end as Q1, case quarter when 'Q2' then sales else 0 end as Q2, case quarter when 'Q3' then sales else 0 end as Q3, case quarter when 'Q4' then sales else 0 end as Q4 from sales_pivoted) group by regionid order by regionid; regionid | q1 | q2 | q3 | q4 ----------+----+----+----+---- 1 | 10 | 12 | 14 | 16 2 | 20 | 22 | 24 | 26 (2 rows)Hope this helps, Neil
我们没有可以执行旋转或反旋转的内置函数。但是，您始终可以编写 SQL 来执行此操作。
create table sales (regionid integer, q1 integer, q2 integer, q3 integer, q4 integer); insert into sales values (1,10,12,14,16), (2,20,22,24,26); select * from sales order by regionid; regionid | q1 | q2 | q3 | q4 ----------+----+----+----+---- 1 | 10 | 12 | 14 | 16 2 | 20 | 22 | 24 | 26 (2 rows)透视查询
create table sales_pivoted (regionid, quarter, sales) as select regionid, 'Q1', q1 from sales UNION ALL select regionid, 'Q2', q2 from sales UNION ALL select regionid, 'Q3', q3 from sales UNION ALL select regionid, 'Q4', q4 from sales ; select * from sales_pivoted order by regionid, quarter; regionid | quarter | sales ----------+---------+------- 1 | Q1 | 10 1 | Q2 | 12 1 | Q3 | 14 1 | Q4 | 16 2 | Q1 | 20 2 | Q2 | 22 2 | Q3 | 24 2 | Q4 | 26 (8 rows)逆向查询
select regionid, sum(Q1) as Q1, sum(Q2) as Q2, sum(Q3) as Q3, sum(Q4) as Q4 from (select regionid, case quarter when 'Q1' then sales else 0 end as Q1, case quarter when 'Q2' then sales else 0 end as Q2, case quarter when 'Q3' then sales else 0 end as Q3, case quarter when 'Q4' then sales else 0 end as Q4 from sales_pivoted) group by regionid order by regionid; regionid | q1 | q2 | q3 | q4 ----------+----+----+----+---- 1 | 10 | 12 | 14 | 16 2 | 20 | 22 | 24 | 26 (2 rows)希望这会有所帮助，尼尔
回答by Josh LOL
I wrote a horrible unpivot function for PostgreSQL. It's rather slow but it at least returns results like you'd expect an unpivot operation to.
我为 PostgreSQL 编写了一个可怕的逆透视函数。它相当慢，但它至少会像您期望的逆透视操作那样返回结果。
https://cgsrv1.arrc.csiro.au/blog/2010/05/14/unpivotuncrosstab-in-postgresql/
https://cgsrv1.arrc.csiro.au/blog/2010/05/14/unpivotuncrosstab-in-postgresql/
Hopefully you can find it useful..
希望你能发现它有用..
回答by Brian D
Pulling slightly modified content from the link in the comment from @a_horse_with_no_name into an answer because it works:
从@a_horse_with_no_name 评论中的链接中提取稍微修改的内容，因为它有效：
Installing Hstore
If you don't have hstoreinstalled and are running PostgreSQL 9.1+, you can use the handy
安装 Hstore
如果你还没有hstore安装并运行 PostgreSQL 9.1+，你可以使用方便的
CREATE EXTENSION hstore;
CREATE EXTENSION hstore;
For lower versions, look for the hstore.sqlfile in share/contriband run in your database.
对于较低版本，请在数据库中查找并运行该hstore.sql文件share/contrib。
Assuming that your source (e.g., wide data) table has one 'id' column, named id_field, and any number of 'value' columns, all of the same type, the following will create an unpivoted view of that table.
假设您的源（例如，宽数据）表有一个 'id' 列， namedid_field和任意数量的 'value' 列，所有类型都相同，下面将创建该表的非透视图。
CREATE VIEW vw_unpivot
AS
SELECT id_field, (h).key AS column_name, (h).value AS column_value
FROM (SELECT id_field, each(hstore(foo) - 'id_field'::text) AS h
FROM zcta5 as foo
) AS unpiv ;
This works with any number of 'value' columns. All of the resulting values will be text, unless you cast, e.g., (h).value::numeric.
这适用于任意数量的“值”列。所有结果值都将是文本，除非您强制转换，例如(h).value::numeric.
