如何创建一个透视多列的 PostgreSQL 透视表? [英] How to create a PostgreSQL pivot table that pivots multiple columns?

问题描述

我一直在探索 PostgreSQL 的 tablefunc 中的 crosstab() 函数 扩展模块，作为生成数据透视表的一种方式.

I have been been exploring the crosstab() function in PostgreSQL's tablefunc extension module, as a way of producing pivot tables.

这很棒，但似乎只适用于最基本的用例.它一般只支持三列输入:

It's great, but seems suitable for only the most basic of use cases. It generally supports only THREE columns of input:

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 |
+------+--------+--------+----------+

但是更复杂的用例呢?如果你有:

But what about more complex use cases? What if you have:

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)以对其进行透视?

Where the first THREE columns should carry over as-is for each row (no grouping or aggregation). And the metric column has TWO associated columns (i.e. value and confidence_level) to pivot for it?

+--------+-----------------+---------+--------------+-------------------+-----------+----------------+
| 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 函数生成它?谢谢！

I'm not sure if this still fits the strict definition of "pivot table". But is such a result possible with crosstab(), or any other readily-available PostgreSQL function? If not, then how could it be produced with a custom PL/pgSQL function? Thanks!

推荐答案

可以尝试使用条件聚合

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

这篇关于如何创建一个透视多列的 PostgreSQL 透视表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！