T-SQL 存储过程返回谷歌风格的“建议"搜索结果

好的，使用 SQL Server 2008.在我的网页上，我有一个带有 jQ​​uery-UI AutoComplete 的文本框.

现在我需要一个存储过程来搜索来自文本框/自动完成 AJAX 调用的搜索字符串，并返回建议的"搜索字符串.我正在使用 AdventureWorks db 进行测试(产品表)

例如，产品表包含产品名称和产品编号(以及其他)列，我想根据用户输入返回建议的搜索字符串，他们可以在其中输入产品名称和/或产品编号.

我让它在一个简单的列中工作.有什么想法吗?

解决方案

我将建议全文搜索(MS 或 Lucene 可以工作) 下面的代码使用 MSSQL FTS 作为我在我的应用程序中使用的瞬间.

如果您还没有安装 FTS 搜索.如果你有检查服务正在运行.在管理工作室中运行它来设置目录并添加产品表；和颜色/名称/产品编号到目录.

使用 [AdventureWorks]走创建全文目录 [ProductsTest]WITH ACCENT_SENSITIVITY = OFF授权 [dbo]走使用 [AdventureWorks]走CREATE FULLTEXT INDEX ON [Production].[Product] KEY INDEX [PK_Product_ProductID] ON ([ProductsTest]) WITH (CHANGE_TRACKING AUTO)走使用 [AdventureWorks]走ALTER FULLTEXT INDEX ON [Production].[Product] ADD ([Color])走使用 [AdventureWorks]走ALTER FULLTEXT INDEX ON [Production].[Product] ADD ([Name])走使用 [AdventureWorks]走ALTER FULLTEXT INDEX ON [Production].[Product] ADD ([ProductNumber])走使用 [AdventureWorks]走ALTER FULLTEXT INDEX ON [Production].[Product] ENABLE走

然后您可以一次对所有列运行查询；例如银色(选择颜色和名称)

Select * from production.product wherecontains(*, '"银*"')

查询中的 * 会找到 Silver*，因此您可以在用户输入时使用它来构建结果.需要考虑的一件事是 google 实时进行这项工作 - 如果您要搜索大量数据能够在不中断用户打字的情况下取回数据.我认为通常人们通过从他们正在寻找的第一个字母开始输入来使用这些搜索 - 我接受会有拼写错误 - 你可以在他们按下的每个空格之后实施拼写检查器，也许可以处理这个问题.或者存储运行的搜索并查看拼写错误并更改代码以根据映射(或使用自定义同义词库在 FTS 中处理).

排名对任何企业来说都是一个有趣的发展问题；您是在寻找 Mountain Frame 的第一个结果 - 还是想按销售额或价格对它们进行加权?如果用户输入多个文本术语，您可以使用 FTS 根据搜索字符串生成排名.

选择aa.rank, bb.*来自 containstable(production.product, *, '"Mountain" and "Silver*"') aa内连接生产.product bb在 aa.[key] = bb.productid按等级降序排列

这将返回 30 行；并根据用户输入的文本进行权重来确定第一名记录.在任何一种情况下，您都可能希望添加编码排名来调整结果以满足您的业务需求 - 对价格最高的小部件 1 进行排名可能不是这样.这就是为什么您要存储人们搜索/点击的内容，以便稍后分析结果.

有一个非常好的语言解析器用于 .Net 进行翻译输入到 FTS'able 语言中的谷歌样式字符串查询，可让您熟悉使用您网站的任何布尔搜索.

您可能还想通过审核用户输入的内容并最终访问并使用成功地图来更改最终建议，以使其真正与用户相关，从而添加一些群体智慧功能.

最后的建议，如果这是一个商业网站，你可能想看看 Easyask，这是一个可怕的伟大的自然语言处理器>

Ok, using SQL Server 2008. On my web page I have a textbox with jQuery-UI AutoComplete hooked up.

Now I need a stored procedure to search across all columns of a single table(or multiple joined tables I suppose) for a search string coming from the textbox/autocomplete AJAX call, and return "suggested" search strings. I am using the AdventureWorks db for testing(Products table)

So for example, the product table has columns for product name and product number(among others) and I want to return suggested search strings based on user input where they may enter a product name and/or a product number.

I have it working across a single column which was simple. Any ideas?

解决方案

I'm going to suggest full text search (MS' or Lucene will work) The code below use MSSQL FTS as its what I use in my app at the moment.

Install FTS Search if you haven't already. If you have check the service is running. In management studio run this to setup a catalog and add the products table; and Color / Name / Product Number to the catalog.

USE [AdventureWorks]
GO
CREATE FULLTEXT CATALOG [ProductsTest]WITH ACCENT_SENSITIVITY = OFF
AUTHORIZATION [dbo]

GO

USE [AdventureWorks]
GO
CREATE FULLTEXT INDEX ON [Production].[Product] KEY INDEX [PK_Product_ProductID] ON ([ProductsTest]) WITH (CHANGE_TRACKING AUTO)
GO
USE [AdventureWorks]
GO
ALTER FULLTEXT INDEX ON [Production].[Product] ADD ([Color])
GO
USE [AdventureWorks]
GO
ALTER FULLTEXT INDEX ON [Production].[Product] ADD ([Name])
GO
USE [AdventureWorks]
GO
ALTER FULLTEXT INDEX ON [Production].[Product] ADD ([ProductNumber])
GO
USE [AdventureWorks]
GO
ALTER FULLTEXT INDEX ON [Production].[Product] ENABLE
GO

You can then run queries against all columns at once; e.g. Silver (Chosen as its in color and Name)

Select * from production.product where
contains(*, '"Silver*"')

The * on the query will find Silver* so you can use this to build up results as the user types in. One thing to consider is that google make this work in real time - if you are searching a lot of data you to be able to get the data back without interrupting the typing of the user. i think generally people use these searches by typing from the first letter they are looking for - i accept there will be spelling mistakes- you could implement a spell checker after every space they press perhaps to handle that. Or store the searches that are run and look at the mispellings and change the code to handle that based on a mapping (or in FTS using a custom thesaurus.)

Ranking is going to be a fun development issue to any business; are you finding the first result for Mountain Frame -or do you want to weight them by sales or price? If the user types in more than one text term you can use FTS to produce a ranking based on the search string.

select aa.rank, bb.* 
From containstable(production.product, *, '"Mountain" and "Silver*"') aa
inner join production.product bb
on aa.[key] = bb.productid
order by rank desc

This returns 30 rows; and weights based on the user inputted text to determine the first place record. In either case you will likely want to add a coded ranking to tweak the results to suit your business desires - ranking te highest priced widget 1 might not be the way. That is why you are going to store what people searched for / clicked on so you can analyse the results later.

There is a really nice language parser for .Net that translates a google style string query inputted into FTS'able language which gives familiarity for any boolean searches that use your site.

You may also want to add some wisdom of crowds features by auditing against what users have input and ultimately gone to visit and use success maps to alter the final suggestions to actually make them relevant to the user.

As a final suggestion if this is a commercial website you might want to look at Easyask which is a scary great natural language processor