sql - 过滤对具有多个值的 crosstab() 查询结果的意外影响

Question

我有一个 crosstab()查询类似于我上一个问题中的查询:
Unexpected effect of filtering on result from crosstab() query

常见的情况是过滤extra1具有多个值的字段:extra1 IN(value1, value2...) .对于 extra1 中包含的每个值过滤器，我添加了这样的排序表达式 (extra1 <> valueN) ，如上述帖子所示。结果查询如下:

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的第一个值包含在排序表达式中 value1 ，得到正确的结果行。但是，下面的value2 , value3 ...，得到错误的结果数，导致每个结果的行数减少。这是为什么？

更新:

将此作为我们的源表(table t):

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

使用 CROSSTAB , 这个想法是得到一个这样的表:

+----------+--------+--------+------+------+------+
| row_name | Extra1 | Extra2 | cat1 | cat2 | cat3 |
+----------+--------+--------+------+------+------+
| Name1    | 10     | A      | 100  | 100  |      |
| Name2    | 11     | B      |      | 200  | 150  |
| Name3    | 12     | C      | 120  | 150  | 150  |
+----------+--------+--------+------+------+------+

我的想法是能够过滤结果表，这样我就可以用 Extra1 得到结果具有值的列 10或 11 ，如下:

+----------+--------+--------+------+------+------+
| row_name | Extra1 | Extra2 | cat1 | cat2 | cat3 |
+----------+--------+--------+------+------+------+
| Name1    | 10     | A      | 100  | 100  |      |
| Name2    | 11     | B      |      | 200  | 150  |
+----------+--------+--------+------+------+------+

问题是在我的查询中，Extra1 得到不同的结果大小与 10作为值(value)和Extra1与 11作为值(value)。与 (Extra1 <> 10)我可以在 Extra1 上获得正确的结果大小对于该值，但在 11 的情况下不是作为值(value)。

这是一个更详细地演示问题的 fiddle :

https://dbfiddle.uk/?rdbms=postgres_11&fiddle=5c401f7512d52405923374c75cb7ff04

Answer 1

所有“额外”列都是从组的第一行复制的(如 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);

db<> fiddle here - 在您的 fiddle 底部添加了我的查询。