时间戳

首页 > 解决方案 > PostgreSQL 交叉表无法按预期工作

postgresql - PostgreSQL 交叉表无法按预期工作

问题描述

在此示例中，我希望生成的数据透视表具有 4 列的值，但实际上只有 2 列的值。

它应该返回如下内容：

| time | trace1 | trace2 | trace3 | trace4 |
| -----------------------------------------|
|   t  |   v    |   v    |   v    |   v    |
|   t  |   v    |   v    |   v    |  null  |
|   t  |  null  |   v    |   v    |   v    |
|   t  |   v    |   v    |  null  |   v    |
|   t  |   v    |  null  |   v    |   v    |
|------------------------------------------|

但我得到了这个：

| time | trace1 | trace2 | trace3 | trace4 |
| -----------------------------------------|
|   t  |   v    |   v    |  null  |  null  |
|   t  |   v    |   v    |  null  |  null  |
|   t  |   v    |   v    |  null  |  null  |
|   t  |   v    |  null  |  null  |  null  |
|   t  |   v    |  null  |  null  |  null  |
|------------------------------------------|

更糟糕的是，如果我删除

order by unixdatetime

，所有内容都将被粉碎成一列，如下所示：

| time | trace1 | trace2 | trace3 | trace4 |
| -----------------------------------------|
|   t  |   v    |  null  |  null  |  null  |
|   t  |   v    |  null  |  null  |  null  |
|   t  |   v    |  null  |  null  |  null  |
|   t  |   v    |  null  |  null  |  null  |
|   t  |   v    |  null  |  null  |  null  |
|------------------------------------------|

这是代码：

select * 
from crosstab(
    $$
    select 
        unixdatetime, 
        gaugesummaryid, 
        value::double precision 
    from 
    (values
        (1546300800,187923,1.5),
        (1546387200,187923,1.5),
        (1546473600,187923,1.5),
        (1546560000,187923,1.75),
        (1546646400,187923,1.75),
        (1546732800,187923,1.75),
        (1546819200,187923,1.75),
        (1546905600,187923,1.5),
        (1546992000,187923,1.5),
        (1547078400,187923,1.5),
        (1547164800,187923,1.5),
        (1547337600,187924,200),
        (1547424000,187924,200),
        (1547510400,187924,200),
        (1547596800,187924,200),
        (1547683200,187924,200),
        (1547769600,187924,200),
        (1547856000,187924,200),
        (1547942400,187924,200),
        (1548028800,187924,200),
        (1548115200,187924,200),
        (1548201600,187924,200),
        (1548288000,187924,200),
        (1546300800,187926,120),
        (1546387200,187926,120),
        (1546473600,187926,120),
        (1546560000,187926,110),
        (1546646400,187926,110),
        (1546732800,187926,110),
        (1546819200,187926,110),
        (1546905600,187926,115),
        (1546992000,187926,115),
        (1547078400,187926,115),
        (1547942400,187927,100),
        (1548028800,187927,100),
        (1548115200,187927,100),
        (1548201600,187927,100),
        (1548288000,187927,100)
    ) as t (unixdatetime, gaugesummaryid, value)
    order by unixdatetime
    $$
    ) as final_result (
        unixdatetime int, 
        trace1 double precision, 
        trace2 double precision, 
        trace3 double precision, 
        trace4 double precision
        );

如果您想玩，这是链接：

https://dbfiddle.uk/?rdbms=postgres_11&fiddle=2c4f6098fb89b78898ba1bf6afa7f439

如何获得想要的结果？

标签： postgresqlpivotcrosstab

解决方案

我建议您使用filter (where ...)子句而不是数据透视表。

select
    unixdatetime,
    min(value) filter (where gaugesummaryid = 187923) as trace_1,
    min(value) filter (where gaugesummaryid = 187924) as trace_2,
    min(value) filter (where gaugesummaryid = 187926) as trace_3,
    min(value) filter (where gaugesummaryid = 187927) as trace_4
from table
group by 1;

请注意，您必须使用聚合函数才能使用该子句。在您的情况下，使用 ,min或并不重要。maxavgsum

推荐阅读