相当于 PostgreSQL 中的 unpivot()

发布于 07-26 12:12 字数 36 浏览 10 评论 0原文

PostgreSQL 中是否有 unpivot 等效函数?

Is there a unpivot equivalent function in PostgreSQL?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(10

鯉魚旗2024-08-02 12:12:54

创建一个示例表:

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> 将数组每个元素的结果分成一行。

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:

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.

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.

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.

哭了丶谁疼2024-08-02 12:12:54

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

You could use VALUES() and JOIN LATERAL to unpivot the columns.

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;

DBFiddle Demo

Using this approach it is possible to unpivot multiple groups of columns at once.

EDIT

Using Zack's suggestion:

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 demo

弄潮2024-08-02 12:12:54

托马斯·凯勒 (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;

Great article by Thomas Kellerer found here

Unpivot with Postgres

Sometimes it’s necessary to normalize de-normalized tables - the opposite of a “crosstab” or “pivot” operation. Postgres does not support an UNPIVOT operator like Oracle or SQL Server, but simulating it, is very simple.

Take the following table that stores aggregated values per quarter:

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

And the following sample data:

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

But we want the quarters to be rows (as they should be in a normalized data model).

In Oracle or SQL Server this could be achieved with the UNPIVOT operator, but that is not available in Postgres. However Postgres’ ability to use the VALUES clause like a table makes this actually quite easy:

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;

will return the following result:

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     

The equivalent query with the standard UNPIVOT operator would be:

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;
梦亿2024-08-02 12:12:54

供我们这些正在寻找如何在 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 行) 
  

希望这有帮助,尼尔

FYI for those of us looking for how to unpivot in RedShift.

The long form solution given by Stew appears to be the only way to accomplish this.


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

吻泪2024-08-02 12:12:54

只需使用 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

Just use 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;

This yields

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

dbfiddle

玩套路吗2024-08-02 12:12:54

将 @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

Pulling slightly modified content from the link in the comment from @a_horse_with_no_name into an answer because it works:

Installing Hstore
If you don't have hstore installed and are running PostgreSQL 9.1+, you can use the handy

CREATE EXTENSION hstore;

For lower versions, look for the hstore.sql file in share/contrib and run in your database.

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.

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.

吹梦到西洲2024-08-02 12:12:54

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

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

希望你会发现它有用..

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.

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

Hopefully you can find it useful..

小嗲2024-08-02 12:12:54

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

    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

Depending on what you want to do... something like this can be helpful.

    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
凉栀2024-08-02 12:12:54

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

You can use FROM UNNEST() array handling to UnPivot a dataset, tandem with a correlated subquery (works w/ PG 9.4).

FROM UNNEST() is more powerful & flexible than the typical method of using FROM (VALUES .... ) to unpivot datasets. This is b/c FROM UNNEST() is variadic (with n-ary arity). By using a correlated subquery the need for the lateral ORDINAL clause is eliminated, & Postgres keeps the resulting parallel columnar sets in the proper ordinal sequence.

This is, BTW, FAST -- in practical use spawning 8 million rows in < 15 seconds on a 24-core system.

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  )

;
 

RESULT SET:

+--------+----------------+-----------+----------+---------+-------
|   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    |    .
+--------+----------------+-----------+----------+---------+ ----   
我不在是我2024-08-02 12:12:54

这是一种结合其他答案中的 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

Here's a way that combines the hstore and CROSS JOIN approaches from other answers.

It's a modified version of my answer to a similar question, which is itself based on the method at https://blog.sql-workbench.eu/post/dynamic-unpivot/ and another answer to that question.

-- 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}
It has a few benefits over other solutions:

By using hstore and not jsonb, it hopefully minimises issues with type conversions (although hstore does convert everything to text)
The columns don't need to be hard coded or known in advance. Here, columns are chosen by a regex on the name, but you could use any SQL logic based on the name, or even the value.
It doesn't require PL/pgSQL - it's all SQL

;

It has a few benefits over other solutions:

  • By using hstore and not jsonb, it hopefully minimises issues with type conversions (although hstore does convert everything to text)
  • The columns don't need to be hard coded or known in advance. Here, columns are chosen by a regex on the name, but you could use any SQL logic based on the name, or even the value.
  • It doesn't require PL/pgSQL - it's all SQL
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文