相当于 PostgreSQL 中的 unpivot()
PostgreSQL 中是否有 unpivot 等效函数?
Is there a unpivot equivalent function in PostgreSQL?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
发布评论
评论(10)
示例数据:
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;
使用这种方法可以同时取消透视多组列。
编辑
使用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);
托马斯·凯勒 (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;
供我们这些正在寻找如何在 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 行)
希望这有帮助,尼尔
只需使用 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 |
将 @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
。
我为 PostgreSQL 编写了一个可怕的 unpivot 函数。 它相当慢,但它至少返回像您期望的逆透视操作一样的结果。
https://cgsrv1.arrc.csiro.au /blog/2010/05/14/unpivotuncrosstab-in-postgresql/
希望你会发现它有用..
您可以使用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 | .
+--------+----------------+-----------+----------+---------+ ----
这是一种结合其他答案中的 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
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
创建一个示例表:
您可以使用 UNION ALL 进行“unpivot”或“uncrosstab”:
这会在
foo
上运行 3 个不同的子查询,每个子查询对应我们要取消透视的每一列,并在一个表中返回每个每个子查询的记录。但这将扫描表 N 次,其中 N 是要逆透视的列数。 这是低效的,并且是一个大问题,例如,当您正在使用需要很长时间扫描的非常大的表时。
相反,使用:
这更容易编写,并且只会扫描表一次。
array[a, b, c]
返回一个数组对象,其中 a、b 和 c 的值作为其元素。unnest(array[a, b, c])
< /a> 将数组每个元素的结果分成一行。Create an example table:
You can 'unpivot' or 'uncrosstab' using UNION ALL:
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:
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.