sql - 过滤对 crosstab() 查询结果的意外影响

Question

我有一个 crosstab() 查询，如下所示:

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 = val1
  ORDER  BY row_name ASC',
 'SELECT category_name FROM category_name WHERE field = certain_value'
) AS ct(row_name text, extra1 text, extra2 text, ...)

简化示例，实际查询非常复杂并且包含重要信息。上述查询在使用 table.extra1 = val1 过滤后返回 N 个结果行。

当我如下更改查询时:

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 <b>IN (val1, ...)</b> --> more values
  ORDER  BY row_name ASC',
 'SELECT category_name FROM category_name WHERE field = certain_value'
) AS ct(row_name text, extra1 text, extra2 text, ...)
<b>WHERE extra1 = val1</b>; --> condition on the result

添加了更多可能的值 table.extra1 IN (val1, ...) 和最终条件 WHERE extra1 = val1。现在我得到的行比原来的行少。更糟糕的是，如果我向 IN (val1, ...) 添加更多的值，我得到的行仍然更少。这是为什么？

Answer 1

extra1、extra2、... 是交叉表术语中的“额外列”。
The manual for the tablefunc module解释规则:

It may also have one or more “extra” columns. The row_name column must be first. The category and value columns must be the last two columns, in that order. Any columns between row_name and category are treated as “extra”. The “extra” columns are expected to be the same for all rows with the same row_name value.

再往下:

The output row_name column, plus any “extra” columns, are copied from the first row of the group.

我对关键部分进行了大胆的强调。

您只按row_name 排序:

ORDER  BY row_name ASC

在您过滤的第一个示例中无关紧要:

WHERE ... t.extra1 = 'val1'  -- single quotes by me

无论如何，所有输入行都有 extra1 = 'val1'。但这在第二个示例中很重要，您可以使用以下内容进行过滤:

WHERE ... t.extra1 IN('val1', ...) --> More values

现在，上面第一个加粗的要求对于额外的列 extra1 是违反的。虽然第一个输入查询的排序顺序是不确定的，但“额外”列 extra1 的结果值是任意选择的。 extra1 的可能值越多，最终具有“val1”的行就越少:这就是您所观察到的。

您仍然可以让它工作:为至少具有其中之一的每个 row_name 报告 extra1 = 'val1'，更改 ORDER BY 到:

ORDER  BY row_name, (extra1 <> 'val1')

将“val1”排在最前面。 boolean 表达式的解释(带有更多链接):

• PostgreSQL: Grouping then filtering table, with condition for nonexistence

其他“额外”列仍然是任意选择的，而排序顺序不是确定的。

交叉表基础知识:

• PostgreSQL Crosstab Query
• Postgresql crosstab query with multiple "row name" columns