具有多个值列的数据透视/交叉表

问题描述：

我有一个产生以下结果集的视图：

I have a view that produces the following resultset:

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 在目标结果集中生成新列？如果我只是将 future_value 列添加到 crosstab（text）查询中，则会抱怨无效的源数据SQL语句。

I know how to do this if I use just the current_value column, using crosstab. How can I use current_value and future_value to produce new columns in the destination resultset? If I just add future_value column to the crosstab(text) query it complains about "invalid source data SQL statement".

我正在使用PostgreSQL 9.3.6。

I'm using PostgreSQL 9.3.6.

答

一个方式将是使用复合类型：

One way would be to use a composite type:

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

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

Or, for ad-hoc use (registers the type for the duration of the session):

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

然后运行交叉表，并分解复合类型：

Then run the crosstab as you know it and decompose the composite type:

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交叉表查询

PostgreSQL Crosstab Query

具有多个值列的数据透视/交叉表

相关推荐