PostgreSQL是否有类似于“反转列”功能的函数？
PostgreSQL 中类似于 unpivot() 的函数是什么？
55
- Tony Searle
2
5"unipivot"的意思是"单支枢"，它通常用于描述唱臂或其他轴承系统。 - Milen A. Radev
2在PostgreSQL中，如@Bill Karwin所指出的那样，crosstab()函数用于数据透视操作。文档中说：“crosstab函数用于生成“透视”显示，其中数据横向列出而不是纵向。”因此，通过取消透视，我认为@Tony Searle的意思是“数据沿页面向下列出，而不是横向列出”。请参见下面的答案。 - Stew
10个回答
129
创建一个示例表格：
你可以使用 UNION ALL 来“unpivot”或“uncrosstab”。
这个查询在
但是这样会扫描表N次，其中N是你想要展开的列的数量。这是低效的，尤其是当你处理一个非常大的表需要花费很长时间来扫描时。
相反，可以使用以下方法：
这样写起来更简单，而且只需要扫描一次表格。
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]) 将结果拆分为数组的每个元素的一行。- Stew
3
9“unnest”策略在有255列的大表格上非常实用和高效，谢谢！ - Mike T
8这篇博客介绍了使用“hstore”模块的另一种解决方案：http://www.postgresonline.com/journal/archives/283-Unpivoting-data-in-PostgreSQL.html。 - user330315
20
您可以使用
示例数据：
VALUES() 和 JOIN LATERAL 来将列转换为行。示例数据：
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);
- Lukasz Szozda
1
13
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;
- Tom De Cort
2
我遇到了这个错误
错误：在“（”附近语法错误
第5行：（c.q1，“Q1”），
^ - sanchitkhanna26
@sanchitkhanna26 在“交叉连接侧向子查询”中的值必须在所有元组之前仅出现一次，而不是在每个元组之前出现一次。 - Alessandro Rossi
5
对于我们这些正在寻找如何在RedShift中进行数据旋转的人，以下是FYI。
Stew给出的长篇解决方案似乎是实现此功能的唯一方法。
无法查看上述内容的人，请参考以下文字：
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
- camacnei
4
从@a_horse_with_no_name的评论链接中略微修改的内容来作为答案的原因是因为它有效：
安装Hstore
如果您尚未安装hstore并且正在运行PostgreSQL 9.1+，则可以使用方便的
CREATE EXTENSION hstore;
对于较低版本，请查找share/contrib中的hstore.sql文件，并在数据库中运行。
假设您的源（例如宽数据）表具有一个'id'列，命名为id_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 ;
这适用于任意数量的"value"列。所有生成的值都将是文本格式，除非您进行强制转换，例如：
(h).value::numeric。- Brian D
4
只需使用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 |
- Lukas Eder
2
1现在这真的是非常简洁和好的本地化Postgres。 - L. Rodgers
我发现唯一的JSON-only re-PIVOT路径是聚合JSON作为n-v对，构建对象的JSON数组，然后将它们转换为已知类型：json_populate_recordset ( NULL::tmpZxta, /** use temp table as a record type!! **/ json_build_array( json_object_agg(column_name,data)))这可以让你“半动态地构建任何形式的行集”（CROSSTAB()无法做到），尽管填充混合列类型需要按类型分区数据。 - L. Rodgers
2
我为PostgreSQL写了一个糟糕的unpivot函数。它非常慢，但至少返回像您期望的unpivot操作一样的结果。
https://cgsrv1.arrc.csiro.au/blog/2010/05/14/unpivotuncrosstab-in-postgresql/
希望您能觉得它有用。- Josh LOL
0
您可以使用FROM UNNEST()数组处理来对数据集进行反交叉操作，并与相关子查询配合使用（适用于PG 9.4）。
FROM UNNEST()比使用FROM (VALUES .... )来进行数据集反交叉更加强大和灵活。这是因为FROM UNNEST()是变长的（具有n元性）。通过使用相关子查询，可以消除对LATERAL ORDINAL子句的需求，而Postgres将保持结果并行列集处于正确的序列中。
顺便说一下，这很快--在实际使用中，在24核系统上生成800万行只需要不到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 | .
+--------+----------------+-----------+----------+---------+ ----
- Lee Rodgers
0
根据你想要做什么...这样的东西可能会有所帮助。
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
- Sumtraveller
0
这里有一种方法，结合了其他答案中的hstore和CROSS JOIN方法。
这是我对类似问题的回答的修改版本，它本身基于https://blog.sql-workbench.eu/post/dynamic-unpivot/的方法和那个问题的另一个答案。
它比其他解决方案有一些优点：
这是我对类似问题的回答的修改版本，它本身基于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
- Michal Charemza
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接
- 相关问题
- 9 PostgreSQL中类似于JavaScript encodeURI的函数是什么？
- 3 PostgreSQL中是否有类似于Oracle的systimestamp()函数的等效函数？
- 3 MySQL中类似于PostgreSQL的dblink模块的等价物是什么？
- 20 SQLite中类似于PostgreSQL的GREATEST函数的等效函数是什么？
- 3 SQL Server中类似于PostgreSQL的函数是什么？
- 3 Postgresql中类似于SQL Server的user_id()函数的等效函数是什么？
- 5 PostgreSQL中类似于Oracle "bulk collect"的语句是什么？
- 8 在PostgreSQL中是否有类似于sp_help的等效函数？
- 4 PostgreSQL中类似于简单存储过程的函数是什么？
- 15 在PostgreSQL函数中搜索类似于'grep-alike'的内容