Странность ранжирования SQL Server с использованием FREETEXTTABLE по нескольким столбцам

Question

Я изо всех сил пытался понять, как полнотекстовый поиск в SQL Server оценивает мои результаты.

Рассмотрим следующее FREETEXTTABLE поиск:

DECLARE @SearchTerm varchar(55) = 'Peter Alex'

SELECT ftt.[RANK], v.*
FROM FREETEXTTABLE (vMembersFTS, (Surname, FirstName, MiddleName, MemberRef, Passport), @SearchTerm) ftt
INNER JOIN vMembersFTS v ON v.ID = ftt.[KEY]
ORDER BY ftt.[RANK] DESC;

Это возвращает следующие результаты и рейтинги:

RANK  ID   MemberRef  Passport    FirstName   MiddleName   Surname    Salutation  
----- ---- ---------- ----------- ----------- ------------ ---------- ------------
18    2    AB-002                 Pete                     Peters     
18    9    AB-006                 George                   Alex       Mr Alex
18    13   AB-009                 Peter       David        Alex       Mr Alex
14    3    AB-003                 Peter       Alex         Jones                  

Как вы можете судить по результатам, опубликованным выше, последняя строка, хотя и имеет, как я считаю, хорошее совпадение и для "Петра", и для "Алекса", появляется с рангом только 14, где результат в первом Строка имеет только один матч на "Питер" (по общему признанию, фамилия "Петерс").

Это надуманный пример, но он иллюстрирует мои разочарования и недостаток знаний.

Я потратил немало времени на исследования, но сейчас чувствую себя немного не в своей тарелке. Я уверен, что я делаю что-то глупое, например, поиск по нескольким столбцам.

Я приветствую вашу помощь и поддержку. Заранее спасибо.

Спасибо,

Кэйн

(Кстати, я использую SQL Server 2012)

Вот SQL, который вы можете использовать, чтобы повторить тест самостоятельно:

-- Create the Contacts table.
CREATE TABLE dbo.Contacts
(
    ID          int         NOT NULL PRIMARY KEY,
    FirstName   varchar(55) NULL,
    MiddleName  varchar(55) NULL,
    Surname     varchar(55) NOT NULL,
    Salutation  varchar(55) NULL,
    Passport    varchar(55) NULL
);
GO

-- Create the Members table.
CREATE TABLE dbo.Members
(
    ContactsID  int         NOT NULL PRIMARY KEY,
    MemberRef   varchar(55) NOT NULL
);
GO

-- Create the FTS view.
CREATE VIEW dbo.vMembersFTS WITH SCHEMABINDING AS
SELECT  c.ID, 
        m.MemberRef,
        ISNULL(c.Passport, '') AS Passport,
        ISNULL(c.FirstName, '') AS FirstName,
        ISNULL(c.MiddleName, '') AS MiddleName, 
        c.Surname, 
        ISNULL(c.Salutation, '') AS Salutation
FROM dbo.Contacts c 
INNER JOIN dbo.Members AS m ON m.ContactsID = c.ID 
GO

-- Create the view index for FTS.
CREATE UNIQUE CLUSTERED INDEX IX_vMembersFTS_ID ON dbo.vMembersFTS (ID);
GO

-- Create the FTS catalogue and stop-list.
CREATE FULLTEXT CATALOG ContactsFTSCatalog WITH ACCENT_SENSITIVITY = OFF;
CREATE FULLTEXT STOPLIST ContactsSL FROM SYSTEM STOPLIST;
GO

-- Create the member full-text index.
CREATE FULLTEXT INDEX ON dbo.vMembersFTS
    (Surname, Firstname, MiddleName, Salutation, MemberRef, Passport)
KEY INDEX IX_vMembersFTS_ID
ON ContactsFTSCatalog
WITH STOPLIST = ContactsSL;
GO



-- Insert some data.
INSERT INTO Contacts VALUES (1, 'John', NULL, 'Smith', NULL, NULL);
INSERT INTO Contacts VALUES (2, 'Pete', NULL, 'Peters', NULL, NULL);
INSERT INTO Contacts VALUES (3, 'Peter', 'Alex', 'Jones', NULL, NULL);
INSERT INTO Contacts VALUES (4, 'Philip', NULL, 'Smith', NULL, NULL);
INSERT INTO Contacts VALUES (5, 'Harry', NULL, 'Dukes', NULL, NULL);
INSERT INTO Contacts VALUES (6, 'Joe', NULL, 'Jones', NULL, NULL);
INSERT INTO Contacts VALUES (7, 'Alex', NULL, 'Phillips', 'Mr Phillips', NULL);
INSERT INTO Contacts VALUES (8, 'Alexander', NULL, 'Paul', 'Alex', NULL);
INSERT INTO Contacts VALUES (9, 'George', NULL, 'Alex', 'Mr Alex', NULL);
INSERT INTO Contacts VALUES (10, 'James', NULL, 'Castle', NULL, NULL);
INSERT INTO Contacts VALUES (11, 'John', NULL, 'Alexander', NULL, NULL);
INSERT INTO Contacts VALUES (12, 'Robert', NULL, 'James', 'Mr James', NULL);
INSERT INTO Contacts VALUES (13, 'Peter', 'David', 'Alex', 'Mr Alex', NULL);
INSERT INTO Members VALUES (1, 'AB-001');
INSERT INTO Members VALUES (2, 'AB-002');
INSERT INTO Members VALUES (3, 'AB-003');
INSERT INTO Members VALUES (5, 'AB-004');
INSERT INTO Members VALUES (8, 'AB-005');
INSERT INTO Members VALUES (9, 'AB-006');
INSERT INTO Members VALUES (11, 'AB-007');
INSERT INTO Members VALUES (12, 'AB-008');
INSERT INTO Members VALUES (13, 'AB-009');



-- Run the FTS query.
DECLARE @SearchTerm varchar(55) = 'Peter Alex'
SELECT ftt.[RANK], v.*
FROM FREETEXTTABLE (vMembersFTS, (Surname, FirstName, MiddleName, MemberRef, Passport), @SearchTerm) ftt
INNER JOIN vMembersFTS v ON v.ID = ftt.[KEY]
ORDER BY ftt.[RANK] DESC;

Answer 1

Ранг присваивается на основе порядка в вашем запросе:

DECLARE @SearchTerm varchar(55) = 'Peter Alex'
SELECT ftt.[RANK], v.*
FROM FREETEXTTABLE (vMembersFTS, (Surname, FirstName, MiddleName, MemberRef, Passport), @SearchTerm) ftt
INNER JOIN vMembersFTS v ON v.ID = ftt.[KEY]
ORDER BY ftt.[RANK] DESC;

Так что в вашем случае совпадение по SurName превосходит FirstName, а оба имеют преимущество по MiddleName.

Ваши лучшие 3 результата имеют рейтинг 18, ​​так как все три соответствуют фамилии. Последняя запись имеет рейтинг 14 для сопоставления по FirstName и MiddleName, но не по SurName.

Вы можете найти подробную информацию о расчете ранга здесь: https://technet.microsoft.com/en-us/library/ms142524(v=sql.105).aspx

Если вы хотите выделить равный вес для них, вы можете, но вам придется использовать CONTAINSTABLE и не FREETEXTTABLE,

Информацию можно найти здесь: https://technet.microsoft.com/en-us/library/ms189760(v=sql.105).aspx