主页 / dba / 问题 / 191964
Accepted
lakshman
Asked: 2017-11-30 01:20:12 +0800 CST

如何使用复合“行名”交叉制表

  • 772

我有下表id(intger), project (varchar), month (varchar), hours (integer)

CREATE TABLE foo (id,project,month,hours)
    AS VALUES
    ( 1 , 'A', 'aug', 1 ),
    ( 1 , 'A', 'sep', 3 ),
    ( 1 , 'B', 'aug', 2 ),
    ( 1 , 'B', 'sep', 5 ),
    ( 2 , 'A', 'aug', 2 ),
    ( 2 , 'A', 'sep', 4 );

我想要下表

|id | project | aug | sep |
|1  | A       | 1   | 3   |
|1  | B       | 2   | 5   |
|2  | A       | 2   | 4   |

我读到这可以用数组实现，但文档似乎很混乱。有人可以阐明这一点吗？

postgresql pivot

1 个回答

  1. Best Answer

    crosstab不允许复合键。它要求行提供一个唯一的标识符，以便将它们作为轴心。这意味着在您的情况下(id,project)必须进行序列化。您可以使用jsonb, 或 array 等来做到这一点。在这里，我只是使用一个text[].

    SELECT id, project, aug, sep
FROM crosstab(
  $$
    SELECT ARRAY[id,project]::text[], id, project, month, hours
    FROM foo
    ORDER BY 1
  $$,
  $$
    SELECT DISTINCT month FROM foo ORDER BY 1
  $$
) AS c(rn text[], id int,project text,aug int,sep int);

 id | project | aug | sep 
----+---------+-----+-----
  1 | A       |   1 |   3
  1 | B       |   2 |   5
  2 | A       |   2 |   4
(3 rows)

    一些笔记，

    • 你应该存储month为date.
    • 您可能还想查看允许复合键的MADlib pivot 。

    还有很多其他方法可以在不使用交叉表的情况下解决这个问题，

    SELECT id, project, jsonb_object_agg( month, hours ) AS month_hours
FROM foo
GROUP BY id, project
ORDER BY 1,2;
 id | project |     month_hours      
----+---------+----------------------
  1 | A       | {"aug": 1, "sep": 3}
  1 | B       | {"aug": 2, "sep": 5}
  2 | A       | {"aug": 2, "sep": 4}
(3 rows)
    • 3

相关问题