sql - 筛选对具有多个值的crosstab()查询的结果产生意外影响

我有一个类似于上一个问题的查询：Unexpected effect of filtering on result from crosstab() query常见的情况是用多个值过滤crosstab()字段：extra1。对于extra1 IN(value1, value2...)过滤器中包含的每个值，我添加了一个类似于extra1的排序表达式，如上面提到的文章所示。结果查询如下：SELECT *FROM crosstab( 'SELECT row_name, extra1, extra2..., another_table.category, value FROM table t JOIN another_table ON t.field_id = another_table.field_id WHERE t.field = certain_value AND t.extra1 IN (val1, val2, ...) --> more values ORDER BY row_name ASC, (extra1 <> val1), (extra1 <> val2)', ... --> more ordering expressions 'SELECT category_name FROM category_name WHERE field = certain_value') AS ct(extra1, extra2...)WHERE extra1 = val1; --> condition on the result排序表达式(extra1 <> valueN)中包含的extra1的第一个值将得到正确的结果行。但是，下面的value1，value2…，得到的结果数目错误，导致每个结果上的行数更少。为什么？更新：将此作为源表（value3）：+----------+--------+--------+------------------------+-------+| row_name | Extra1 | Extra2 | another_table.category | value |+----------+--------+--------+------------------------+-------+| Name1 | 10 | A | 1 | 100 || Name2 | 11 | B | 2 | 200 || Name3 | 12 | C | 3 | 150 || Name2 | 11 | B | 3 | 150 || Name3 | 12 | C | 2 | 150 || Name1 | 10 | A | 2 | 100 || Name3 | 12 | C | 1 | 120 |+----------+--------+--------+------------------------+-------+这是我们的分类表：+-------------+--------+| category_id | value |+-------------+--------+| 1 | Cat1 || 2 | Cat2 || 3 | Cat3 |+-------------+--------+使用table t，我们的想法是得到这样一个表：+----------+--------+--------+------+------+------+| row_name | Extra1 | Extra2 | cat1 | cat2 | cat3 |+----------+--------+--------+------+------+------+| Name1 | 10 | A | 100 | 100 | || Name2 | 11 | B | | 200 | 150 || Name3 | 12 | C | 120 | 150 | 150 |+----------+--------+--------+------+------+------+我们的想法是能够过滤结果表，这样我就可以使用值为CROSSTAB或Extra1的10列获得结果，如下所示：+----------+--------+--------+------+------+------+| row_name | Extra1 | Extra2 | cat1 | cat2 | cat3 |+----------+--------+--------+------+------+------+| Name1 | 10 | A | 100 | 100 | || Name2 | 11 | B | | 200 | 150 |+----------+--------+--------+------+------+------+问题是，在我的查询中，以11为值的Extra1和以10为值的Extra1的结果大小不同。使用11我可以在(Extra1 <> 10)上为该值获得正确的结果大小，但在Extra1as值的情况下不能。这里有一把小提琴更详细地演示了这个问题：https://dbfiddle.uk/?rdbms=postgres_11&fiddle=5c401f7512d52405923374c75cb7ff04 最佳答案 所有“额外”列都从组的第一行复制（如my previous answer中指出的那样）当你过滤时：.... WHERE extra1 = 'val1';…在同一列中添加更多的ORDER BY表达式是没有意义的。只有源组中至少有一个extra1 = 'val1'的行才能生存。从您的各种评论中，我猜您可能希望看到在extra子句中过滤的集合中的所有不同的现有值WHERE -对于相同的unixdatetime。如果是，则在旋转之前聚合。比如：SELECT *FROM crosstab( $$ SELECT unixdatetime, x.extras, c.name, s.value FROM ( SELECT unixdatetime, array_agg(extra) AS extras FROM ( SELECT DISTINCT unixdatetime, extra FROM source_table s WHERE extra IN (1, 2) -- condition moves here ORDER BY unixdatetime, extra ) sub GROUP BY 1 ) x JOIN source_table s USING (unixdatetime) JOIN category_table c ON c.id = s.gausesummaryid ORDER BY 1 $$ , $$SELECT unnest('{trace1,trace2,trace3,trace4}'::text[])$$) AS final_result (unixdatetime int , extras int[] , trace1 numeric , trace2 numeric , trace3 numeric , trace4 numeric);旁白：下面有关第二个函数参数的相关答案中给出的建议也适用于您的案例：PostgreSQL crosstab doesn't work as desired我演示了上面的静态第二个参数查询。在这里，您根本不需要加入category_table。同样，短一点，快一点，但是：SELECT *FROM crosstab( $$ SELECT unixdatetime, x.extras, s.gausesummaryid, s.value FROM ( SELECT unixdatetime, array_agg(extra) AS extras FROM ( SELECT DISTINCT unixdatetime, extra FROM source_table WHERE extra IN (1, 2) -- condition moves here ORDER BY unixdatetime, extra ) sub GROUP BY 1 ) x JOIN source_table s USING (unixdatetime) ORDER BY 1 $$, $$SELECT unnest('{923,924,926,927}'::int[])$$) AS final_result (unixdatetime int , extras int[] , trace1 numeric , trace2 numeric , trace3 numeric , trace4 numeric);dbfiddlehere-在您的fiddle底部添加了我的查询。关于sql - 筛选对具有多个值的crosstab()查询的结果产生意外影响，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/57221777/