我期望多行时，Postgres中的交叉表函数返回一行输出

Question

我目前有m以下格式的表格：

id    scenario    period    ct
2        1          1       1  
2        1          2       1
2        1          3       1
2        1          4       1
2        2          1       1
2        2          2       1
2        2          3       1
2        2          4       1
2        3          1       1
2        3          2       1
2        3          3       1
2        3          4       1

我要创建下表：

id    scenario    period    1    2    3    4
2        1          1       1
2        1          2            1
2        1          3                 1
2        1          4                      1
2        2          1       1
2        2          2            1
2        2          3                 1
2        2          4                      1
2        3          1       1
2        3          2            1
2        3          3                 1
2        3          4                      1

tablefunc扩展名已经在我的Postgres数据库中创建。我目前正在尝试使用该crosstab()功能来完成数据透视。但是，我得到的表如下所示：

id    scenario    period    1    2   3    4 
2        1          1       1    1   1    1

我试过的查询：

SELECT * FROM crosstab(
        'SELECT id, scenario, period, ct FROM m 
            ORDER BY 1',
         'SELECT DISTINCT period FROM m 
            ORDER BY 1')
AS (id, scenario, period, 1, 2, 3, 4);

Answer 1

此查询生成所需的输出：

SELECT id, scenario, period, p1, p2, p3, p4  -- all except aux column rn
FROM   crosstab(
  'SELECT row_number() OVER (ORDER BY id, scenario, period)::int AS rn
        , id, scenario, period, period, ct
   FROM   m
   ORDER  BY 1'
, 'VALUES (1), (2), (3), (4)'
   ) AS (rn int, id int, scenario int, period int, p1 int, p2 int, p3 int, p4 int);

两个特殊的困难：

1. 您还没有 row_name的 唯一列。我row_number()用来生成代理密钥：rn。我从外部将其删除，SELECT以符合您期望的结果。
   您尝试的方式id被视为 row_name ，所有输入行都汇总到单个输出行中。
   

2. 您需要结果中的其他列（scenario和period），这些列必须在 row_name之后 和 category 之前。您必须列出period 两次 才能另外获得原始列-看起来似乎很多余。

基本：

• PostgreSQL交叉表查询

与此特定情况有关：

• 使用Tablefunc在多个列上枢轴

通常，您会有这样的查询：

SELECT id, scenario, p1, p2, p3, p4  -- all except aux column rn
FROM   crosstab(
  'SELECT rank() OVER (ORDER BY id, scenario)::int AS rn
        , id, scenario, period, ct
   FROM   m
   ORDER  BY 1'
, 'VALUES (1), (2), (3), (4)'
   ) AS (rn int, id int, scenario int, p1 int, p2 int, p3 int, p4 int);

输出如下：

id   scenario   p1   p2   p3   p4
2    1          1    1    1    1
2    2          1    1    1    1
2    3          1    1    1    1

请注意，使用rank()而不是row_number()将相同的组合组合(id, scenario)在一起。
如果计数不是全部，则结果更有意义1。