Limitar los resultados de la búsqueda con RANK

Se aplica a:SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Las funciones CONTAINSTABLE y FREETEXTTABLE devuelven una columna denominada RANK que contiene valores ordinales de 0 a 1000 (valores de clasificación). Estos valores se utilizan para clasificar las filas devueltas en función del grado de coincidencia con los criterios de selección. Los valores de clasificación solo indican un orden relativo de relevancia de las filas en el conjunto de resultados, con un valor inferior que indica la relevancia menor. Los valores reales carecen de relevancia y normalmente difieren cada vez que se ejecuta la consulta.

El número de elementos que coincide con una condición de búsqueda suele ser grande. Para evitar que las consultas CONTAINSTABLE o FREETEXTTABLE devuelvan demasiadas coincidencias, se recomienda usar el parámetro opcional top_n_by_rank, que solo devuelve un subconjunto de filas. top_n_by_rank es un valor entero, donde n especifica que solo se devuelvan las n coincidencias con la clasificación más alta en orden descendente. Si se combina top_n_by_rank con otros parámetros, es posible que la consulta devuelva menos filas de las que en realidad coinciden con todos los predicados.

SQL Server ordena las coincidencias por orden de clasificación y devuelve solo hasta el número especificado de filas. Esta opción puede aumentar significativamente el rendimiento. Por ejemplo, una consulta que por lo general devolvería 100 000 filas de una tabla de 1 millón se procesará de forma más rápida si solo se solicitan las 100 primeras filas.

Ejemplos del uso de RANK para limitar los resultados de la búsqueda

Ejemplo A: buscar solo las tres primeras coincidencias

En el ejemplo siguiente se usa CONTAINSTABLE para devolver las tres primeras coincidencias.

USE AdventureWorks2022;
GO

SELECT K.RANK,
    AddressLine1,
    City
FROM Person.Address AS A
INNER JOIN CONTAINSTABLE(Person.Address, AddressLine1, 'ISABOUT ("des*",
    Rue WEIGHT(0.5),
    Bouchers WEIGHT(0.9))', 3) AS K
    ON A.AddressID = K.[KEY];
GO

El conjunto de resultados es el siguiente:

RANK        Address                          City
----------- -------------------------------- ------------------------------
172         9005, rue des Bouchers           Paris
172         5, rue des Bouchers              Orleans
172         5, rue des Bouchers              Metz

Ejemplo B: buscar las cinco primeras coincidencias

En el ejemplo siguiente se utiliza CONTAINSTABLE para devolver la descripción de los 5 productos más destacados, donde la columna Description contiene la palabra “aluminum” cerca de la palabra light o de la palabra lightweight.

USE AdventureWorks2022;
GO

SELECT FT_TBL.ProductDescriptionID,
    FT_TBL.Description,
    KEY_TBL.RANK
FROM Production.ProductDescription AS FT_TBL
INNER JOIN CONTAINSTABLE(Production.ProductDescription,
    Description, '(light NEAR aluminum) OR (lightweight NEAR aluminum)', 5) AS KEY_TBL
        ON FT_TBL.ProductDescriptionID = KEY_TBL.[KEY];
GO

Cómo se clasifican los resultados de la consulta de búsqueda

La búsqueda de texto completo de SQL Server puede generar una puntuación (o valor de rango) opcional que indica la relevancia de los datos devueltos por una consulta de texto completo. Este valor de rango se calcula en cada fila y se puede utilizar como criterio de ordenación para ordenar el conjunto de resultados de una consulta determinada por relevancia. Los valores de clasificación solo indican un orden de importancia relativa de las filas en el conjunto de resultados. Los valores reales carecen de relevancia y normalmente difieren cada vez que se ejecuta la consulta. El valor de clasificación no tiene importancia en las consultas.

Estadísticas de clasificación

Cuando se compila un índice, se recopilan estadísticas para usarlas en la clasificación. El proceso de generación de un catálogo de texto completo no produce directamente una única estructura de índice. En su lugar, el motor de búsqueda de texto completo para SQL Server crea índices intermedios a medida que se indizan los datos. El motor de búsqueda de texto completo combina después dichos índices en un índice mayor, según sea necesario. Este proceso puede repetirse muchas veces. A continuación, el motor de búsqueda de texto completo realiza una "mezcla maestra" que combina todos los índices intermedios en un gran índice maestro.

En cada índice intermedio se recopilan estadísticas, que se mezclan cuando se mezclan los índices. Algunos valores estadísticos solo pueden generarse durante el proceso de mezcla maestra.

Mientras se clasifica un conjunto de resultados de consulta, SQL Server utiliza las estadísticas del índice intermedio más grande. Dependerá de si se han combinado o no los índices intermedios. Como resultado, las estadísticas de clasificación serán más o menos precisas si no se han combinado los índices intermedios. Por este motivo, una misma consulta puede devolver con el tiempo distintos resultados de rango, a medida que se agreguen, modifiquen y eliminen los datos de índices de texto completo, y se vayan combinando los índices de menor tamaño.

Para minimizar el tamaño del índice y la complejidad del cálculo, se suelen redondear las estadísticas.

En la siguiente lista se mencionan algunos términos y valores estadísticos de uso frecuente que son importantes para calcular la clasificación.

Problemas de cálculo de clasificación

El proceso de cálculo de clasificación depende de varios factores. Los separadores de palabras de cada uno de los idiomas acortan el texto de forma distinta. Por ejemplo, un separador de palabras podría separar la cadena "dog-house" en "dog" "house" y otro en "dog-house". Por este motivo, las coincidencias y la clasificación variarán en función del idioma especificado, ya que no solo son distintas las palabras sino también la longitud del documento. La diferencia de longitud del documento puede afectar a las categorías de todas las consultas.

Las estadísticas como IndexRowCount pueden variar enormemente. Por ejemplo, si un catálogo tiene 2.000 millones de filas en el índice maestro, un nuevo documento se indizará en un índice intermedio de la memoria y los rangos de dicho documento basados en el número de documentos en el índice almacenado en la memoria podrían desviarse de los rangos de los documentos del índice maestro. Por este motivo, se recomienda que después de cualquier rellenado que da lugar a un gran número de filas que se indexan o se vuelvan a indexar, los índices se combinen en un índice maestro mediante la instrucción ALTER FULLTEXT CATALOG ... REORGANIZETransact-SQL. El motor de búsqueda de texto completo también combinará automáticamente los índices basándose en parámetros como el número y el tamaño de los índices intermedios.

Los valores deMaxOccurrence se normalizan en 1 de 32 intervalos. Esto significa que, por ejemplo, un documento de 50 palabras se trata igual que un documento de 100 palabras. A continuación se muestra la tabla utilizada para la normalización. Dado que las longitudes del documento están en la clasificación entre los valores de tabla adyacentes 32 y 128, se tratan eficazmente como si tuvieran la misma longitud, 128 (32<docLength<= 128).

{ 16, 32, 128, 256, 512, 725, 1024, 1450, 2048, 2896, 4096, 5792, 8192, 11585,
16384, 23170, 28000, 32768, 39554, 46340, 55938, 65536, 92681, 131072, 185363,
262144, 370727, 524288, 741455, 1048576, 2097152, 4194304 };

Clasificación de CONTAINSTABLE

La clasificación deCONTAINSTABLE usa el algoritmo siguiente:

StatisticalWeight = Log2( ( 2 + IndexedRowCount ) / KeyRowCount )
Rank = min( MaxQueryRank, HitCount * 16 * StatisticalWeight / MaxOccurrence )

Las coincidencias de frases se clasifican del mismo modo que las claves individuales, excepto en que KeyRowCount (número de filas que contienen la frase) se calcula y puede ser impreciso y superior al número real.

Clasificación de NEAR

CONTAINSTABLE admite las consultas de dos o más términos de búsqueda que estén próximos, mediante la opciónNEAR. El valor de rango de cada fila devuelta se basa en varios parámetros. Un factor de rango importante es el número total de coincidencias (o aciertos) en relación con la longitud del documento. Por ejemplo, si un documento de 100 palabras y un documento de 900 palabras contienen las mismas coincidencias, el de 100 palabras tiene un rango superior.

La longitud total de cada acierto en una fila también contribuirá a la clasificación de esa fila en función de la distancia entre los términos de búsqueda primero y último de ese acierto. Cuanto menor sea la distancia, más contribuye el acierto al valor de clasificación de la fila. Si en una consulta de texto completo no se especifica un entero como la distancia máxima, un documento que solo tenga aciertos y cuyas distancias sean mayores de 100 términos lógicos tendrá una clasificación de 0.

Clasificación de ISABOUT

CONTAINSTABLE permite consultar los términos ponderados utilizando la opciónISABOUT. ISABOUT es lo que se denomina consulta de espacio vectorial en la terminología tradicional de recuperación de información. El algoritmo de clasificación predeterminado que se utiliza es Jaccard, una fórmula muy conocida. La clasificación se calcula para cada término de la consulta y se combina del modo que se describe en el algoritmo siguiente.

ContainsRank = same formula used for CONTAINSTABLE ranking of a single term (above).
Weight = the weight specified in the query for each term. Default weight is 1.
WeightedSum = Σ[key=1 to n] ContainsRankKey * WeightKey
Rank =  ( MaxQueryRank * WeightedSum ) / ( ( Σ[key=1 to n] ContainsRankKey^2 )
      + ( Σ[key=1 to n] WeightKey^2 ) - ( WeightedSum ) )

Clasificación de FREETEXTTABLE

La clasificación deFREETEXTTABLE se basa en la fórmula de clasificación OKAPI BM25. Las consultas FREETEXTTABLE agregarán palabras a la consulta a través de la generación de formas con inflexión de las palabras originales de la consulta; estas palabras se tratan como palabras independientes sin ninguna relación especial con las palabras a partir de las cuales se generan. Los sinónimos que se generan con la característica de diccionario de sinónimos se tratan como términos independientes del mismo peso. Cada palabra de la consulta contribuye al rango.

Rank = Σ[Terms in Query] w ( ( ( k1 + 1 ) tf ) / ( K + tf ) ) * ( ( k3 + 1 ) qtf / ( k3 + qtf ) ) )
Where:
w is the Robertson-Sparck Jones weight.
In simplified form, w is defined as:
w = log10 ( ( ( r + 0.5 ) * ( N - R + r + 0.5 ) ) / ( ( R - r + 0.5 ) * ( n - r + 0.5 ) )
N is the number of indexed rows for the property being queried.
n is the number of rows containing the word.
K is ( k1 * ( ( 1 - b ) + ( b * dl / avdl ) ) ).
dl is the property length, in word occurrences.
avdl is the average length of the property being queried, in word occurrences.
k1, b, and k3 are the constants 1.2, 0.75, and 8.0, respectively.
tf is the frequency of the word in the queried property in a specific row.
qtf is the frequency of the term in the query.

Contenido relacionado

• Consultar con búsqueda de texto completo