postgresql - 如何根据 OR 查询中的匹配数对行进行评分？

Question

我正在尝试根据使用 OR 子句指定的一组可变条件对行进行评分，并希望返回具有大量直接匹配项的行。

这是针对 postgres 9.1 (已编辑)11.1 的，我最初的尝试(基于我正在尝试重新查找的堆栈溢出文章)大致如下所示:

SELECT                                                                          
  T.id,                                                                         
  (                                                                             
    SELECT                                                                      
    count(*)                                                                    
    FROM (                                                                      
      VALUES                                                                    
        (T.criteria1),                                                               
        (T.criteria2),                                                              
        (T.criteria3),                                                      
        (T.criteria4)                                                              
    ) AS VALS(col)                                                              
    WHERE VALS.col IS NOT NULL                                                  
  ) AS score                                                                    
  FROM my_table AS T                                                     
  WHERE T.other_criteria = 'somevalue'                                                    
    AND (                                                                       
      T.criteria1='foo'                                                              
      OR T.criteria2='bar'                                                          
      OR T.criteria3 = 'baz                                                          
      OR T.criteria4 = 'stackoverflow'                                                        
    )                                                                           
    ORDER BY score DESC                                                         
    LIMIT 1;        

起初我认为它可以工作，但后来我意识到它实际上在做的只是将与我的 OR 匹配的行上的数字非空值相加。我不想让 score 成为非空列的计数，而是希望它成为与 where 条件匹配的列的计数。

例如，如果我有一行匹配我的 OR 条件中的 2 个，我希望它返回 2 分，如果它匹配 3 个，则返回 3 分等。

我已经尝试过类似下面的操作，但没有成功。

SELECT 
  T.id
 SUM(                                                                          
    (case when T.criteria1 = 'foo' then 1 else 0 end)  
    ...                              
  ) 
...

任何想法将不胜感激。

Answer 1

你可以简单地

ORDER BY (T.criteria1 IS NOT DISTINCT FROM 'foo')::integer
       + (T.criteria2 IS NOT DISTINCT FROM 'bar')::integer
       + (T.criteria3 IS NOT DISTINCT FROM 'baz')::integer
       + (T.criteria4 IS NOT DISTINCT FROM 'stackoverflow')::integer DESC