PostgreSQL交叉表无法按需工作 [英] PostgreSQL crosstab doesn't work as desired
问题描述
在此示例中，我希望生成的数据透视表具有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=2c4faf8988 a>
如何获得理想的结果?
虽然某些目标值可能会丢失，但您需要使用2个参数形式的crosstab()(此处
详细说明:
最好从单个查询中获得 dynamic 个目标列的结果. SQL，SQL不能那样工作.有多种解决方法.参见:
In this example, I expect the resulting pivot table to have values for 4 columns, but instead there's only values for 2.
It should've returned something like this:
| 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 |
|------------------------------------------|
but I got this instead:
| 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 |
|------------------------------------------|
Even worse, if I remove
order by unixdatetime
, everything will be smashed into only 1 column as below:
| 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 |
|------------------------------------------|
Here's the code:
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
);
Here's the link in case you'd like to play around:
https://dbfiddle.uk/?rdbms=postgres_11&fiddle=2c4f6098fb89b78898ba1bf6afa7f439
How to get the desired result?
While some of the target values may be missing , you need the 2-argument form of crosstab() (like unutbu provided).
But it makes no sense to use a query producing unstable results as 2nd parameter. Use a VALUES expression (or similar) to provide a stable set of target columns in sync with the resulting column definition list. Like:
SELECT *
FROM crosstab(
$$
SELECT *
FROM (
VALUES
(bigint '1546300800', 187923, float8 '1.5')
, (1546387200,187923,1.5)
, (1546473600,187923,1.5)
-- , ...
, (1548288000,187927,100)
) t (unixdatetime, gaugesummaryid, value)
ORDER BY 1,2
$$
, 'VALUES (187923), (187924), (187926), (187927)' -- !!
) final_result (unixdatetime int
, trace1 float8
, trace2 float8
, trace3 float8
, trace4 float8);db<>fiddle here
Detailed explanation:
It would be nice to get results for a dynamic number of target columns from a single query. Alas, SQL does not work like that. There are various workarounds. See:
这篇关于PostgreSQL交叉表无法按需工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
