我有一个这样的查询A:
SELECT * FROM my_table WHERE foreign_key = 'abc' AND field1 = 'foo' AND field2 = 'bar';
有没有办法找出与数据的超集相比,where 子句的哪个部分负责排除数据?例如,与查询 B 相比:
SELECT * FROM my_table WHERE foreign_key = 'abc';
我知道您可以在单独的查询中获取 where 子句的每个部分,并获取查询 B 的差异数量。是否有更有效的方法或最佳实践?
目标是标记排除的数据行及其排除原因。
我将使用简化的查询来回答:
SELECT *
FROM my_table
WHERE field1 = 'foo' AND field2 = 'bar';
我们可以尝试使用CASE
表达式来标记失败的原因:
SELECT
id,
CASE WHEN (field1 <> 'foo' OR field1 IS NULL) AND
(field2 <> 'bar' OR field2 IS NULL)
THEN 'field1,field2'
WHEN (field1 <> 'foo' OR field1 IS NULL)
THEN 'field1'
WHEN (field2 <> 'bar' OR field2 IS NULL)
THEN 'field2'
ELSE 'pass' END AS reason
FROM my_table;
请注意,此查询实际上没有 WHERE
子句;它返回所有记录,每条记录都标记为 pass,或者带有可能导致失败的字段。
是标记排除的数据行及其排除原因。
然后,您可能希望查看以前被排除的数据 - 这意味着您需要从 WHERE 子句中删除谓词。如果您只是将它们添加到 SELECT 子句中,您将看到哪些满足,哪些不满足 -
SELECT *,
(foreign_key = 'abc') AS foreign_key_rule,
(field1 = 'foo') AS field1_rule,
(field2 = 'bar') AS field2_rule
FROM my_table WHERE 1;
您可以执行如下操作来找出为什么会排除行:
select
id,
case
when coalesce(foreign_key, '') != 'abc' then 'foreign_key not "abc"'
when coalesce(field1, '') != 'foo' then 'field1 not "foo"'
when coalesce(field2, '') != 'bar' then 'field2 not "bar"'
end as exclusion_reason
from my_table
where not (foreign_key = 'abc' AND field1 = 'foo' AND field2 = 'bar')
请注意,where
子句已被否定,用 not (...)
括起来,因此仅返回排除的行。
您可以调整它来标记排除的行,如下所示:
update my_table set
exclusion_reason = case
when coalesce(foreign_key, '') != 'abc' then 'foreign_key not "abc"'
when coalesce(field1, '') != 'foo' then 'field1 not "foo"'
when coalesce(field2, '') != 'bar' then 'field2 not "bar"'
end
where where not (foreign_key = 'abc' AND field1 = 'foo' AND field2 = 'bar')