T-SQL | Пример FTI в цикле

Например, нужен полнотекстовый поиск адресов, представленных в пользовательской таблице, по данным справочника адресов Person.Address в поле AddressLine1 из базы данных AdventureWorks

 

Пользовательская таблица (SearchAddressResult):

 

id

– id искомой записи

SearchText

– искомый текст (адрес)

FoundAddressID

– найденный AddressID в Person.Address

 

В результате поиска необходимо отредактировать данные таблицы по полю FoundAddressID

 

Если в таблицу добавить поле Rank и в процессе поиска проставить ранг совпадения, то можно отфильтровать "плохо" найденные адреса.

 

Пример таблицы (SearchAddressResult):

 

 

Для поиска адресов вызываем  функцию FREETEXTTABLE (table, field, search_string, language, top_n_by_rank)

 

table

– Где искать: имя таблицы, в которой настроен полнотекстовый индекс;

field

– Где искать: имя индексированного поля (указывается в процессе настройки полнотекстового индекса);

search_string

– Что искать: строка поиска

language

– Язык. Указывается, если нужно искать с учетом особенностей языка (переносы, синонимы и пр.)

top_n_by_rank

– При указании аргумента top_n_by_rank функция вернет первые n в порядке сортировки по рангу совпадений (самый высокий – наилучшее совпадение; самый низкий – наихудшее)

 

 

В приведенном примере аргументами FREETEXTTABLE будут следующие:

 

(Person.Address, AddressLine1, @SearchText, language 'English', 1)

 

Person.Address

Таблица, в которой ищем (полнотекстовый индекс настроен);

AddressLine1

Поле, по которому ищем (указан в качестве полнотекстового индекса)

@SearchText

Переменная процедуры поиска в цикле

language 'English'

Учет особенностей англ. языка

1

По рангу совпадений нужна первая строка (строки с наилучшим совпадением всегда сверху)

 

 


Пример запроса полнотекстового поиска по первому адресу из пользовательской таблицы:

 

use AdventureWorks

select * from freetexttable(Person.Address, AddressLine1, 'Landing Avenue, 3747', language 'English', 1)

 

 

 


Найденный адрес в Person.Address:

 

use AdventureWorks

 

Select * from Person.Address where AddressID =

(select [key] from freetexttable(Person.Address, AddressLine1, 'Landing Avenue, 3747', language 'English', 1))

 

 

 


Т.о., если бы задача решалась вручную, то вручную можно было бы проставить данные по первой строке
(поля SearchAddressResult.FoundAddressID и SearchAddressResult.Rank):

 

 

 

 


Процедура поиска и обновления данных по полям SearchAddressResult.FoundAddressID и SearchAddressResult.Rank в цикле:

 

USE AdventureWorks;

 

declare @FoundAddressID int, @SearchText nvarchar(1000);

 

DECLARE contact_cursor CURSOR FOR

SELECT id, SearchText FROM SearchAddressResult;

OPEN contact_cursor;

 

-- Perform the first fetch.

FETCH NEXT FROM contact_cursor

into @FoundAddressID, @SearchText;

 

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.

WHILE @@FETCH_STATUS = 0

BEGIN

 

 --print @SearchText

 exec

('update dbo.SearchAddressResult set [Rank] =

(select [Rank] from freetexttable(Person.Address, AddressLine1, '''+@SearchText+''', language ''English'', 1))

where id = '+@FoundAddressID );

 

 exec

('update SearchAddressResult set FoundAddressId =

(select [key] from freetexttable(Person.Address, AddressLine1, '''+@SearchText+''', language ''English'', 1))

where id=' + @FoundAddressID);

 

 FETCH NEXT FROM contact_cursor

 into @FoundAddressID, @SearchText;

END

 

CLOSE contact_cursor;

DEALLOCATE contact_cursor;

 

 


Обновленные данные пользовательской таблицы:

 

 

 


Если построить запрос связующий SearchAddressResult.FoundAddressID и Person.Address.AddressID и отсортировать данные по рангу, то можно как-то понять с какого ранга результаты поиска действительно релевантны (в приведенном примере, очевидно, это те, ранг которых > 80):

 

use Adventureworks

select  

id

,sar.SearchText

,pa.AddressLine1

,sar.[Rank]

from dbo.SearchAddressResult sar

left join Person.Address pa

on pa.AddressID = sar.FoundAddressID

order by [Rank]

 

 

 

В общем виде, в результате анализа релевантности найденных значений и рангов, можно добавить условие Rank > n в связующем запросе (или на уровне процедуры*):

 

use Adventureworks

select  

sar.id

,sar.SearchText

,case when [Rank] > 80 then pa.AddressLine1 else null end as AddressLine1

from dbo.SearchAddressResult sar

left join Person.Address pa

ON pa.AddressID = sar.FoundAddressID

order by [Rank]

 

 

 

 

 


*

USE AdventureWorks;

 

declare @FoundAddressID int, @SearchText nvarchar(1000);

 

DECLARE contact_cursor CURSOR FOR

SELECT id, SearchText FROM SearchAddressResult;

OPEN contact_cursor;

 

-- Perform the first fetch.

FETCH NEXT FROM contact_cursor

into @FoundAddressID, @SearchText;

 

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.

WHILE @@FETCH_STATUS = 0

BEGIN

 

 --print @SearchText

 exec

('update SearchAddressResult set FoundAddressId =

(select case when [rank] > 80 then [key] else null end from freetexttable(Person.Address, AddressLine1, '''+@SearchText+''', language ''English'', 1))

where id=' + @FoundAddressID);

 

 FETCH NEXT FROM contact_cursor

 into @FoundAddressID, @SearchText;

END

 

CLOSE contact_cursor;

DEALLOCATE contact_cursor;

 

 



© 2018 | Анна Петросян | pashelp@yandex.ru