consulta con los resultados mas relevantes

[pithon]

Hola quiero hacer una consulta con los resultados mas relevante pero no se muy bien como hacerlo, uso against con un campo llamado concepto para la busqueda aparte tengo otro campo llamado fecha.
Con estos dos campos quiero mostrar los resultados mas relevantes para la busqueda y si hay registros que tienen la misma relevancia mostrar el orden segun la fecha del otro campo.
hice esto pero solo me muestra el orden por fecha mas alta.

id
1
2
3

fecha
20/20/2000
21/20/2000
22/20/2000

concepto
pc usados seminuevos
pc seminuevos
pc usados

Código MySQL:

SELECT id FROM articulo WHERE MATCH(concepto)AGAINST('pc usados o seminuevos') ORDER BY fecha DESC
 

el resultado me muestra esto

3,2,1

pero quiero que primero lo ordene por relevancia y los que tienen la misma relevancia me los ordene por fecha es decir quiero que me mostrara esto:

1,3,2

[gnzsoloyo]

pero quiero que primero lo ordene por relevancia y los que tienen la misma relevancia me los ordene por fecha es decir quiero que me mostrara esto:

Pues eso sólo requiere que ordenes por más de un valor... Solamente tienes que indicar que el orden sea: 1) relevancia y 2) Fecha.
¿Nunca ordenaste por mas de un campo?

Considerando sólo el ejemplo del manual de referencia:

Código MySQL:

mysql> SELECT id, MATCH (title,body)
    -> AGAINST ('Tutorial' IN NATURAL LANGUAGE MODE) AS score
    -> FROM articles;
+----+------------------+
| id | score            |
+----+------------------+
|  1 | 0.65545833110809 |
|  2 |                0 |
|  3 | 0.66266459226608 |
|  4 |                0 |
|  5 |                0 |
|  6 |                0 |
+----+------------------+
6 rows in set (0.00 sec)
 

podríamos imaginar que en tu caso sería:

Código MySQL:

SELECT id FROM articulo 
WHERE MATCH(concepto) AGAINST('pc usados o seminuevos') 
ORDER BY  MATCH(concepto) DESC, fecha DESC
 

Pruebalo y veamos.

[pithon]

Rectifico la consulta del manual esta bien la que me pusistes de mi ejemplo da un error.
saludos

[gnzsoloyo]

da un error.

¿Sabías que si dices "me da un error", pero no explicas claramente qué error despliega, y lo acompañas al menos con el código probado exacto, en realidad no nos estás diciendo nada útil?

POstea el detalle de lo que muestra MySQL, y además la query usada con exactitud, es decir la REAL. Sin abreviar nada.

[pithon]

Iniciado por gnzsoloyo

¿Sabías que si dices "me da un error", pero no explicas claramente qué error despliega, y lo acompañas al menos con el código probado exacto, en realidad no nos estás diciendo nada útil?

POstea el detalle de lo que muestra MySQL, y además la query usada con exactitud, es decir la REAL. Sin abreviar nada.

Perdon.

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DESC, fecha DESC LIMIT 0, 25' at line 3

la consulta esta igual que el ejemplo cambio los campos.

[gnzsoloyo]

la consulta esta igual que el ejemplo cambio los campos.

Aun así, postea la query, aunque te parezca redundante.

Y prueba esto:

Código MySQL:

SELECT id
FROM
   (SELECT id, MATCH(concepto) mtch
    FROM articulo 
    WHERE MATCH(concepto) AGAINST('pc usados o seminuevos') 
    ORDER BY  2 DESC, fecha DESC) tabladerivada
LIMIT 25;
 

[pithon]

Bueno al final adapte bien las dos consultas a mis necesidades quedaron asi:

Código MySQL:

SELECT concepto, MATCH (concepto)
AGAINST ('pc nuevo o seminuevo' IN NATURAL LANGUAGE MODE) AS score FROM articulo HAVING score > 0.2 ORDER BY score DESC
 

Código MySQL:

SELECT concepto, MATCH(concepto) AGAINST('pc nuevo o seminuevo') as rel
FROM articulo HAVING rel>0.2 
ORDER BY  rel DESC, fecha DESC
 

En si funciona bien pero tiene algo que no es lo que quiero, la relevancia nunca es igual entonces no tiene en cuenta la fecha y para mi consulta la fecha es casi importante como la relevancia, que podria hacer .
saludos

[gnzsoloyo]

la relevancia nunca es igual entonces no tiene en cuenta la fecha y para mi consulta la fecha es casi importante como la relevancia

Ese planteo habla de conceptos incompatibles entre si. No puedes crear una query que devuelva datos en base a conceptos que no puedan relacionarse de algún modo, no es razonable.
El camino usual es generar algun tipo de sistema de ponderación que permita jerarquizar las respuestas en un patrón único.
Si fecha y relevancia no son compatibles, entonces debes definir cual es el de mayor peso, o en qué momento prima uno sobre el otro.

En definitiva se escapa un poco del SQL. Debes definir los fundamentos del sistema de calificación.
Por ciertas experiencias, es posible que lo que necesitas termine requiriendo de stored procedures un un juego de tablas diferentes.

[primary]

Seria algo como si es bastante relevante prioriza la revelancía si no prioriza la fecha pero no tengo idea de como hacerlo.
Saludos

[gnzsoloyo]

¿Entendiste la idea de mi ultimo post?
No puedes hacer una query simple que haga eso. Es un desarrollo de lógica en un SP, por lo que te conviene primero establecer cuales son las reglas de priorización. SIn las reglas no puedes hace runa evaluación.

La idea es: ¿En base a qué consideras que la relevancia es mejor que la fecha?
Y: ¿En qué momento la fecha pasa a ser mas importante que la relevancia?

Define los criterios, y entonces podremos ver cómo implementarlo.

[pithon]

Si te entendí seria cuando la relevancia es buena digamos más de 0.5 prioriza la relevancia en caso contrario prioriza la fecha.
Seria una sql muy complicada y lenta?
Ah por cierto gracias por la ayuda.
Saludos

[gnzsoloyo]

Estás hablando de consultas condicionales, que no existen en MySQL.
Para hacer lo que quieres, te conviene usar stored procedures. Sería mas simple.

[pithon]

Hola gnzsoloyo, estoy intentando aprender hacer el procedimiento que necesito pero no entiendo muy bien que logica aplicar para mostrar el orden correspondiente, hasta donde llego puedo ejecutar las consultas que desee en el orden que desee pero no se como ordenarlas si hago estos dos procedimientos:

Código MySQL:

CREATE PROCEDURE relevancia()
BEGIN
SELECT concepto, MATCH(concepto) AGAINST('pc nuevo o seminuevo') as rel
FROM articulo HAVING rel>0.6 
ORDER BY  rel DESC;
END;
 
 
CREATE PROCEDURE relevancia()
BEGIN
SELECT concepto, MATCH(concepto) AGAINST('pc nuevo o seminuevo') as rel
FROM articulo HAVING rel>0.6 
ORDER BY  fecha DESC;
END;
 

Es decir necesitaria adaptar estos dos procedimientos en uno segun las caracteristicas del resultado si por ejemplo la relevancia es mayor que 6 ordena por relevancia en caso contrario por fecha.

que logica tendria que aplicarle?
saludos

[gnzsoloyo]

Vamos a ver un par de conceptos, para usar los SP:
1) La meta principal de un SP es contener lógica implementada en la BBDD, no consultas simples.
2) Si creas SP para implementar consultas simples, es para segurizar los accesos a los datos.
3) Si vas a usar lógica dentro de un SP, entonces tienes que programar el proceso completo. No hacer N SP separados unitarios, salvo que quieras re-usar funciones (MySQL no maneja colecciones, asi que no puedes obtener cursores desde otro SP).
4) Si sabes programar un proceso o función en un lenguaje, entonces analiza y diseña el proceso del mismo modo, solo que usando los recursos de MySQL para rutinas programadas, tales como ciclos, iteraciones, variables internas y controladores de flujo lógico (IF / THEN /ELSE/ END IF).

Dicho esto, lo primero que debes hacer, antes que nada y esto es algo que ya intenté explicarte varias veces, es definir lo que se va a evaluar.

Hagamos la suposición de que el orden obtenido por relevancia fuese este:

1.
2.
3.
4.
5.
6.
7.
8.
...

y que si lo ordeno por fecha fuese este:

3.
7.
4.
8.
5.
1.
6.
2.
...

¿Cómo compatibilizas ambos órdenes?

Supongamos que la linea de corte fuese el 5. Ene se caso quedarían así: (1; 2; 3; 4; 5), pero si sigo poniendo los restantes basandome en la fecha tendría: (1; 6; 2) donde vuelven a aparecer el 1 y el 2. Y eso no tiene sentido porque me quedan fuera el 7 y el 8, que tienen menos peso de relevancia pero mayor en la fecha.

¿Se va entendiendo el problema?

Es simplemente que dos criterios independientes pueden causar un solapamiento de datos en unos casos, y la desaparición de otros, porque se trata de criterios incompatibles entre sí.

¿Qué se hace en esos casos?
Bueno es "relativamente" simple: Se debe crear una tabla o grupo de tablas que permita establecer una PONDERACION de pesos para cada una de las dos opciones, y se use para calcular un orden que tome en cuenta ambas cosas.

ESO es lo que no estás pensando.

Ahora bien, piensalo de este modo: Si tuvieses un mismo registro con IGUALES valores y ordenes de relevancia y fecha al mismo tiempo, ¿qué porcentaje de importancia le darías a la relevancia y qué porcentaje a la fecha?
De acuerdo a la respuesta podremos hacer un SP capaz de resolver ambas cosas.

[pithon]

Ahora bien, piensalo de este modo: Si tuvieses un mismo registro con IGUALES valores y ordenes de relevancia y fecha al mismo tiempo, ¿qué porcentaje de importancia le darías a la relevancia y qué porcentaje a la fecha?
De acuerdo a la respuesta podremos hacer un SP capaz de resolver ambas cosas.

En ese caso no le daria importancia a la fecha es decir 100% relevancia 0% fecha.



La relevancia siempre seria lo mas importante, siempre y cuando la fecha no sea muy antigua de la actual por ejemplo un periodo de 1 año, en este caso quedria ordenar por fechas.

Me interesa mostrar los registros mas relevantes pero imaginate que hubieran registros de hace 10 años y otros de hace 2 años ahi quiero que se ordene por fechas.

[gnzsoloyo]

En ese caso no le daria importancia a la fecha es decir 100% relevancia 0% fecha.

Me parece que no has visto nada de matemática estadística, ¿no?
No existe la ponderación 100 - 0. La suma de ambas debe dar 1, por lo que si le asignas a una 0,51 y a la otra 0,49 es suficiente.

La relevancia siempre seria lo mas importante, siempre y cuando la fecha no sea muy antigua de la actual por ejemplo un periodo de 1 año, en este caso quedria ordenar por fechas.

Esto parece sugerir que el orden de tiempo es decreciente, por lo que a más antigua, menos relevante. Sería buena idea crear una tabla de ponderaciones por meses que se pueda usar para calcular ese peso decreciente.

Ahora bien, creo que no estás teniendo en cuenta lo que te digo: Si tienes X valores con alta relevancia, pero mucha antigüedad, entonces tienes que hacer un ajuste en el calculo de la relevancia y bajársela en base a su antigüedad.

Una solución: Crear una tabla de puntajes por antigüedad y otro por relevancia. Los multiplicas en una consulta cuando obtienes los datos y los ordenas en base a ese puntaje, junto con el ID.
Es rústico, incompleto y poco preciso, pero creo que puedes intentarlo.

Ahora estoy en horario de trabajo, por lo que no puedo postearte un ejemplo, pero tata de verlo por ti mismo.
Recuerda: Dos tablas de puntaje, una para los niveles de relevancia y otra para los de antigüedad.