📄 65731.htm
字号:
<link href="./dzs_cs.css" rel="stylesheet" type="text/css" /><table width="96%" border="0" align="center" cellpadding="0" cellspacing="0"> <tr> <td> </td> </tr> <tr> <td height="24" align="center" valign="bottom" class="d_font3">如何理解SQL Server中的Ranking</td> </tr> <tr> <td height="3" bgcolor="#E3E3E3"></td> </tr> <tr> <td> </td> </tr> <tr> <td class="d_font4"> <P>【51CTO.com独家特稿】MS SQL Server全文检索能够产生一个积分（即Rank值），它反映了查询数据返回结果的相关性。这里每行Rank可以被视为一种排序条件，可以反映排序所定的相关性。</P>
<P>注：Rank值一般与特定的用户查询相关(如使用FREETEXT子句)，并不针对一般意义性质的查询。</P>
<P><STRONG>Rank排名统计信息</STRONG> </P>
<P>当一个索引一旦被建立，其相关统计信息即被收集用于排名。建立全文目录的处理过程并不直接地建立一个单一的索引结构。实际上，微软的全文检索引擎SQL Server （ MSFTESQL）服务创造中间索引，数据均被有效地索引。事实上，MSFTESQL能够合并多个索引成为一个大的索引且这一过程将被重复多次，MSFTESQL接着进行“主合并” ，它合并了所有的中间索引成为一个大型的主索引。 </P>
<P>当计算排名查询结果集，其中一些统计数据，可以使用来自规模较小的中间索引，以满足查询和一些主索引。这将取决于是否有中间索引已经合并与否。因此，如果中间索引均不会被合并，排名的统计资料，可以在不同的精度。这也解释了为什么同样的查询，随着时间的推移，全文检索的索引数据的不断添加、修改、删除、并为规模较小的索引合并可以返回不同分级的结果。</P>
<P>为了最大限度地减少的面积索引和计算复杂性，统计数据往往会四舍五入处理。</P>
<P>下面的列表包括了一些常用的术语和统计信息值，这些值在计算排名中是很重要的：</P>
<P>◆Property<BR>全文索引栏的行。</P>
<P>◆Document <BR>该实体（Document）通过查询被返回。在SQL Server中，它相当于一行。一份Document可以有多个Property，就如同一行可以有多个建立了全文索引的栏位。</P>
<P>◆Index <BR>单独的反转索引包含一个或多个Document。它可能完全分布在内存或磁盘上。当查询结果匹配时，许多查询统计对于个别的索引而言是相对的。 <BR> <BR>◆Full-Text Catalog<BR>全文检索目录。为查询建立的中间索引的集合体，该目录对于SQL Server的管理员来说，要么被建立或者没有建立。</P>
<P>◆Word, Token or item 关键词<BR>全文搜索引擎搜索的基本单元， Documents文本源被分词成了Word或Token，不同语言其分词结果是不相同的。</P>
<P>◆Occurrence<BR>在文本属性(document property)中关键词Word的偏移量字长是由分词器所决定的。如在第一分词是在发生1处 ，接下来的在2处，依此类推。为了避免在短语和邻近的查询出现假负数结果，在最终的句点和段落尾巴归宿的引入发生较大的差异。</P>
<P>◆Catalog Key<BR>包含关键字的Word和property的组合。</P>
<P>◆HitCount<BR>在一行中有多少次关键字命中的次数。</P>
<P>◆IndexedRowCount<BR>索引行的总数。这种计数是基于中间索引基础上计算出来的。这个数字在精确度上可以存在着很大的不同。</P>
<P>◆KeyRowCount <BR>全文目录中包含关键词的总行数。</P>
<P>◆MaxOccurrence <BR>某一行property的最大匹配值,它可能存放在一个全文目录中。</P>
<P>◆MaxQueryRank<BR>MSFTESQL能够返回最大的Rank值为1000。</P>
<P><STRONG>Rank排名计算问题</STRONG></P>
<P>计算排名(Rank)的过程，取决于多项因素。不同的语言分词记号完全不同。例如，字符串“dog-house”可以被分成“dog”、“house” ，而另一种分词方法又可能把它分成“dog-house” 。这意味着，匹配和排序会基于语言的种类而有所不同，因为其中不仅有不同的关键字，还影响到文档Document的长度，而Document 长度差异可以直接影响到检索的排名。 </P>
<P>诸如IndexRowCount这样的统计信息可以有很大的不同。例如，如果有一个全文目录在主索引上包含有20亿记录行，然而基于中间主索引的比较文件这个统计会有偏差值。</P>
<P>基于此种原因，我们推荐使用ALTER FULLTEXT CATALOG ... REORGANIZE DDL命令去重建索引，以供发布重新的统计信息使用。</P>
<P>MSFTESQL将自动地合并基于根据中间索引大小和数值的参数而计算出来的索引。</P>
<P>MaxOccurrence的值被归类到1到32个层级。这意味着，比如在一份Document中50个关键字长等同于100个字长，下面的表即为范式化的。因为该文档长度在毗邻表值为32和128倍数之间 ，但它们被同样有效地视作为具有相同长度 128 （ 32 < docLength < = 128 ） 。</P>
<P>
<TABLE cellSpacing=0 borderColorDark=#ffffff cellPadding=2 width=400 align=center borderColorLight=black border=1>
<TBODY>
<TR>
<TD class=code bgColor=#e6e6e6><PRE>{<BR>16, 32, 128, 256, 512, 725, 1024, 1450, 2048, 2896, 4096, 5792, 8192, 11585, <BR>16384, 23170, 28000, 32768, 39554, 46340, 55938, 65536, 92681, 131072, 185363, <BR>262144, 370727, 524288, 741455, 1048576, 2097152, 4194304 <BR>}; </PRE></TD></TR></TBODY></TABLE></P>
<P>这里top_n_by_rank是用于新预先计算的rank选项，当计算排名一些附加值时，可能会四舍五入，所以对于一些预先计算的rank值上可能会存在一些差异,此时要确保预先计算rank选项可用。</P>
<P>#p#</P>
<P><STRONG>Ranking常量表</STRONG></P>
<P>
<TABLE cellSpacing=0 borderColorDark=#ffffff cellPadding=2 width=400 align=center borderColorLight=black border=1>
<TBODY>
<TR>
<TD class=code bgColor=#e6e6e6><PRE>StatisticalWeight = Log2( ( 2 + IndexedRowCount ) / KeyRowCount )<BR>Rank = min( MaxQueryRank, HitCount * 16 * StatisticalWeight / MaxOccurrence )</PRE></TD></TR></TBODY></TABLE></P>
<P>短语的匹配排名象个别Keys如KeyRowCount，估计计算结果可能不准确，会稍高于实际数字。</P>
<P><STRONG>ISABOUT的排序Ranking</STRONG><BR> <BR>ISABOUT是一种被称之为向量空间查询的传统信息检索术语。该排名算法（Rank algorithm）采用了著名的杰卡德公式。排名的计算方法先为每个条目做计算，然后得出结果，相关说明如下。</P>
<P>ContainsRank = 同样被用来计算 单条目的CONTAINSTABLE排序 (如下) 。Weight = 每个条目查询的权重值，默认为1。<BR>WeightedSum = Σ[key=1 to n] ContainsRankKey * WeightKey<BR>Rank = ( MaxQueryRank * WeightedSum ) / ( ( Σ[key=1 to n] ContainsRankKey2 ) <BR> + ( Σ[key=1 to n] WeightKey2 ) - ( WeightedSum ) ) <BR> <BR><STRONG>FREETEXT中的排序Ranking</STRONG> </P>
<P>FREETEXT排名是基于对奥卡皮BM25排名公式计算得出的。 FREETEXT查询会增加关键字并间接生成到查询（间接形式的原始查询词），这些关键词被当作独立个体分别加以处理，而从它们所派生出的同义词也被视作相同权重计算。每个关键词在查询中的统计将直接影响到排序Rank值。</P>
<P>Rank = Σ[Terms in Query] w ( ( ( k1 + 1 ) tf ) / ( K + tf ) ) * ( ( k3 + 1 ) qtf / ( k3 + qtf ) ) )</P>
<P>注：</P>
<P>W是Robertson-Sparck Jones的权重。</P>
<P>在这个公司中，w被定义为：</P>
<P>w = log10 ( ( ( r + 0.5 ) * ( N – R + r + 0.5 ) ) / ( ( R – r + 0.5 ) * ( n – r + 0.5 ) )<BR>N：被查询的property计算的索引行数。 <BR>n：包含关键字Word的行数。 <BR>K is ( k1 * ( ( 1 – b ) + ( b * dl / avdl ) ) ). <BR>dl：property长度，关键字 word出现的次数。<BR>Avdl：被查询的property平均长度，在关键字出现的次数。<BR>k1, b, and k3 分别为常量1.2, 0.75, and 8.0。 <BR>tf：在特定行中出现的在查询property中的频率字。<BR>Rank = Σ[Terms in Query] w ( ( ( k1 + 1 ) tf ) / ( K + tf ) ) * ( ( k3 + 1 ) qtf / ( k3 + qtf ) ) )</P>
<P>注：<BR> <BR>W是Robertson-Sparck Jones的权重。</P>
<P>在这个公司中，w被定义为：<BR>w = log10 ( ( ( r + 0.5 ) * ( N – R + r + 0.5 ) ) / ( ( R – r + 0.5 ) * ( n – r + 0.5 ) )</P>
<P>其中：</P>
<P>N：提交查询的Property被索引行的计数。 <BR>K ：( k1 * ( ( 1 – b ) + ( b * dl / avdl ) ) )。<BR>dl ： Property长度在关键字 word出现的次数。<BR>Avdl : 被查询的Property平均长度，在关键字出现的次数。</P>
<P>k1, b, and k3分别是常量1.2, 0.75, and 8.0。<BR>tf：在特殊行中出现的在查询Property中的频率字。</P>
<P>最后 ，让我们用一个实例来观察Rank计算结果，可以加深对以上Rank公式的理解。</P>
<P>
<TABLE cellSpacing=0 borderColorDark=#ffffff cellPadding=2 width=400 align=center borderColorLight=black border=1>
<TBODY>
<TR>
<TD class=code bgColor=#e6e6e6><PRE><P> CREATE TABLE RANK(id int IDENTITY,<BR> TITLE nvarchar(1000),<BR> Keyword nvarchar(30) ,<BR> context nvarchar(max), <BR> CONSTRAINT PK_RANK PRIMARY KEY(id)) <BR> INSERT RANK(TITLE, Keyword,Context)<BR> SELECT N'中国招商投资提供资金合同', '资金,合同' ,'2008中国招商投资提供资金合同</P><P> 中国招商投资提供资金合同中国招商投资提供资金合同'<BR> UNION ALL SELECT N'资金合同', '资金' ,'资金'<BR> UNION ALL SELECT N'大资金大合同', '资金' ,'资金'<BR> UNION ALL SELECT N'国际资金国际合同', '国际资金国际合同' ,</P><P> '1国际资金国际合同亚洲资金亚洲合同中国资金合同' <BR> UNION ALL SELECT N'国际资金国际合同', '国际资金国际合同' ,</P><P> '1国际资金国际合同亚洲资金亚洲合同中国资金合同' <BR> UNION ALL SELECT N'资金合同', '资金' ,'资金'<BR> UNION ALL SELECT N'国际资金合同', '资金' ,'资金'<BR> UNION ALL SELECT N'中国资金合同中国资金合同', '资金' ,'资金'<BR> UNION ALL SELECT N'国际资金国内资金国家资金', '资金' ,'资金'<BR> UNION ALL SELECT N'国家资金合同国际资金合同', '资金' ,'资金'<BR> UNION ALL SELECT N'国际资金合同', '资金' ,'资金'<BR> UNION ALL <BR> SELECT N' 大量资金大量资金大量资金大量资金大量资金大量资金大量资金大量资金 </P><P> 大量资金', '资金' ,'资金'<BR> UNION ALL <BR> SELECT N' 大量大量大量资金大量大量大量大量大量大量大量大量大量大量大量大量 </P><P> 大量大量', '资金' ,'资金'<BR> GO <BR> <BR> --初始化全文索引 <BR> EXEC sp_fulltext_database 'enable' <BR> GO <BR> <BR> --创建一个名为 DEMO 的全文目录 <BR> EXEC sp_fulltext_catalog 'DEMO', 'create' <BR> GO <BR> <BR> --创建并填充全文索引 <BR> EXEC sp_fulltext_table 'RANK','create','DEMO','PK_RANK' <BR> EXEC sp_fulltext_column 'RANK','TITLE','add',0x0804 <BR> EXEC sp_fulltext_column 'RANK','Keyword','add',0x0804 <BR> EXEC sp_fulltext_column 'RANK','context','add',0x0804 <BR> EXEC sp_fulltext_table 'RANK','start_change_tracking' <BR> EXEC sp_fulltext_table 'RANK','Start_background_updateindex' <BR> GO </P></PRE></TD></TR></TBODY></TABLE></P>
<P>请观察如下语句所产生的按rank值倒序排序返回的结果集：</P>
<P>
<TABLE cellSpacing=0 borderColorDark=#ffffff cellPadding=2 width=400 align=center borderColorLight=black border=1>
<TBODY>
<TR>
<TD class=code bgColor=#e6e6e6><PRE><P>SELECT f.RANK,TITLE,Keyword ,context<BR> FROM FREETEXTTABLE([rank], (title) ,'资金') AS f<BR> RIGHT JOIN [rank] AS p ON f.[key] = p.ID <BR> ORDER BY f.rank desc </P></PRE></TD></TR></TBODY></TABLE></P>
<P><STRONG>原文链接：</STRONG><A href="http://msdn2.microsoft.com/en-us/library/ms142524.aspx">http://msdn2.microsoft.com/en-us/library/ms142524.aspx</A></P>
<P><FONT size=4>【相关文章】</FONT></P>
<UL type=disc>
<LI><A href="http://database.51cto.com/art/200801/64072.htm" target=_blank><U><FONT color=blue>SQL Server 2005全文检索技术在网站上的应用实录</FONT></U></A></LI></UL>
<UL type=disc>
<LI><A href="http://database.51cto.com/art/200801/63562.htm" target=_blank><U><FONT color=blue>SQL Server的文件恢复技术</FONT></U></A></LI></UL>
<UL type=disc>
<LI><A href="http://database.51cto.com/art/200712/61627.htm" target=_blank><U><FONT color=blue>SQL Server的新文件存储机制</FONT></U></A></LI></UL><DIV align=right>【责任编辑：<A class=ln href='mailto:sunsj@51cto.com'>火凤凰</A> TEL：（010）68476606】</DIV> </td> </tr> <tr> <td class="d_font4"> </td> </tr> </table>⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -