sql-server-2008 - tabla - update subquery sql server

¿Cómo se podría usar Lucene.NET para ayudar a implementar la búsqueda en un sitio como Stack Overflow?

Question

He hecho una pregunta similar en Meta Stack Overflow , pero eso trata específicamente de si Lucene.NET se usa o no en Stack Overflow.

El objetivo de la pregunta aquí es más bien hipotético, acerca de qué enfoques se tomarían si usaran Lucene.NET como base para la búsqueda en el sitio y otros factores en un sitio como Stack Overflow [SO].

Según la entrada en el blog Stack Overflow titulada " SQL 2008 Problemas de búsqueda de texto completo ", había una fuerte indicación de que Lucene.NET estaba siendo considerado en algún momento, pero parece que definitivamente no es el caso, según el comentario de Geoff Dalgas el 19 de febrero de 2010:

Lucene.NET no se está utilizando para el desbordamiento de pila; estamos utilizando la indexación de texto completo de SQL Server. La búsqueda es un área donde seguimos haciendo ajustes menores.

Entonces mi pregunta es, ¿cómo se podría utilizar Lucene.NET en un sitio que tenga la misma semántica de Stack Overflow?

Aquí hay algunos antecedentes y lo que he hecho / pensado hasta ahora (sí, he estado implementando la mayor parte de esto y la búsqueda es el último aspecto que tengo que completar):

Tecnologías:

• ASP.NET MVC
• SQL Server 2008
• .NET 3.5
• C # 3.0

Y, por supuesto, la estrella del espectáculo, Lucene.NET.

La intención también es pasar a .NET / C # 4.0 lo antes posible. Si bien no creo que sea un cambio de juego, debe tenerse en cuenta.

Antes de entrar en aspectos de Lucene.NET, es importante señalar los aspectos de SQL Server 2008, así como los modelos involucrados.

Modelos

Este sistema tiene más de un tipo de modelo primario en comparación con el desbordamiento de pila. Algunos ejemplos de estos modelos son:

• Preguntas: Estas son preguntas que las personas pueden hacer. La gente puede responder preguntas, al igual que en Stack Overflow.
• Notas: Estas son proyecciones unidireccionales, por lo que a diferencia de una pregunta, usted está haciendo una declaración sobre el contenido. La gente no puede publicar respuestas a esto.
• Eventos: estos son datos sobre un evento en tiempo real. Tiene información de ubicación, información de fecha / hora.

Lo importante a tener en cuenta acerca de estos modelos:

• Todos ellos tienen una propiedad de nombre / título (texto) y una propiedad de cuerpo (HTML) (los formatos son irrelevantes, ya que el contenido se analizará adecuadamente para el análisis).
• Cada instancia de un modelo tiene una URL única en el sitio

Luego están las cosas que Stack Overflow proporciona qué IMO, son decoradores para los modelos. Estos decoradores pueden tener diferentes cardinalidades, ya sea de uno a uno o de uno a muchos:

• Votos: clave en el usuario
• Respuestas: Opcional, como ejemplo, vea el caso de Notes arriba
• Favorito: ¿El modelo está en la lista de favoritos de un usuario?
• Comentarios (opcional)
• Asociaciones de etiquetas: las etiquetas están en una tabla separada, para no replicar la etiqueta para cada modelo. Hay un enlace entre el modelo y la tabla de asociaciones de etiquetas, y luego desde la tabla de asociaciones de etiquetas hasta la tabla de etiquetas.

Y hay cuentas de apoyo que en sí mismas son decoradores uno a uno para los modelos que están codificados de la misma manera (generalmente por un tipo de id modelo y la ID del modelo):

• Conteo de votos: total de votos positivos, negativos, intervalo de puntuación de Wilson (esto es importante, determinará el nivel de confianza basado en los votos para una entrada, en su mayor parte, suponga el límite inferior del intervalo de Wilson).

Las respuestas (respuestas) son modelos que tienen la mayoría de los decoradores que tienen la mayoría de los modelos, simplemente no tienen un título o una url, y si el modelo tiene una respuesta es opcional. Si se permiten respuestas, es por supuesto una relación de uno a muchos.

SQL Server 2008

Las tablas siguen básicamente el diseño de los modelos anteriores, con tablas separadas para los decoradores, así como algunas tablas y vistas de apoyo, procedimientos almacenados, etc.

Cabe señalar que la decisión de no utilizar la búsqueda de texto completo se basa principalmente en el hecho de que no normaliza puntajes como Lucene.NET. Estoy abierto a sugerencias sobre cómo utilizar la búsqueda basada en texto, pero tendré que realizar búsquedas en varios tipos de modelos, así que tenga en cuenta que voy a necesitar normalizar el puntaje de alguna manera .

Lucene.NET

Aquí es donde está el gran signo de interrogación. Aquí están mis pensamientos hasta ahora sobre la funcionalidad de Stack Overflow, así como sobre cómo y qué he hecho.

Indexación

Preguntas / Modelos

Creo que cada modelo debe tener un índice propio que contenga una identificación única para buscarlo rápidamente en función de una instancia de Término de esa identificación (indexado, no analizado).

En esta área, he considerado que Lucene.NET analice cada pregunta / modelo y cada respuesta individualmente. Entonces, si hubiera una pregunta y cinco respuestas, la pregunta y cada una de las respuestas se indexarían como una unidad por separado.

La idea aquí es que la puntuación de relevancia que devuelve Lucene.NET sería más fácil de comparar entre los modelos que proyectan de diferentes maneras (por ejemplo, algo sin respuestas).

Como ejemplo, una pregunta establece el tema, y ​​luego la respuesta se desarrolla sobre el tema.

Para una nota, que no tiene respuestas, maneja la cuestión de presentar el tema y luego elaborarlo.

Creo que esto ayudará a que los puntajes de relevancia sean más relevantes el uno para el otro.

Etiquetas

Inicialmente, pensé que estos deberían mantenerse en un índice separado con múltiples campos que tienen los identificadores de los documentos en el índice del modelo apropiado. O, si es demasiado grande, hay un índice con solo las etiquetas y otro índice que mantiene la relación entre el índice de etiquetas y las preguntas a las que se aplican. De esta manera, cuando haces clic en una etiqueta (o usas la estructura de URL), es fácil ver de forma progresiva que solo tienes que "comprar" si tienes éxito:

• Si la etiqueta existe
• A qué preguntas están asociadas las etiquetas
• Las preguntas en sí mismas

Sin embargo, en la práctica, hacer una consulta de todos los elementos basados ​​en etiquetas (como hacer clic en una etiqueta en Stack Overflow) es extremadamente fácil con SQL Server 2008. Según el modelo anterior, simplemente requiere una consulta como:

select
     m.Name, m.Body
from
    Models as m
        left outer join TagAssociations as ta on
            ta.ModelTypeId = <fixed model type id> and
            ta.ModelId = m.Id
        left outer join Tags as t on t.Id = ta.TagId
where
    t.Name = <tag>

Y dado que ciertas propiedades se comparten en todos los modelos, es bastante fácil hacer UNION entre diferentes tipos / tablas de modelos y producir un conjunto coherente de resultados.

Esto sería análogo a un TermQuery en Lucene.NET (me refiero a la documentación de Java, ya que es completa, y Lucene.NET pretende ser una traducción línea por línea de Lucene , por lo que toda la documentación es la misma).

El problema que surge con el uso de Lucene.NET aquí es el del orden de clasificación. El puntaje de relevancia para un TermQuery cuando se trata de etiquetas es irrelevante. Es 1 o 0 (lo tiene o no).

En este punto, el puntaje de confianza (intervalo de puntuación de Wilson) entra en juego para ordenar los resultados.

Este puntaje podría almacenarse en Lucene.NET, pero para ordenar los resultados en este campo, se basaría en los valores que se almacenan en la memoria caché de campo, que es algo que realmente quiero evitar. Para una gran cantidad de documentos, el caché de campo puede crecer mucho (el puntaje de Wilson es el doble, y necesitaría uno doble para cada documento, que puede ser un conjunto grande).

Dado que puedo cambiar la instrucción de SQL a la orden basada en el intervalo de puntuación de Wilson como este:

select
     m.Name, m.Body
from
    Models as m
        left outer join TagAssociations as ta on
            ta.ModelTypeId = <fixed model type id> and
            ta.ModelId = m.Id
        left outer join Tags as t on t.Id = ta.TagId
        left outer join VoteTallyStatistics as s on
            s.ModelTypeId = ta.ModelTypeId and
            s.ModelId = ta.ModelId
where
    t.Name = <tag>
order by
    --- Use Id to break ties.
    s.WilsonIntervalLowerBound desc, m.Id

Parece una opción fácil de usar para manejar la función de Stack Overflow "obtener todos los elementos etiquetados con <tag>".

Respuestas

Originalmente, pensé que esto está en un índice separado propio, con una clave en el índice de Preguntas.

Creo que debería haber una combinación de cada modelo y cada respuesta (si hay una) para que las puntuaciones de relevancia en los diferentes modelos sean más "iguales" cuando se comparen entre sí.

Esto, por supuesto, inflaría el índice. Estoy algo cómodo con eso ahora mismo.

¿O hay una forma de almacenar, por ejemplo, los modelos y las respuestas como documentos individuales en Lucene.NET y luego tomar ambos y poder obtener el puntaje de relevancia para una consulta que trate ambos documentos como uno solo ? Si es así, entonces esto sería ideal .

Por supuesto, existe la pregunta de qué campos se almacenarán, indexarán y analizarán (todas las operaciones pueden ser operaciones separadas o mix-and-matching). ¿Cuánto indexaría uno?

¿Qué pasa con el uso de stemmers / porters especiales para errores de ortografía (usando Metaphone) y también sinónimos (hay terminología en la comunidad que ofreceré, que tiene su propia jerga / terminología para ciertas cosas que tiene múltiples representaciones)?

Aumentar

Esto está relacionado con la indexación, por supuesto, pero creo que merece su propia sección.

¿Estás potenciando campos y / o documentos? Si es así, ¿cómo los impulsas? ¿El impulso es constante para ciertos campos? O se vuelve a calcular para campos en los que se aplica el voto / vista / datos favoritos / externos.

Por ejemplo, en el documento, ¿el título recibe un impulso sobre el cuerpo? Si es así, ¿qué factores de impulso crees que funcionan bien? ¿Qué pasa con las etiquetas?

La idea aquí es la misma que en Stack Overflow. Los términos en el documento tienen relevancia, pero si un documento está etiquetado con el término, o está en el título, entonces debe ser potenciado.

Shashikant Kore sugiere una estructura de documentos como esta:

• Título
• Pregunta
• Respuesta aceptada (o respuesta altamente votada si no hay una respuesta aceptada)
• Todas las respuestas combinadas

Y luego usar el impulso pero no basado en el valor del voto sin procesar. Creo que tengo eso cubierto con el intervalo de puntuación de Wilson.

La pregunta es, ¿debería aplicarse el impulso a todo el documento? Me inclino por no en este caso, porque significaría que tendría que reindexar el documento cada vez que un usuario votara en el modelo.

Buscar elementos etiquetados

Originalmente pensé que cuando consultaba una etiqueta (haciendo clic específicamente en una o usando la estructura URL para buscar contenido etiquetado), eso es un TermQuery simple contra el índice de la etiqueta, luego en el índice de asociaciones (si es necesario) y luego de vuelta a las preguntas, Lucene.NET maneja esto realmente rápido.

Sin embargo, dadas las notas anteriores sobre lo fácil que es hacer esto en SQL Server, he optado por esa ruta cuando se trata de buscar elementos etiquetados.

Búsqueda general

Entonces, la pregunta más destacada es cuando se hace una frase general o búsqueda de términos en función del contenido, ¿qué y cómo se integra otra información (como los votos) para determinar los resultados en el orden correcto? Por ejemplo, al realizar esta búsqueda en ASP.NET MVC en Stack Overflow , estos son los recuentos para los cinco primeros resultados (cuando se utiliza la pestaña de relevancia):

    q votes answers accepted answer votes asp.net highlights mvc highlights
    ------- ------- --------------------- ------------------ --------------
         21      26                    51                  2              2
         58      23                    70                  2              5
         29      24                    40                  3              4
         37      15                    25                  1              2
         59      23                    47                  2              2

Tenga en cuenta que los aspectos más destacados están solo en el título y resumen en la página de resultados y son solo indicadores menores en cuanto a cuál es la frecuencia del término verdadero en el documento, título, etiqueta, respuesta (sin importar cómo se apliquen, que es otra buena pregunta).

¿Cómo se combina todo esto?

En este punto, sé que Lucene.NET devolverá un puntaje de relevancia normalizado, y los datos del voto me darán un intervalo de puntuación de Wilson que puedo usar para determinar el puntaje de confianza.

¿Cómo debo considerar la combinación de dos puntajes de tese para indicar el orden de clasificación del conjunto de resultados según la relevancia y la confianza?

Es obvio para mí que debería haber alguna relación entre los dos, pero qué relación debería ser evadida en este momento. Sé que tengo que refinarlo a medida que pasa el tiempo, pero realmente estoy perdido en esta parte.

Mis pensamientos iniciales son si el puntaje de relevancia está entre 0 y 1 y el puntaje de confianza está entre 0 y 1, entonces podría hacer algo como esto:

1 / ((e ^ cs) * (e ^ rs))

De esta forma, uno obtiene un valor normalizado que se acerca a 0, el más relevante y confiable es el resultado, y puede ordenarse sobre eso.

El principal problema es que si se realiza un aumento en la etiqueta y / o campo de título, entonces la puntuación de relevancia está fuera de los límites de 0 a 1 (el extremo superior se vuelve ilimitado entonces, y no sé cómo manejar eso )

Además, creo que tendré que ajustar el puntaje de confianza para dar cuenta de los votos que son completamente negativos. Dado que los conteos de votos que son completamente negativos dan como resultado un intervalo de puntuación de Wilson con un límite inferior de 0, algo con -500 votos tiene el mismo puntaje de confianza que algo con -1 voto, o 0 votos.

Afortunadamente, el límite superior disminuye de 1 a 0 a medida que aumentan los votos negativos. Podría cambiar el puntaje de confianza por un rango de -1 a 1, así:

confidence score = votetally < 0 ? 
    -(1 - wilson score interval upper bound) :
    wilson score interval lower bound

El problema con esto es que al insertar 0 en la ecuación se clasificarán todos los elementos con cero votos debajo de aquellos con votos negativos.

Con ese fin, estoy pensando si el puntaje de confianza se usará en una ecuación recíproca como la anterior (me preocupa el desbordamiento obviamente), entonces debe ser modificado para que siempre sea positivo. Una forma de lograr esto es:

confidence score = 0.5 + 
    (votetally < 0 ? 
        -(1 - wilson score interval upper bound) :
        wilson score interval lower bound) / 2

Mis otras preocupaciones son cómo realizar realmente el cálculo dado Lucene.NET y SQL Server. Dudo en poner el puntaje de confianza en el índice de Lucene porque requiere el uso del caché de campo, lo que puede tener un gran impacto en el consumo de memoria (como se mencionó anteriormente).

Una idea que tuve fue obtener el puntaje de relevancia de Lucene.NET y luego usar un parámetro con valores de tabla para transmitir el puntaje a SQL Server (junto con los identificadores de los elementos a seleccionar), en cuyo punto realizaría el cálculo con el puntaje de confianza y luego devuelva los datos debidamente ordenados.

Como dije antes, hay muchas otras preguntas que tengo sobre esto, y las respuestas han comenzado a enmarcar las cosas, y seguirán ampliándose a medida que las preguntas y las respuestas se hayan evocado.

Answer 1

Creo que Lucene no es bueno para este trabajo. Necesita algo realmente rápido con alta disponibilidad ... como SQL. Pero, ¿quiere código abierto?

Le sugiero que use Sphinx - http://www.sphinxsearch.com/ Es mucho mejor, y estoy hablando con experiencia, los usé a ambos.

Sphinx es increíble. Realmente es.

Answer 2

Determinar la relevancia siempre es complicado. Necesitas descubrir lo que estás tratando de lograr. ¿Su búsqueda intenta proporcionar una coincidencia exacta para un problema que alguien podría tener o está tratando de proporcionar una lista de artículos recientes sobre un tema?

Una vez que haya calculado lo que desea devolver, puede observar el efecto relativo de cada característica que está indexando. Eso va a tener una búsqueda difícil. A partir de ahí, modifica según los comentarios de los usuarios (sugiero usar comentarios implícitos en lugar de explícitos, de lo contrario molestarás al usuario).

En cuanto a la indexación, debe tratar de poner los datos para que cada elemento tenga toda la información necesaria para clasificarlo. Esto significa que tendrás que tomar los datos de varias ubicaciones para crearlos. Algunos sistemas de indexación tienen la capacidad de agregar valores a los elementos existentes, lo que facilitaría la adición de puntajes a las preguntas cuando aparecieran respuestas posteriores. La simplicidad simplemente haría que reconstruyese la pregunta cada cierto tiempo.

Answer 3

El índice de lucene tendrá los siguientes campos:

• Título
• Pregunta
• Respuesta aceptada (o respuesta altamente votada si no hay una respuesta aceptada)
• Todas las respuestas combinadas

Todos estos son campos que se analizan. La normalización de longitud está desactivada para obtener un mejor control en la puntuación.

El orden antes mencionado de los campos también refleja su importancia en orden descendente. Es decir, si la coincidencia de consulta en el título es más importante que en la respuesta aceptada, todo lo demás permanece igual.

El n. ° de upvotes es para la pregunta y la respuesta superior se puede capturar aumentando esos campos. Sin embargo, el recuento de votos acumulados sin procesar no se puede usar como valores de impulso, ya que podría sesgar los resultados dramáticamente. (Una pregunta con 4 votos ascendentes obtendrá el doble del puntaje de uno con 2 votos hacia arriba.) Estos valores deben ser amortiguados agresivamente antes de que puedan ser utilizados como factor de impulso. Usar algo del logaritmo natural (para votos ascendentes> 3) se ve bien.

El título puede ser aumentado por un valor un poco más alto que el de la pregunta.

Aunque la interconexión de preguntas no es muy común, tener un peso básico similar a un pagerank para una pregunta podría arrojar algunos resultados interesantes.

No considero las etiquetas de la pregunta como información muy valiosa para la búsqueda. Las etiquetas son agradables cuando solo quieres navegar por las preguntas. La mayoría de las veces, las etiquetas son parte del texto, por lo que la búsqueda de las etiquetas coincidirá con la pregunta. Esto está abierto a discusión, sin embargo.

Se realizará una consulta de búsqueda típica en los cuatro campos.

+(title:query question:query accepted_answer:query all_combined:query)

Este es un boceto amplio y requerirá un ajuste significativo para llegar a valores de refuerzo correctos y pesos correctos para consultas, si es necesario. La experiencia mostrará los pesos adecuados para las dos dimensiones de calidad: relevancia e importancia. Puede complicar las cosas introduciendo recencia como un parámetro de cambio. La idea aquí es que, si ocurre un problema en una versión particular del producto y se corrige en revisiones posteriores, las nuevas preguntas podrían ser más útiles para el usuario.

Se pueden agregar algunos giros interesantes para buscar. Alguna forma de búsqueda básica de sinónimos podría ser útil si solo se encuentran unos "pocos" resultados coincidentes. Por ejemplo, "descrease java heap size" es lo mismo que "reducir el tamaño del montón de Java". Pero, entonces, también significará que la "reducción del mapa" comenzará a coincidir con la "disminución del mapa". (El corrector ortográfico es obvio, pero supongo que los programadores deletrearían sus consultas correctamente).

Answer 4

Las respuestas que estás buscando realmente no se pueden encontrar solo con lucene. Necesita algoritmos de clasificación y agrupamiento para filtrar y comprender los datos y cómo se relacionan. Lucene puede ayudarlo a obtener datos normalizados, pero luego necesita el algoritmo correcto.

Te recomendaría que revises uno o todos los libros siguientes, te ayudarán con las matemáticas y te ayudarán a apuntar en la dirección correcta:

Algoritmos de la web inteligente

Inteligencia Colectiva en Acción

Programación de inteligencia colectiva

Answer 5

Probablemente hayas pensado más en este tema que la mayoría de las personas que intentarán responderte (parte de la razón por la que ha pasado un día y soy tu primera respuesta, me imagino). Voy a tratar de abordar tus últimas tres preguntas, porque hay muchas en las que no tengo tiempo para entrar, y creo que esas tres son las más interesantes (las preguntas de implementación física probablemente se desarrollen). para terminar siendo ''elegir algo, y luego ajustarlo a medida que aprendes más'').

datos de voto No estoy seguro de que los votos hagan algo más relevante para una búsqueda, francamente, simplemente los hace más populares. Si eso tiene sentido, estoy tratando de decir que si una publicación determinada es relevante para su pregunta es, en su mayoría, independiente de si fue relevante para otras personas. Dicho esto, probablemente haya al menos una correlación débil entre las preguntas interesantes y las que la gente querría encontrar. Los datos de votación probablemente sean más útiles para realizar búsquedas basadas únicamente en datos, por ejemplo, búsquedas de tipo "más popular". En las búsquedas genéricas basadas en texto, probablemente no le daría ningún peso a los votos al principio, pero consideraría trabajar en un algoritmo que tal vez proporcione un ligero peso para la clasificación (por lo tanto, no se devuelven los resultados, sino un impulso menor al pedido). de ellos).

respuestas Estoy de acuerdo con su enfoque aquí, sujeto a algunas pruebas; recuerde que esto tendrá que ser un proceso iterativo basado en los comentarios de los usuarios (por lo que deberá recopilar métricas sobre si las búsquedas arrojaron resultados exitosos para el buscador)

otro No olvide la puntuación del usuario también. Por lo tanto, los usuarios obtienen puntos en SO también, y eso influye en su rango predeterminado en las respuestas de cada pregunta que responden (parece que es principalmente para el desempate en respuestas que tienen el mismo número de golpes)