时间戳

首页 > 解决方案 > 过滤对 crosstab() 查询结果的意外影响

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

问题描述

我有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, ...)

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

当我按如下方式更改查询时：

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, ...) --> 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, ...)
WHERE extra1 = val1; --> condition on the result

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

标签： sqlpostgresqlsql-order-bydistinctcrosstab

解决方案

extra1, extra2, ...是交叉表术语中的“额外列”。
tablefunc 模块的手册解释了规则：

它也可能有一个或多个“额外”列。该row_name列必须是第一个。类别和value列必须是最后两列，按此顺序。row_name和之间的任何列category都被视为“额外”。对于具有相同row_name值的所有行，“额外”列应该是相同的。

再往下：

输出row_name列，以及任何“额外”列，都是从 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”的行就越少：这就是您观察到的。

您仍然可以使其工作：要报告至少具有其中之一的extra1 = 'val1'每个人，请将其更改为：row_nameORDER BY

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

在顶部排序“val1”。该表达式的解释boolean（带有更多链接）：

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

交叉表基础知识：

推荐阅读