SQL多条件模糊查询解决方案(类似百度搜索)
时间:2012-10-18 16:22:32 点击:
377
核心提示:前言: 算法的基本特性在前几篇博客中已经做了详细的说明,经过不断的改进优化,到归仓的时候了,也就是说,该算法告一段落,不再更新。 作为最终的解决方案,简要的总结一下算法特性,以方便读者参阅。l 目的:... 前言:
算法的基本特性在前几篇博客中已经做了详细的说明,经过不断的改进优化,到归仓的时候了,也就是说,该算法告一段落,不再更新。
作为最终的解决方案,简要的总结一下算法特性,以方便读者参阅。
l 目的:主要用于多条件模糊匹配。
l 贪婪特性:返回满足条件尽可能多的记录。
l 权重特性:为关键词分配权重,代表关键词的重要性,在不破坏贪婪特性的前提下,返回权重高的记录。
l 必要关键词指定特性:在不破坏贪婪特性和权重特性的前提下,返回的结果中必须包含指定的关键词。
l 典型应用:问-答系统,例如百度提问、京东商品咨询。
经过分析,在最终的解决方案中,提供两个版本的算法,已经封装成存储过程和函数,直接导入数据库即可。
普通版本:
l 描述:基于SQL的LIKE语句实现,使用简单,但受限于LIKE语句,不适合超大数据量处理。指定必要词会加快处理速度。
l 使用范围:万级别的数据量,数据量超过1万条,将导致运行缓慢。
l 使用方法:直接在查询分析器中运行脚本导入数据库即可。
l 调用示例:execute proc_Common_SuperLike'id','t_test','content','20','|','[i]|o|c'
l 参数说明:id表的主键字段名称。t_test表名。content匹配内容字段名称。20选出20个记录(从顶至下匹配度越来越低)。|关键字的分隔符号。[i]|o|c一共有i,o,c三个关键字,通过|分隔,其中i是必要词。
- GO
- CREATE function Get_StrArrayLength
- (
- @str varchar(1024), --要分割的字符串
- @split varchar(10) --分隔符号
- )
- returns int
- as
- begin
- declare @location int
- declare @start int
- declare @length int
- set @str=ltrim(rtrim(@str))
- set @location=charindex(@split,@str)
- set @length=1
- while @location<>0
- begin
- set @start=@location 1
- set @location=charindex(@split,@str,@start)
- set @length=@length 1
- end
- return @length
- end
- GO
- CREATE function Get_StrArrayStrOfIndex
- (
- @str varchar(1024), --要分割的字符串
- @split varchar(10), --分隔符号
- @index int --取第几个元素
- )
- returns varchar(1024)
- as
- begin
- declare @location int
- declare @start int
- declare @next int
- declare @seed int
- set @str=ltrim(rtrim(@str))
- set @start=1
- set @next=1
- set @seed=len(@split)
- set @location=charindex(@split,@str)
- while @location<>0 and @index>@next
- begin
- set @start=@location @seed
- set @location=charindex(@split,@str,@start)
- set @next=@next 1
- end
- if @location =0 select @location =len(@str) 1
-
- --这儿存在两种情况:1、字符串不存在分隔符号 2、字符串中存在分隔符号,跳出while循环后,@location为0,那默认为字符串后边有一个分隔符号。
- return substring(@str,@start,@location-@start)
- end
- GO
- CREATE PROCEDURE proc_Common_SuperLike
- --要查询的表的主键字段名称
- @primaryKeyName varchar(999),
- --要查询的表名
- @talbeName varchar(999),
- --要查询的表的字段名称,即内容所在的字段
- @contentFieldName varchar(999),
- --查询记录的个数(TOP *),匹配的个数越多,排名越靠前
- @selectNumber varchar(999),
- --匹配字符分隔标记
- @splitString varchar(999),
- --匹配字符组合字符串
- @words varchar(999)
-
- AS
- declare @sqlFirst varchar(999)
- declare @sqlCenter varchar(999)
- declare @sqlLast varchar(999)
- declare @next int
- declare @arrayLength int
- declare @newWords varchar(999)
- declare @newTable varchar(999)
- BEGIN
- set @newTable=@talbeName
- set @newWords=@words
- set @next=dbo.Get_StrArrayLength(@words,'[')
- --判断是否有必要词
- if @next>1
- begin
- set @newTable=''
- --构造必要表sql语句
- while @next>1
- begin
- set @newTable=@newTable @contentFieldName ' like ''%' dbo.Get_StrArrayStrOfIndex(dbo.Get_StrArrayStrOfIndex(@words,'[',@next),']',1) '%'' AND '
- set @next=@next-1
- end
- set @newTable=left(@newTable,(len(@newTable)-4))
- --构造临时表
- set @newTable='SELECT * into ##tempTable FROM ' @talbeName ' WHERE ' @newTable
- execute(@newTable)
- --指定临时表
- set @newTable='##tempTable'
- --去掉关键词组中的必要词标记
- set @newWords=REPLACE(REPLACE(@words,'[',''),']','')
- end
- set @sqlCenter=''
- set @next=1
- set @arrayLength=dbo.Get_StrArrayLength(@newWords,@splitString)
-
- while @next<=@arrayLength
- begin
- --构造sql查询条件(中间部分)
- set @sqlCenter = @sqlCenter 'SELECT ' @primaryKeyName ',' CONVERT(varchar(999),@arrayLength-@next 1) ' AS wordPower FROM ' @newTable ' WHERE ' @contentFieldName ' like ''%' dbo.Get_StrArrayStrOfIndex(@newWords,@splitString,@next) '%'' UNION ALL '
- set @next=@next 1
- end
- --处理sql语句中间部分,去除最后无用语句
- set @sqlCenter=left(@sqlCenter,(len(@sqlCenter)-10))
- --构造sql语句开头部分
- set @sqlFirst='SELECT TOP ' @selectNumber ' ' @primaryKeyName ',COUNT(*) SUM(wordPower) AS finalPower FROM ('
- --构造sql语句结尾部分
- set @sqlLast=') AS t_Temp GROUP BY ' @primaryKeyName ' ORDER BY finalPower DESC'
- --拼接出完整sql语句,并执行
- Execute(@sqlFirst @sqlCenter @sqlLast)
- --判断临时表是否存在,存在则删除,一定要删除!
- if OBJECT_ID('tempDb..##tempTable') is not null
- begin
- drop table ##tempTable
- end
- END
大数据量版本:
l 描述:基于SQL的全文索引实现,使用较为复杂,但执行速度极快,适合处理大数据量。指定必要词会降低处理速度。
l 使用范围:千万级别的数据量,i3一代笔记本处理器,查询1千万条记录仅需2秒。
l 使用方法:在查询分析器中运行脚本导入数据库,再为要查询的表创建全文索引,索引字段设置为要查询的字段。
l 调用示例:execute proc_Common_SuperLike'id','t_test','content','20','|','[i]|o|c'
l 参数说明:id表的主键字段名称。t_test表名。content匹配内容字段名称。20选出20个记录(从顶至下匹配度越来越低)。|关键字的分隔符号。[i]|o|c一共有i,o,c三个关键字,通过|分隔,其中i是必要词。
- GO
- CREATE function Get_StrArrayLength
- (
- @str varchar(1024), --要分割的字符串
- @split varchar(10) --分隔符号
- )
- returns int
- as
- begin
- declare @location int
- declare @start int
- declare @length int
- set @str=ltrim(rtrim(@str))
- set @location=charindex(@split,@str)
- set @length=1
- while @location<>0
- begin
- set @start=@location 1
- set @location=charindex(@split,@str,@start)
- set @length=@length 1
- end
- return @length
- end
- GO
- CREATE function Get_StrArrayStrOfIndex
- (
- @str varchar(1024), --要分割的字符串
- @split varchar(10), --分隔符号
- @index int --取第几个元素
- )
- returns varchar(1024)
- as
- begin
- declare @location int
- declare @start int
- declare @next int
- declare @seed int
- set @str=ltrim(rtrim(@str))
- set @start=1
- set @next=1
- set @seed=len(@split)
- set @location=charindex(@split,@str)
- while @location<>0 and @index>@next
- begin
- set @start=@location @seed
- set @location=charindex(@split,@str,@start)
- set @next=@next 1
- end
- if @location =0 select @location =len(@str) 1
-
- --这儿存在两种情况:1、字符串不存在分隔符号 2、字符串中存在分隔符号,跳出while循环后,@location为0,那默认为字符串后边有一个分隔符号。
- return substring(@str,@start,@location-@start)
- end
- GO
- CREATE PROCEDURE proc_Common_SuperLike
- --要查询的表的主键字段名称
- @primaryKeyName varchar(999),
- --要查询的表名
- @talbeName varchar(999),
- --要查询的表的字段名称,即内容所在的字段
- @contentFieldName varchar(999),
- --查询记录的个数(TOP *),匹配的个数越多,排名越靠前
- @selectNumber varchar(999),
- --匹配字符分隔标记
- @splitString varchar(999),
- --匹配字符组合字符串
- @words varchar(999)
-
- AS
- declare @sqlFirst varchar(999)
- declare @sqlCenter varchar(999)
- declare @sqlLast varchar(999)
- declare @next int
- declare @arrayLength int
- declare @newTable varchar(999)
- BEGIN
- set @newTable=''
- set @sqlCenter=''
- set @next=1
- set @arrayLength=dbo.Get_StrArrayLength(@words,@splitString)
-
- while @next<=@arrayLength
- begin
- --构造sql查询条件(中间部分)
- --判断是否是必要词
- if CHARINDEX('[',dbo.Get_StrArrayStrOfIndex(@words,@splitString,@next))>0
- begin
- set @sqlCenter = @sqlCenter 'SELECT ' @primaryKeyName ',' CONVERT(varchar(999),@arrayLength-@next 1) ' AS wordPower FROM ' @talbeName ' WHERE CONTAINS(' @contentFieldName ',''"*' REPLACE(REPLACE(dbo.Get_StrArrayStrOfIndex(@words,@splitString,@next),'[',''),']','') '*"'') UNION ALL '
- --构造必要词
- set @newTable=@newTable 'CONTAINS(' @contentFieldName ',''"*' REPLACE(REPLACE(dbo.Get_StrArrayStrOfIndex(@words,@splitString,@next),'[',''),']','') '*"'') AND '
- end
- else
- begin
- set @sqlCenter = @sqlCenter 'SELECT ' @primaryKeyName ',' CONVERT(varchar(999),@arrayLength-@next 1) ' AS wordPower FROM ' @talbeName ' WHERE CONTAINS(' @contentFieldName ',''"*' dbo.Get_StrArrayStrOfIndex(@words,@splitString,@next) '*"'') UNION ALL '
- end
-
- set @next=@next 1
- end
- --判断是否有必要词
- if CHARINDEX('[',@words)>0
- begin
- ---处理必要词部分,去除最后无用语句
- set @newTable=left(@newTable,(len(@newTable)-4))
- set @newTable='AS t_Temp WHERE ' @primaryKeyName ' IN (SELECT ' @primaryKeyName ' FROM ' @talbeName ' WHERE ' @newTable ')'
- end
- else
- begin
- set @newTable='AS t_Temp'
- end
-
- --处理sql语句中间部分,去除最后无用语句
- set @sqlCenter=left(@sqlCenter,(len(@sqlCenter)-10))
- --构造sql语句开头部分
- set @sqlFirst='SELECT TOP ' @selectNumber ' ' @primaryKeyName ',COUNT(*) SUM(wordPower) AS finalPower FROM ('
- --构造sql语句结尾部分
- set @sqlLast=') ' @newTable ' GROUP BY ' @primaryKeyName ' ORDER BY finalPower DESC'
- --拼接出完整sql语句,并执行
- Execute(@sqlFirst @sqlCenter @sqlLast)
- END
附-SQL数据库表全文索引创建指南:
--开启全文索引
sp_fulltext_database enable
--创建索引目录(创建出来是一个目录,用来放索引文件)
CREATE FULLTEXT CATALOG 索引目录名称 --例如myFullText
--创建全文索引
CREATE FULLTEXT INDEX ON 表名(字段名) --为哪个表的哪个字段创建全文索引,例如t_test(content)
KEY INDEX 主键索引名称 ON 索引目录名称 --注意是主键索引名称,而不是主键字段名称!例如,PK__t_test__3213E83F0EA330E9;指定全文索引目录,即放在哪个目录下,例如myFullText
注意:如果在创建数据库表全文索引之前,数据库表中已经有大量记录,那么创建全文索引是需要时间的,因此创建完全文索引后马上使用可能查不到数据。
作者:蜀都互联 来源:www.zcool8.com
|