PostgreSQL交叉表无法按需工作

详细说明: PostgreSQL交叉表查询 最好从单个查询中获得 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=2c4f6098fb89b78898ba1bf6afa7f439How 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 hereDetailed explanation:PostgreSQL Crosstab QueryIt 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:Execute a dynamic crosstab query 这篇关于PostgreSQL交叉表无法按需工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！