我期望多行时，Postgres中的交叉表函数返回一行输出 [英] Crosstab function in Postgres returning a one row output when I expect multiple rows

问题描述

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

I currently have a table m of the following format:

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()函数来完成数据透视.但是，我得到的表如下所示:

The tablefunc extension has been created in my Postgres database already. I'm currently trying to use the crosstab() function to complete the pivot. However, I'm getting a table that looks like the following:

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);

推荐答案

此查询生成所需的输出:

This query generates your desired output:

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 ，并且所有输入行都汇总到单个输出行中.

1. You don't have a single unique column for the row_name yet. I use row_number() to generate the surrogate key: rn. I removed it from the outer SELECT to match your desired result.
   The way you tried it, id is taken to be the row_name and all input rows are aggregated into a single output row.

您要在结果中添加其他列(scenario和period)，这些列必须在 row_name 之后且在 category 之前.您必须列出period 两次才能另外获得原始列-看起来似乎很多余.

You want additional columns (scenario and period) in the result, which must come after the row_name and before the category. You must list period twice to get the original column additionally - redundant as though it may seem.

基础:

• PostgreSQL交叉表查询

与这种特殊情况有关:

• 使用Tablefunc在多列上旋转

通常，您会遇到类似这样的查询:

Typically, you would have a query like this:

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，则结果更有意义.

Note the use of rank() instead of row_number() to group same combinations of (id, scenario) together.
The result makes more sense if counts are not all 1.

这篇关于我期望多行时，Postgres中的交叉表函数返回一行输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！