相当于 PostgreSQL 中的 unpivot()

Question

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

您可以使用 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;

这会在 foo 上运行 3 个不同的子查询，每个子查询对应我们要取消透视的每一列，并在一个表中返回每个每个子查询的记录。

但这将扫描表 N 次，其中 N 是要逆透视的列数。 这是低效的，并且是一个大问题，例如，当您正在使用需要很长时间扫描的非常大的表时。

相反，使用：

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] 返回一个数组对象，其中 a、b 和 c 的值作为其元素。
unnest(array[a, b, c])< /a> 将数组每个元素的结果分成一行。

Answer 2

您可以使用 VALUES()并加入横向取消旋转列。

示例数据：

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 Demo

使用这种方法可以同时取消透视多组列。

编辑

使用Zack的建议：

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

db<>fiddle 演示

Answer 3

托马斯·凯勒 (Thomas Kellerer) 的精彩文章此处

使用 Postgres 进行逆透视

有时需要对非规范化表进行规范化 - 与“交叉表”或“透视”操作相反。 Postgres 不支持像 Oracle 或 SQL Server 这样的 UNPIVOT 运算符，但是模拟它非常简单。

采用下表存储每个季度的聚合值：

create table customer_turnover
(
  customer_id   integer,
  q1            integer,
  q2            integer,
  q3            integer,
  q4            integer
);

以及以下示例数据：

customer_id | q1  | q2  | q3  | q4 
------------+-----+-----+-----+----
          1 | 100 | 210 | 203 | 304
          2 | 150 | 118 | 422 | 257
          3 | 220 | 311 | 271 | 269

但我们希望季度是行（因为它们应该在标准化数据模型中）。

在 Oracle 或 SQL Server 中，这可以通过 UNPIVOT 运算符来实现，但这在 Postgres 中不可用。 然而，Postgres 像表一样使用 VALUES 子句的能力使得这实际上非常容易：

select c.customer_id, t.*
from customer_turnover c
  cross join lateral (
     values 
       (c.q1, 'Q1'),
       (c.q2, 'Q2'),
       (c.q3, 'Q3'),
       (c.q4, 'Q4')
  ) as t(turnover, quarter)
order by customer_id, quarter;

将返回以下结果：

customer_id | turnover | quarter
------------+----------+--------
          1 |      100 | Q1     
          1 |      210 | Q2     
          1 |      203 | Q3     
          1 |      304 | Q4     
          2 |      150 | Q1     
          2 |      118 | Q2     
          2 |      422 | Q3     
          2 |      257 | Q4     
          3 |      220 | Q1     
          3 |      311 | Q2     
          3 |      271 | Q3     
          3 |      269 | Q4     

使用标准 UNPIVOT 运算符的等效查询将是：

select customer_id, turnover, quarter
from customer_turnover c
  UNPIVOT (turnover for quarter in (q1 as 'Q1', 
                                    q2 as 'Q2', 
                                    q3 as 'Q3',
                                    q4 as 'Q4'))
order by customer_id, quarter;

Answer 4

供我们这些正在寻找如何在 RedShift 中取消旋转的人参考。

Stew 给出的长格式解决方案似乎是唯一的方法完成这个。

---

对于那些看不到它的人，以下是粘贴的文本：

我们没有可以进行旋转或逆旋转的内置函数。 然而，
你总是可以编写 SQL 来做到这一点。

创建表sales（regionid整数，q1整数，q2整数，q3整数，q4整数）； 
  插入销售值 (1,10,12,14,16), (2,20,22,24,26); 

  按区域 ID 从销售订单中选择 *； 

   区域ID |   q1 |   q2 |   q3 |   q4 
  ----------+----+----+----+---- 
   1 |   10 | 10   12 | 12   14 | 14   16 
   2 |   20 | 20   22 | 22   24 |   26 
  （2 行） 
  

数据透视查询

创建表 sales_pivoted（regionid、quarter、sales） 
  作为 
  从销售额中选择regionid，'Q1'，q1 
  联合所有 
  从销售额中选择regionid，'Q2'，q2 
  联合所有 
  从销售额中选择regionid，'Q3'，q3 
  联合所有 
  从销售额中选择regionid，'Q4'，q4 
  ; 

  按区域 ID、季度从 sales_pivoted 订单中选择 *； 

   区域ID |   季度 |   销售量  
  ----------+---------+-------- 
   1 |   Q1 |   10 
   1 |   Q2 |   12 
   1 |   第三季度 |   14 
   1 |   第四季度 |   16 
   2 |   Q1 |   20 
   2 |   Q2 |   22 号 
   2 |   第三季度 |   24 
   2 |   第四季度 |   26 
  （8行） 
  

逆透视查询

选择regionid，sum(Q1)为Q1，sum(Q2)为Q2，sum(Q3)为Q3，sum(Q4)为Q4 
  从 
  （选择区域ID，  
  案例季度，当“Q1”时，销售额为 0，以 Q1 结束， 
  案例季度为“Q2”，则销售额为 0，以 Q2 结束， 
  案例季度为“Q3”，然后销售额为 0，以 Q3 结束， 
  如果季度为“Q4”，则销售额为 0，则作为 Q4 结束 
  来自 sales_pivoted） 

  按区域 ID 分组 
  按区域 ID 排序； 

   区域ID |   q1 |   q2 |   q3 |   q4  
  ----------+----+----+----+---- 
   1 |   10 | 10   12 | 12   14 | 14   16 
   2 |   20 | 20   22 | 22   24 |   26 
  （2 行） 
  

希望这有帮助，尼尔

Answer 5

只需使用 JSON：

with data (id, name) as (
  values (1, 'a'), (2, 'b')
)
select t.*
from data, lateral jsonb_each_text(to_jsonb(data)) with ordinality as t
order by data.id, t.ordinality;

这会产生

|key |value|ordinality|
|----|-----|----------|
|id  |1    |1         |
|name|a    |2         |
|id  |2    |1         |
|name|b    |2         |

dbfiddle

Answer 6

将 @a_horse_with_no_name 评论中的链接中稍微修改的内容拉入答案，因为它有效：

安装 Hstore
如果您没有安装 hstore 并且运行的是 PostgreSQL 9.1+，则可以使用方便的

CREATE EXTENSION hstore；

对于较低版本，请查找 hstore。在 share/contrib 中创建 sql 文件并在数据库中运行。

假设您的源（例如，宽数据）表有一个名为 id_field 的“id”列，以及任意数量的“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 ; 

这适用于任意数量的“值”列。 所有结果值都将是文本，除非您进行强制转换，例如 (h).value::numeric。

Answer 7

我为 PostgreSQL 编写了一个可怕的 unpivot 函数。 它相当慢，但它至少返回像您期望的逆透视操作一样的结果。

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

希望你会发现它有用..

Answer 8

取决于你想做什么......这样的事情可能会有所帮助。

    with wide_table as (
    select 1 a, 2 b, 3 c
    union all
    select 4 a, 5 b, 6 c
)
select unnest(array[a,b,c]) from wide_table

Answer 9

您可以使用FROM UNNEST()数组处理来UnPivot数据集，并与相关子查询串联（适用于PG 9.4）。

FROM UNNEST() 更强大且更强大。 比使用 FROM (VALUES .... ) 逆透视数据集的典型方法灵活。 这是 b/c FROM UNNEST() 是可变参数（具有 n 元数）。 通过使用相关子查询，消除了对横向 ORDINAL 子句的需要，& Postgres 将生成的并行柱状集保持在正确的顺序中。

顺便说一句，这是快速的——在实际使用中，在 <100 万行的时间内生成 800 万行。 24 核系统上为 15 秒。

WITH _students AS ( /** CTE **/
                  SELECT * FROM
                    (   SELECT 'jane'::TEXT ,'doe'::TEXT , 1::INT 
                         UNION
                        SELECT 'john'::TEXT ,'doe'::TEXT , 2::INT 
                         UNION
                        SELECT 'jerry'::TEXT ,'roe'::TEXT , 3::INT 
                         UNION
                        SELECT 'jodi'::TEXT ,'roe'::TEXT , 4::INT 
                    ) s ( fn, ln, id )
) /** end WITH **/   
SELECT s.id
 , ax.fanm  -- field labels, now expanded to two rows
 , ax.anm  -- field data, now expanded to two rows
 , ax.someval -- manually incl. data
 , ax.rankednum -- manually assigned ranks
 ,ax.genser -- auto-generate ranks
FROM _students s
,UNNEST /** MULTI-UNNEST() BLOCK **/
    (
        ( SELECT ARRAY[ fn, ln ]::text[] AS anm -- expanded into two rows by outer UNNEST()
                  /** CORRELATED SUBQUERY **/
                 FROM _students s2 WHERE s2.id = s.id -- outer relation
         )   
   
        ,( /** ordinal relationship preserved in variadic UNNEST() **/
         SELECT ARRAY[ 'first name', 'last name' ]::text[] -- exp. into 2 rows
                  AS fanm 
         )  
     
        ,( SELECT ARRAY[ 'z','x','y'] -- only 3 rows gen'd, but ordinal rela. kept
                 AS someval
        ) 
   
        ,( SELECT ARRAY[ 1,2,3,4,5   ] -- 5 rows gen'd, ordinal rela. kept.
                  AS rankednum
         ) 

        ,( SELECT ARRAY( /** you may go wild ... **/
                         SELECT generate_series(1, 15, 3 ) 
                         AS genser
                         )
          )
        


   ) ax (  anm, fanm, someval, rankednum , genser  )

;
 

结果集：

+--------+----------------+-----------+----------+---------+-------
|   id   |   fanm         |   anm     | someval  |rankednum| [ etc. ]  
+--------+----------------+-----------+----------+---------+-------
|   2    |   first name   |   john    |   z      |    1    |    .
|   2    |   last name    |   doe     |   y      |    2    |    .
|   2    |   [null]       |  [null]   |   x      |    3    |    .
|   2    |   [null]       |  [null]   |  [null]  |    4    |    .
|   2    |   [null]       |  [null]   |  [null]  |    5    |    .
|   1    |   first name   |   jane    |   z      |    1    |    .
|   1    |   last name    |   doe     |   y      |    2    |    .
|   1    |                |           |   x      |    3    |    .
|   1    |                |           |          |    4    |    .
|   1    |                |           |          |    5    |    .
|   4    |   first name   |   jodi    |   z      |    1    |    .
|   4    |   last name    |   roe     |   y      |    2    |    .
|   4    |                |           |   x      |    3    |    .
|   4    |                |           |          |    4    |    .
|   4    |                |           |          |    5    |    .
|   3    |   first name   |   jerry   |   z      |    1    |    .
|   3    |   last name    |   roe     |   y      |    2    |    .
|   3    |                |           |   x      |    3    |    .
|   3    |                |           |          |    4    |    .
|   3    |                |           |          |    5    |    .
+--------+----------------+-----------+----------+---------+ ----   

Answer 10

这是一种结合其他答案中的 hstore 和 CROSS JOIN 方法的方法。

这是我对类似问题的回答的修改版本，它本身基于https://blog.sql-workbench.eu/post/dynamic-unpivot/ 和 该问题的另一个答案。

-- Example wide data with a column for each year...
WITH example_wide_data("id", "2001", "2002", "2003", "2004") AS (
  VALUES 
    (1, 4, 5, 6, 7),
    (2, 8, 9, 10, 11)
)

-- that is tided to have "year" and "value" columns
SELECT
  id,
  r.key AS year,
  r.value AS value
FROM
  example_wide_data w
CROSS JOIN
  each(hstore(w.*)) AS r(key, value)
WHERE
  -- This chooses columns that look like years
  -- In other cases you might need a different condition
  r.key ~ '^[0-9]{4}
  与其他解决方案相比，它有一些好处：

通过使用 hstore 而不是 jsonb，它有望最大限度地减少类型转换问题（尽管 hstore 确实将所有内容转换为文本）。
列不需要硬编码或提前知道。   在这里，列是通过名称的正则表达式选择的，但您可以根据名称甚至值使用任何 SQL 逻辑。
  它不需要 PL/pgSQL - 都是 SQL

;

与其他解决方案相比，它有一些好处：

• 通过使用 hstore 而不是 jsonb，它有望最大限度地减少类型转换问题（尽管 hstore 确实将所有内容转换为文本）。
• 列不需要硬编码或提前知道。 在这里，列是通过名称的正则表达式选择的，但您可以根据名称甚至值使用任何 SQL 逻辑。
• 它不需要 PL/pgSQL - 都是 SQL