逆透视和PostgreSQL逆透视和PostgreSQL(unpivot and PostgreSQ

有PostgreSQL中unpivot的同等功能？

Answer 1:

创建示例表：

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

你可以“逆透视”或“uncrosstab”使用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;

这将运行在3级不同的子查询foo ，一个是我们要UNPIVOT每一列，并返回，在一个表中，从每个子查询的每个记录。

但是，将扫描表N次，其中N是要unpivot的列数。 这是低效的，当，例如，你有一个非常大的表需要较长的时间来扫描工作的一个大问题。

相反，使用：

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

这是比较容易写，而且将只扫描表一次。

array[a, b, c]返回一个数组对象，具有一个，b和c的值，因为它的元件。 unnest(array[a, b, c])分解的结果到一行的每个阵列的元件。

希望帮助！

Answer 2:

FYI对于我们这些寻求如何在红移UNPIVOT。

通过炖给出的长型的解决方案似乎是实现这一目标的唯一途径。

https://forums.aws.amazon.com/thread.jspa?threadID=126369

---

对于那些谁也看不出它有这里是粘贴下面的文字...

我们没有内置函数，将做旋转或逆转置。 然而，你可以写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) 

UNPIVOT查询

 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) 

希望这有助于尼尔

Answer 3:

你可以使用VALUES()和JOIN LATERAL到unpivot的列。

样本数据：

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);

查询：

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;

DBFiddle演示

使用这种方法，可以在一次逆转置列的多个组。

Answer 4:

我写了一个可怕的逆透视功能的PostgreSQL。 这是相当缓慢的，但它至少返回喜欢你的结果会期望的逆转置操作。

https://cgsrv1.arrc.csiro.au/blog/2010/05/14/unpivotuncrosstab-in-postgresql/

希望你能发现它有用..

Answer 5:

从拉从@a_horse_with_no_name评论的链接略微修改的内容为答案，因为它的工作原理：

安装Hstore如果你没有安装hstore和正在运行的PostgreSQL 9.1+，你可以使用得心应手

CREATE EXTENSION hstore;

对于较低的版本，请在股/的contrib的hstore.sql文件，并在数据库中运行。

假设信号源（例如，宽的数据）表有一个“ID”列中，命名为id_field ，和任意数量的“价值”的列中，所有相同类型的，下面将创建表的非透视图。

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 ; 

这适用于任意数量的“价值”列。 所有的结果值将是文本，除非你施放，例如， (h).value::numeric 。

文章来源: unpivot and PostgreSQL