如何创建多列透视的PostgreSQL枢轴表？

Question

我一直在探索PostgreSQL的crosstab()函数的tablefunc 扩展模块，作为生成枢轴表的一种方法。

它很棒，但似乎只适用于最基本的用例。它通常只支持三列输入：

1. 保持不变的值列，作为行标签。
2. 一列被旋转的值，成为新的列名。
3. 一列值，成为各自的新枢轴列的值。

基本上是这样的：

+------+----------+-------+
| ITEM |  STATUS  | COUNT |
+------+----------+-------+
| foo  | active   |    12 |
| foo  | inactive |    17 |
| bar  | active   |    20 |
| bar  | inactive |     4 |
+------+----------+-------+

..。并制作这个：

+------+--------+--------+----------+
| ITEM | STATUS | ACTIVE | INACTIVE |
+------+--------+--------+----------+
| foo  | active |     12 |       17 |
| bar  | active |     20 |        4 |
+------+--------+--------+----------+

但是更复杂的用例呢？如果你有：

1. 您希望保留的多个输入列-是否在输出中？
2. 要将多个输入列转换为新列？

如下例所示：

+--------+-----------------+---------+--------+-------+------------------+
| SYSTEM |  MICROSERVICE   |  MONTH  | METRIC | VALUE | CONFIDENCE_LEVEL |
+--------+-----------------+---------+--------+-------+------------------+
| batch  | batch-processor | 2019-01 | uptime |    99 |                2 |
| batch  | batch-processor | 2019-01 | lag    |    20 |                1 |
| batch  | batch-processor | 2019-02 | uptime |    97 |                2 |
| batch  | batch-processor | 2019-02 | lag    |    35 |                2 |
+--------+-----------------+---------+--------+-------+------------------+

前三列应按每一行的原样结转(不进行分组或聚合)。而metric列有两个相关联的列(即value和confidence_level)作为支点？

+--------+-----------------+---------+--------------+-------------------+-----------+----------------+
| SYSTEM |  MICROSERVICE   |  MONTH  | UPTIME_VALUE | UPTIME_CONFIDENCE | LAG_VALUE | LAG_CONFIDENCE |
+--------+-----------------+---------+--------------+-------------------+-----------+----------------+
| batch  | batch-processor | 2019-01 |           99 |                 2 |        20 |              1 |
| batch  | batch-processor | 2019-02 |           97 |                 2 |        35 |              2 |
+--------+-----------------+---------+--------------+-------------------+-----------+----------------+

我不确定这是否仍然符合“枢轴表”的严格定义。但是，这样的结果在crosstab()或任何其他现成的PostgreSQL函数中是可能的吗？如果不是，那么如何使用自定义PL/pgSQL函数生成它？谢谢!

Answer 1

您可以尝试使用条件聚合。

select system,MICROSERVICE , MONTH,
max(case when METRIC='uptime' then VALUE end) as uptime_value,
max(case when METRIC='uptime' then CONFIDENCE_LEVEL end) as uptime_confidence,
max(case when METRIC='lag' then VALUE end) as lag_value,
max(case when METRIC='lag' then CONFIDENCE_LEVEL end) as lag_confidence
from tablename
group by system,MICROSERVICE , MONTH

Answer 2

另一种方法(我已经使用过)是将数据写入文件，使用单独的实用程序以所需的格式交叉表，并将结果导入新的表中。