超过一个值列的数据透视表/交叉表

Question

我有一个视图，它生成了以下结果集：

CREATE TABLE foo
AS
  SELECT client_id, asset_type, current_value, future_value
  FROM ( VALUES
    ( 1, 0, 10 , 20 ),
    ( 1, 1, 5  , 10 ),
    ( 1, 2, 7  , 15 ),
    ( 2, 1, 0  , 2 ),
    ( 2, 2, 150, 300 )
  ) AS t(client_id, asset_type, current_value, future_value);

我需要将它转换成这样:

client_id    a0_cur_val   a0_fut_val  a1_cur_val  a1_fut_val  ...
1            10           20          5           10          
2            NULL         NULL        0           2           

我知道如何在使用交叉表时只使用current_value列来完成此操作。如果我想要使用current_value和future_value来生成目标结果集中的新列，该怎么办呢？如果我只是将crosstab(text)查询中的future_value列添加进去，它会报错，提示“无效的源数据SQL语句”。我正在使用PostgreSQL 9.3.6版本。

- Rodrigo Strauss

---

如果有人认为只使用一列可以有帮助，我可以发布交叉表查询。 - Rodrigo Strauss

将你已经尝试过的内容/代码贴出来总是很有帮助的。 - Erwin Brandstetter

Answer 1

其中一种方法是使用复合类型:

CREATE TYPE i2 AS (a int, b int);

或者，对于临时使用（在会话期间注册类型）：

CREATE TEMP TABLE i2 (a int, b int);

然后按照您所知道的方式运行交叉表并分解复合类型：

SELECT client_id
     , (a0).a AS a0_cur_val, (a0).b AS a0_fut_val
     , (a1).a AS a1_cur_val, (a1).b AS a1_fut_val
     , (a2).a AS a2_cur_val, (a2).b AS a2_fut_val
FROM   crosstab(
       'SELECT client_id, asset_type, (current_value, future_value)::i2
        FROM   foo
        ORDER  BY 1,2'

      ,'SELECT * FROM generate_series(0,2)'
   ) AS ct (client_id int, a0 i2, a1 i2, a2 i2);

所有的括号都是必需的！

crosstab()基础：

• PostgreSQL 交叉表查询

- Erwin Brandstetter

---

我认为在这里使用数组或者指针更加合理，但是回答很棒。 - Evan Carroll

@EvanCarroll：是的，当所有列恰好具有相同的数据类型或可以转换为“text”时，以上代码适用于任何类型组合。 - Erwin Brandstetter

Answer 2

另一个选择是构建一个连接，将两个交叉表查询组合起来，以便您可以独立地恢复任何两个值集中的任何一个... 意思是：

select coalesce(cur.client_id, fut.client_id) client_id
, c0, f0, c1, f1, c2, f2
from
(select client_id, c0, c1, c2
from crosstab 
    ('select client_id, asset_type, current_value
    from foo
    order by client_id, asset_type')
as sal1 (client_id int4, c0 int4 , c1 int4 , c2 int4)) cur
full outer join 
(select client_id, f0, f1, f2
from crosstab 
    ('select client_id, asset_type, future_value
    from foo
    order by client_id, asset_type')
as sal1 (client_id int4, f0 int4 , f1 int4 , f2 int4)) fut
on fut.client_id = cur.client_id

意思是...在两个不同的交叉表查询中获取当前值和未来值，然后将它们连接起来以在联接查询中获得结果

• 我使用了全外连接和coalesce函数来处理client_id，以防第一个包含当前值的查询中缺少任何客户端，如果我们知道当前值始终存在，则可以使用左连接，如果需要当前值和未来值，则可以使用内连接

- Atreide