如何赋予完整匹配相对于部分匹配的权重 (PostgreSQL)

Question

我有一个查询，它需要输入搜索包含数万个条目（应该几乎是美国的每个城市）的位置表中邮政编码/地区/城市/地铁代码中最匹配的匹配项。我使用的查询是：

select
  metrocode,
  region,
  postalcode,
  region_full,
  city
from
  dv_location
where
(
  region ilike '%Chicago%'
 or
  postalcode ilike '%Chicago%'
 or
  city ilike '%Chicago%'
 or
  region_full ilike'%Chicago%'
)

 and metrocode is not null

奇怪的是，我得到的结果集看起来像这样：

metrocode;region;postalcode;region_full;city
862;CA;95712;California;Chicago Park
862;CA;95712;California;Chicago Park
602;IL;60611;Illinois;Chicago
602;IL;60610;Illinois;Chicago

我做错了什么？我的想法是，芝加哥比芝加哥公园具有更大的权重，因为芝加哥与该术语完全匹配（尽管我要求对该术语进行通配符匹配）。

Answer 1

您可以在 order by 子句中显式设置顺序。

...
order by
case
  when city ilike 'Chicago' then 1
  when city ilike 'Chicago%' then 2
  when city ilike '%Chicago%' then 3
  else 4
end

或者您希望对结果进行排序...

Answer 2

试试这个：

select
  metrocode,
  region,
  postalcode,
  region_full,
  city,
  (region = 'Chicago'
   OR postalcode = 'Chicago'
   OR city = 'Chicago'
   OR region_full = 'Chicago') AS full_match
from
  dv_location
where
(
  region ilike '%Chicago%'
 or
  postalcode ilike '%Chicago%'
 or
  city ilike '%Chicago%'
 or
  region_full ilike'%Chicago%'
)
 and metrocode is not null
 order by full_match desc;