Как добиться ранжирования на основе соответствия предложения (без полнотекстового индексирования)

У меня есть поисковый запрос, который динамически компилируется с использованием механизма запросов критериев NHibernate. Результирующий SQL-запрос может выглядеть так:

select 
    *
from
    sometable
where
(
    (
        firstname like 'chris%' or
        lastname like 'chris%'
    )
    and
    (
        firstname like 'vann%' or
        lastname like 'vann%'
    )    
)

Данные в таблице могут выглядеть так:

FirstName         LastName
------------------------------
Chris             Smith
John              Vann
Chris             Vann

Я хотел бы упорядочить результаты таким образом, чтобы строка, соответствующая обоим подпунктам в предложении where (т. е. имя = Крис и фамилия = Ванн), оценивалась выше, чем строка, соответствующая только одному из подпунктов. Возможно ли это в стандартном SQL?

Изменить: я значительно упростил вопрос, чтобы разобраться в сути проблемы.


sql-server ranking
person Chris    schedule 30.01.2012    source источник
comment
Старый вопрос, я знаю, но оператор where будет совпадать только с обоими подпунктами из-за «и». Так что просто Крис Ванн в ваших выборочных данных (и гипотетический Ванн Крис). Похоже, вы хотите, чтобы Крис совпадал либо по имени, либо по фамилии ИЛИ Ванн совпадал по имени или фамилии, а затем оценивали тех, кто назначает совпадение, как в самом высоком рейтинге.   -  person Erikest    schedule 14.01.2019

Ответы (2)


arrow_upward
1
arrow_downward

Это только начало. Вы можете создать столбец вычисления priority и сортировать строки по этому столбцу. Столбец является индикатором строки с хорошим соответствием. Вот пример кода, написанного для вас:

create table #t (f varchar(10), l varchar(10) );

insert into #t values ('aa','ee'),('aa','ii'),('oo','ee');

select 
   *,
   case when f like 'aa%' then 1 else 0 end +
   case when l like 'aa%' then 1 else 0 end +
   case when f like 'ii%' then 1 else 0 end + 
   case when l like 'aa%' then 1 else 0 end 
   as priority
from #t
order by 
   priority desc

Полученные результаты:

f  l  priority 
-- -- -------- 
aa ee 4        
aa ii 4        
oo ee 0

Для вашей схемы может быть что-то вроде:

select 
    *.
    case when firstname like 'chris%' and lastname like 'vann%' then 4 else 0 +
    case when firstname like 'chris%' and lastname not like 'vann%' then 3 else 0 +
    case when firstname not like 'chris%' and lastname like 'vann%' then 3 else 0 +
    ...
    as priority
from
    sometable
where
(
    (
        firstname like 'chris%' or
        lastname like 'chris%'
    )
    and
    (
        firstname like 'vann%' or
        lastname like 'vann%'
    )    
)
order by priority desc
person dani herrera    schedule 30.01.2012
comment
Я думал об этом, но этот подход включает в себя множество специальных (непараметризованных) операторов SQL, чего я обычно стараюсь избегать. - person Chris; 31.01.2012
comment
@Chris, Да, я знаю, я опубликовал этот подход, потому что вы запрашиваете стандартный способ SQL для этого: возможно ли это в стандартном SQL? Как я уже сказал: это только отправная точка, возможно, это поможет вашему окончательному решению. С Уважением. - person dani herrera; 31.01.2012

arrow_upward
0
arrow_downward

Вот ранжирование T-SQL, которое я придумал, похоже, работает довольно хорошо.

  • Он ранжирует с помощью функции «Разница» каждую пару поиска: первый-первый, первый-последний, последний-первый, последний-последний, а затем добавляет вес, когда совпадение подстроки условия поиска находится в имени или фамилии с первым-первым и последние-последние спички тяжелее.
  • Далее он упорядочивает те, которые имеют точные совпадения подстрок, а затем имеют самые ранние совпадения, а затем имеют наименьшую разницу между длиной строки поиска и длиной имени/фамилии.
  • Весовые коэффициенты (* 2, * 4) в TotalRank являются произвольными и просто отражают мое желание придать больший вес совпадениям, которые являются первыми первыми и последними последними.
  • В приведенном ниже SQL есть много дополнительных столбцов, демонстрирующих компоненты, которые входят в столбец TotalRank. Очевидно, вы можете удалить их.

`

DECLARE @searchFirst varchar(max) = 'chris';

DECLARE @searchLast varchar(max) = 'vann';

SELECT firstname, lastname,

SOUNDEX(@searchFirst) as FSearchSoundEx,

SOUNDEX(firstname) as FSoundEx,

DIFFERENCE(firstname, @searchFirst) as FDiff,

LEN(firstName) - LEN(@searchFirst) as FFDelta,


SOUNDEX(lastname) as LSoundEx,

SOUNDEX(@searchLast) as LSearchSoundEx,

DIFFERENCE(lastName, @searchLast) as LDiff,

LEN(lastName) - LEN(@searchLast) as LLDelta,


PATINDEX('%' + @searchFirst + '%', firstname) as FFIndex,

PATINDEX('%' + @searchFirst + '%', lastname) as FLIndex,

PATINDEX('%' + @searchLast + '%', firstname) as LFIndex,

PATINDEX('%' + @searchLast + '%', lastname) as LLIndex,

CONVERT(BIT, PATINDEX('%' + @searchFirst + '%', firstname)) as HasFF,

CONVERT(BIT, PATINDEX('%' + @searchFirst + '%', lastname)) as HasFL, 

CONVERT(BIT, PATINDEX('%' + @searchLast + '%', firstname)) as HasLF,

CONVERT(BIT, PATINDEX('%' + @searchLast + '%', lastname)) as HasLL,

DIFFERENCE(firstname, @searchFirst) * DIFFERENCE(firstname, @searchFirst) as FFDiffSq, DIFFERENCE(lastname, @searchFirst) * DIFFERENCE(lastname, @searchFirst) as FLDiffSq, DIFFERENCE(firstname, @searchLast) * DIFFERENCE(firstname, @searchLast) as LFDiffSq, DIFFERENCE(lastname, @searchLast) * DIFFERENCE(lastname, @searchLast) as LLDiffSq,

DIFFERENCE(firstname, @searchFirst) * DIFFERENCE(firstname, @searchFirst) + DIFFERENCE(lastname, @searchFirst) * DIFFERENCE(lastname, @searchFirst) + DIFFERENCE(firstname, @searchLast) * DIFFERENCE(firstname, @searchLast) + Difference(lastname, @searchLast) * Difference(lastname, @searchLast) as SumDiffSquares,

DIFFERENCE(firstname, @searchFirst) * DIFFERENCE(firstname, @searchFirst) * 2 + DIFFERENCE(lastname, @searchFirst) * DIFFERENCE(lastname, @searchFirst) + DIFFERENCE(firstname, @searchLast) * DIFFERENCE(firstname, @searchLast) + DIFFERENCE(lastname, @searchLast) * DIFFERENCE(lastname, @searchLast) * 2
+ CONVERT(BIT, PATINDEX('%' + @searchFirst + '%', firstname)) * 4 + CONVERT(BIT, PATINDEX('%' + @searchFirst + '%', lastname)) + CONVERT(BIT, PATINDEX('%' + @searchLast + '%', firstname)) + CONVERT(BIT, PATINDEX('%' + @searchLast + '%', lastname)) * 4 as TotalRank

FROM Contacts

ORDER BY TotalRank Desc, HasLL Desc, HasFF Desc, HasFL Desc, HasLF Desc, LLIndex, FFIndex, FLIndex, LFIndex, LLDelta, FFDelta
person Erikest    schedule 14.01.2019