sql - 根据满足多少 WHERE 条件对查询结果进行排名

Question

假设我正在尝试将具有多个条件的查询放在一起:

select * from t
where (Condition1 OR Condition2 OR Condition3)

我的目标是对结果进行分组和排序，以便:

Group 1 => C1 = true,  C2 = true,  C3 = true

Group 2 => C1 = true,  C2 = true,  C3 = false
       OR  C1 = true,  C2 = false, C3 = true
       OR  C1 = false, C2 = true,  C3 = true

Group 3 => C1 = true,  C2 = false, C3 = false
       OR  C1 = false, C2 = true,  C3 = false
       OR  C1 = false, C2 = false, C3 = true

其中C1是Condition1，依此类推。因此，如果您将每个条件转换为 1 或 0，1 为真 0 为假，您将得到一个二进制数字列表:

111

110
101
011

100
010
001

请注意，它们并不是简单地按从大到小的顺序排列。

我只是有点好奇，因为我认为通常 SQL DBMS 不会费心去查看 OR 链中的其他子句，例如，如果第一个子句被证明是真的。我如何将每个条件的结果逐个记录地转换成可以排序的数字？

我希望解决方案可扩展到具有 N 个条件的情况，是否有一个优雅的解决方案？它还必须非常快。我对 Postgres 投入了大量资金。是否有特定于 Postgres 的函数可以提供帮助？

Answer 1

评论中的讨论如何WHERE评估的条件似乎与所问的问题正交 - 对此有简单的解决方案。

Rank query results based on how many of the where conditions are satisfied

SELECT *
FROM   tbl
WHERE (Condition1 OR Condition2 OR Condition3)
ORDER  BY ((Condition1) IS TRUE)::int
        + ((Condition2) IS TRUE)::int
        + ((Condition3) IS TRUE)::int DESC;

或者使用更详细的 standard syntax for the casting就像你 (@Isaac) 自己提供的一样:

ORDER  BY CAST((Condition1) IS TRUE AS integer)
        + CAST((Condition2) IS TRUE AS integer)
        + CAST((Condition3) IS TRUE AS integer) DESC;

基本原则是这样的:
WHERE条件是 bool 表达式，只有 TRUE符合条件，FALSE和 NULL不要。
我们需要一种技术来计算(或连接)TRUE同时丢弃 NULL和 FALSE (或恰恰相反)。以上表达式计数TRUE作为1和 NULL或 FALSE作为0 .

有多种方法可以达到相同的结果:

A CASE表达式稍微冗长，但通常最快:

...    
ORDER  BY (CASE WHEN Condition1 THEN 1 ELSE 0 END
         + CASE WHEN Condition2 THEN 1 ELSE 0 END
         + CASE WHEN Condition3 THEN 1 ELSE 0 END) DESC;

我们可以折叠 FALSE至 NULL与 <expression> OR NULL然后使用 concat()忽略 NULL值。按降序排序，'tt' 在 't' 之前排序等:

...    
ORDER  BY concat(
          Condition1 OR NULL
        , Condition2 OR NULL
        , Condition3 OR NULL) DESC;

或者，对于许多 条件，可能是最短。我们甚至不需要为每个条件添加括号 - 形成一个数组并删除 NULL和 FALSE与 array_remove() ，然后我们可以直接按数组排序:

...    
ORDER  BY array_remove(array_remove(ARRAY[
             Condition1
           , Condition2
           , Condition3
          ], NULL), FALSE) DESC;

我们甚至可以使用另一个 WHERE在子选择和 count() 中使用子句(但由于增加了开销，这通常会更慢):

...    
ORDER  BY (SELECT count(*)
           FROM ( VALUES
                 (Condition1)
               , (Condition2)
               , (Condition3)
              ) t(i)
           WHERE i) DESC;