Сначала заказывать по большинству согласованных записей

Учитывая следующий запрос:

select * from users 
where first_name ilike '%foo%' 
OR last_name ilike '%bar%' 
OR nickname ilike '%foobar%'

Возвращает:

 first_name| last_name   |  nickname
----------------------------------------
  Foo      | ABC         |   abcd
  Foo      | DEF         |   efgh
  Foo      | BAR         |   ijkl
  AMD      | Bar         |   foobar
  Foo      | Bar         |   foobar2

Вопрос:

Как сначала отсортировать наиболее релевантные (сопоставленные) значения? Я имею в виду, что большинство из них соответствуют нескольким шаблонам внутри Where.. OR

Ожидаемый результат:

 first_name| last_name   |  nickname
----------------------------------------
  Foo      | Bar         |   foobar2
  Foo      | BAR         |   ijkl
  AMD      | Bar         |   foobar
  Foo      | ABC         |   abcd
  Foo      | DEF         |   efgh

Ответы

Ответ 1

Закажите его по количеству просмотров:

... ORDER BY (first_name ILIKE '%foo%')::integer 
           + (last_name  ILIKE '%bar%')::integer
           + (nickname   ILIKE '%foobar%')::integer DESC

Ответ 2

вам нужно UTL_MATCH.edit_distance_similarity (col1, col2) (ссылка)

select * from users 
where first_name ilike '%foo%' 
OR last_name ilike '%bar%' 
OR nickname ilike '%foobar%'
order by UTL_MATCH.edit_distance_similarity(first_name, 'foo')
   + UTL_MATCH.edit_distance_similarity(last_name, 'bar')
   + UTL_MATCH.edit_distance_similarity(nickname, 'foobar')

UTL_MATCH.edit_distance_similarity возвращает процент сходства, т.е.

  • Супермен = Супермен => 100 [%]
  • Супермен = supermon => 88 [%]

Edit: Aaah, вы указали PostGreSql, этот оракул. Но поскольку у меня уже есть, похоже, это так или иначе... Edit3: Как отметил Laurenz Albe в комментариях, в postgreSql нет utl_matcvh. Поэтому вам нужно использовать модуль или его подход. Я оставлю этот ответ за полноту.

Edit2: Чтобы проверить сходство в PostGreSql, вы можете использовать модуль fuzzystrmatch Пример:

Модуль fuzzystrmatch предоставляет две функции для работы с кодами Soundex:

soundex (текст) возвращает текст и разницу (текст, текст) возвращает int

SELECT soundex('Anne'), soundex('Ann'), difference('Anne', 'Ann');