Postgres交叉表动态列数

Question

在Postgres 9.4中，我有这样一张表:

id  extra_col  days value
--  ---------  ---  -----
1   rev          0      4
1   rev         30      5
2   cost        60      6

我希望得到这个透视后的结果。

id  extra_col   0  30  60
--  ---------  --  --  --
1   rev         4   5   
2   cost                6

这在交叉表中使用crosstab简单实现，但我需要以下规格：

• 天数列是动态的。有时候每次增加1、2、3（天），0、30、60天（会计月），有时候是360、720（会计年）。
• 天数范围是动态的。（例如，0..500天对比1..10天）。
• 前两列是静态的(id和extra_col)。
• 所有动态列的返回类型都保持相同的类型（在这个例子中，integer）。

以下是我尝试过的解决方案，但都不适用于我：

1. 在PostgreSQL中自动创建交叉表列名 - 需要执行两次数据库操作。
2. 使用crosstab_hash - 不是动态的。

从我所探索的所有解决方案中，似乎只有一种方法可以在一次数据库操作中实现这个目标，这需要运行相同的查询三次。是否可以将查询作为 CTE 存储在 crosstab 函数内？

SELECT *
FROM
CROSSTAB(
    --QUERY--,
    $$--RUN QUERY AGAIN TO GET NUMBER OF COLUMNS--$$
)
as ct (
    --RUN QUERY AGAIN AND CREATE STRING OF COLUMNS WITH TYPE--
)

- allenwlee

---

为什么不进行两次数据库访问？单次访问数据库和动态输出列是互斥的。除非你将动态输出列嵌套到一个单独的列中（例如Oracle的pivot xml运算符），并让客户端对其进行解析。 - Daniel Vérité

Answer 1

任何基于内置功能的解决方案都需要知道输出列的数量。PostgreSQL规划器需要它。有一种基于游标的解决方法 - 这只是从Postgres中获取真正动态结果的一种方式。

示例相对较长且难以阅读（SQL确实不支持交叉表），因此我将不会在此处重新编写来自博客的代码http://okbob.blogspot.cz/2008/08/using-cursors-for-generating-cross.html。

- Pavel Stehule