行转列crosstab使用心得
- 2023-01-08 10:40:00
- pjd
- 原创 371
一 首先，我形成并澄清了你的例子：
SELECT * FROM public.crosstab(
$$VALUES ('na', 1, 100) -- VALUES expression does the same, shorter
, ('ba', 2, 300)$$ -- no column names needed (ignored anyway)
,$$VALUES ('1'), ('2')$$
) AS pivot (na text, co11 int, col2 int) -- sane column names
输入表：
col1 key val --------------- na 1 100 ba 2 300
输出表
na co11 col2 -------------------- na 100 <NULL> ba <NULL> 300
您可以自由选择输出列名，无需使用像"1" ，"2" 这样笨拙的名称。只有数据类型必须匹配。
动态返回类型
不能从查询中提供列定义列表。我们有很多类似的要求。它们都与SQL的性质冲突，SQL不接受“after-the-fact”返回类型。必须与查询一起声明，至少在执行查询时。
您必须使用要在第二个示例中添加的crosstab() 查询的输出来构建SELECT 查询。两次往返服务器。
但我们可以从系统目录中读取一个众所周知的类型，毕竟SELECT * FROM tbl 就是这样做的。我努力尝试并实现了我在这一相关答案中的发现：
Dynamic alternative to pivot with CASE and GROUP BY
我的功能可能是你最好的选择。阅读链接的答案！
它是crosstab_n() 的包装器，接受查询字符串（就像crosstab() ）和提供返回类型的附加多态参数。我们仍然不能即时传递返回类型。SQL唯一可接受的方法是从系统目录中读取它。因此，我们通过创建一个临时表来“动态”安装一个复合类型：
CREATE TEMP TABLE my_pivot (na text, col1 int, col2 int);
SELECT * FROM crosstab_n(
$$VALUES ('na', 1, 100), ('ba', 2, 100)$$
,NULL::my_pivot
);
结果如上。
如果希望临时表仅用于事务，请将crosstab() 添加到其中。两个语句必须在一个事务中执行。细节：
我正在尝试使用Postgres的tablefunc扩展CROSSTAB功能对某些数据执行数据透视操作.数据需要首先进行一些转换,我在一些
常见的表表达式中执行.
但是,它似乎CROSSTAB无法看到那些表达式的结果.
例如,从
临时表中获取数据的查询工作正常:
并且,如预期的那样,产生以下输出:
sql
postgresql
pivot
temp-tables
crosstab
CREATE
TEMPORARY
TABLE temporary_table
(
name,
category, category_value)
ON
COMMIT
DROP
AS (
VALUES (
'A',
'foo',
1 ),
(
'A',
'bar',
2 ),
(
'B',
'foo',
3 ),
(
'B',
'bar',
4 )
);
SELECT *
FROM CROSSTAB(
'SELECT * FROM temporary_table',
$$
VALUES (
'foo'),
(
'bar')
$$
)
AS (
name
TEXT,
foo
INT,
bar
INT );
name | foo | bar
text |
integer |
integer ---- | ------- …