Ranking de lista de usuarios

[cmarrero01]

Estimados,

Estoy necesitando realizar una consulta en MySQL que me traiga una lista de 6 usuarios y que siempre un usuario determinado este en la posicion 4, y mostrar 3 arribas y 2 abajo. Ordenado por el puntaje que tiene cada puntos...

Entonces, lo que necesito es buscar un usuario por su ID ordenado por puntos y que en vez de mostrarme un solo resultado, me muestre 3 usuarios arriba y 2 abajo.

Me explico mejor

Usuario 1 - Puntos 100
Usuario 2 - Puntos 90
Usuario 3 - Puntos 80
Usuario 4 - Puntos 70 (Yo)
Usuario 5 - PUntos 60
Usuario 6 - Puntos 50

Tienen idea de una aproximado a esto???

[gnzsoloyo]

Nosotros tenemos idea. Pero empieza por mostrar como lo harías tú.
Este no es un sito para regalar soluciones, sino para ayudar a lograrlas.
Muestra lo que intentaste.

[cmarrero01]

Gracias Gnzsoloyo por responder...

Empece por pensarlo a traves de un Store Procedure, ya que una sola consulta no me iba a ser suficiente..

Intente con esto:

Código MySQL:

BEGIN
 
    SET @query = CONCAT("SELECT b.userId as user, b.nicename,
    (select points from historypoints h where h.userId = user ORDER BY id desc limit 1) as orderpoints 
    from users as b where b.confirm_email_key = '' 
    order by orderpoints desc limit ",setLimit,",15;");
 
    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
 
END
 

Ahora, esta consulta me devuelve los 15 resultados que tienen mejor puntaje, pero lo que yo necesito, y no encuentro como resolverlo es que me muestre como planteo arriba, a quienes tengo arriba y a quienes abajo.. y yo en en el medio... y si no tengo arriba, tendria que estar primero, y los demas abajo, y viceversa...

La otra manera era crear un between con los puntos, pero como mi limite sera de 6 usuarios, me preocupa que tenga muchos usuarios..

ejemplo:

Código MySQL:

SELECT b.userId as user, b.nicename
    from bp_users as b where b.confirm_email_key = ''   and
  b.userId IN(select userId from bp_historypoints where points >= 4000)
    order by userId desc limit 0,10;
 

Saludos

[gnzsoloyo]

Este tema es parecido a uno tratado hace tiempo, y la solucion no pasa por ordenar, o armar búsquedas como lo planteas.
Si lo miras bien, lo que estás pidiendo una tabla como resultado de una consulta tal que:
1) Siempre tenga un valor dado en la cuarta posición.
2) Siempre tenga tres registros precedentes mayores al cuarto.
3) Siempre tenga dos registros posteriores, y menores al cuarto.

En principio, el planteo carece de sentido desde el momento en que en un sistema estadístico o de sumatorias, no puedes predecir que siempre habrá un conjunto de X registros antes o después, porque podría suceder que la suma de puntajes sea en uno, más de uno o todos, inferior o superior al valor dado de referencia (el cuarto).
Por ende, no puedes, a priori, predefinir que siempre habrá algo que lo preceda, o lo suceda.

Pero dejando de lado que no es racional el planteo, una consulta como la que quieres únicamente se puede construir en forma simple segmentando los rangos en diferentes consultas, y generando una respuesta única, con UNION:

Código MySQL:

SELECT * 
FROM
      (SELECT U.userId Usuario, U.nicename , H.points
      FROM users u INNER JOIN historypoints h ON U.user = H.userId = user 
      WHERE H.points > (SELECT points FROM historypoints WHERE userId = 4)
      ORDER BY H.poits DESC
      LIMIT 3) T1
UNION 
SELECT U.userId Usuario, U.nicename , H.points
FROM users u INNER JOIN historypoints h ON U.user = H.userId = user 
WHERE H.userId = 4
UNION 
SELECT *
FROM 
      (SELECT U.userId Usuario, U.nicename , H.points
      FROM users u INNER JOIN historypoints h ON U.user = H.userId = user 
      WHERE H.points < (SELECT points FROM historypoints WHERE userId = 4)
      ORDER BY H.userId
      LIMIT 2) T2;
 

Si bien esto debería funcionar, no garantiza que obtengas realmente los tres registros superiores o los dos inferiores pedidos. Sólo que el valor de este usuario ID = 4 (que uso de ejemplo) aparezca siempre, como mucho, en la posicion 4 si la consulta devuelve hasta 6 registros.
El resto, no se puede asegurar, porque dependerá de los datos existentes.

[cmarrero01]

Gracias por la respuesta,

Te aclaro el panorama para entiendas un poco mas para ver si logramos crear una query que nos funcione..

Como vos decis, no siempre vas a tener registros arriba y abajo, de hecho, eso lo tendria que contemplar, en caso de no tener arriba, el motivo seria por que estoy primero en el ranking y deberia aparecer primero, y viceversa si estoy en el ultimo..

Tome la query que me pasaste y a primeras no me funciono, por lo que le hice algunas modificaciones, nada importante, pero no funciono...

Código SQL:

SELECT *
FROM
      (SELECT U.userId, U.nicename , H.points
      FROM bp_users u INNER JOIN bp_historypoints h ON U.userId = H.userId
      WHERE H.points > (SELECT points FROM bp_historypoints WHERE userId = 21)
      ORDER BY H.points DESC
      LIMIT 3) T1
UNION
SELECT U.userId, U.nicename , H.points
FROM bp_users u INNER JOIN bp_historypoints h ON U.userId = H.userId
WHERE H.userId = 21
UNION
SELECT *
FROM
      (SELECT U.userId, U.nicename , H.points
      FROM bp_users u INNER JOIN bp_historypoints h ON U.userId = H.userId
      WHERE H.points < (SELECT points FROM bp_historypoints WHERE userId = 21)
      ORDER BY H.userId
      LIMIT 2) T2;
 

El tema que no se si mencione, es que la tabla historypoints mantiene una historia de puntos, por lo que cada usuario tiene varios registros en esta tabla y el ultimo registro de cada usuario es el que corresponde como puntos actuales.

Por lo que esta consulta me tira un error diciendome que "La Subquery tieme mas de 1 resultado)..

Voy a seguir intentando para ver q puedo lograr, pero mas que hacer prueba y error, me gustaria poder comprender exactamente la idea de lo que me propusiste que no la entendi del todo

Muchas Gracias

Best
C

[gnzsoloyo]

El tema que no se si mencione, es que la tabla historypoints mantiene una historia de puntos, por lo que cada usuario tiene varios registros en esta tabla y el ultimo registro de cada usuario es el que corresponde como puntos actuales.

Bueno, eso es crítico y debí haberlo supuesto, a causa del nombre de la tabla.
De todos modos la cosa se vuelve simple, pero ahora deberás definir cuál es el valor que deseas obtener: el máximo puntaje, el mínimo, o la media. Porque incluso si restringes la búsqueda a un período, deberás asegurarte que en ese periodo hay aun único registro por cada user, o bien aplicar las funciones que corresponda.
Supongamos buscar el máximo puntaje:

Código MySQL:

SELECT *
FROM
      (SELECT U.userId, U.nicename, MAX(H.points) points
      FROM bp_users u INNER JOIN bp_historypoints h ON U.userId = H.userId
      WHERE H.points > (SELECT points FROM bp_historypoints WHERE userId = 21)
      GROUP BY U.userId
      ORDER BY H.points DESC
      LIMIT 3) T1
UNION
SELECT *
FROM
      (SELECT U.userId, U.nicename , MAX(H.points) points
      FROM bp_users u INNER JOIN bp_historypoints h ON U.userId = H.userId
      WHERE H.userId = 21
      GROUP BY U.userId
      LIMIT 1) T2
UNION
SELECT *
FROM
      (SELECT U.userId, U.nicename, MAX(H.points) points
      FROM bp_users u INNER JOIN bp_historypoints h ON U.userId = H.userId
      WHERE H.points < (SELECT points FROM bp_historypoints WHERE userId = 21)
      GROUP BY U.userId
      ORDER BY H.userId
      LIMIT 2) T3;
 

[cmarrero01]

Bien, gracias.. Probe la query que me pasaste, y sigo con el mismo problema "Subquery return more than 1 result"...

Despues de mirar muchos tutoriales, manuales y de mas cuestiones llegue a la conclusion de modificar mis tablas..

Lo que hice fue agregar una sola tabla que mantendra un solo registro por usuario...

Esto me soluciona bastante las cosas, pero aun sigo sin lograr el resultado esperado en una varidad de pruebas, el codigo siguiente funciona a medias:

Código SQL:

BEGIN
    DECLARE countRankingTop INTEGER DEFAULT 0;
    DECLARE countRankingBottom INTEGER DEFAULT 0;
    DECLARE limitTop INTEGER DEFAULT 3;
    DECLARE limitBottom INTEGER DEFAULT 2;
 
    SELECT COUNT(userId) INTO countRankingTop FROM bp_user_points b 
WHERE points >= (SELECT points FROM bp_user_points b WHERE userId = `userId`) 
AND userId != `userId`;
 
    SELECT COUNT(userId) INTO countRankingBottom FROM bp_user_points b 
WHERE points <= (SELECT points FROM bp_user_points b WHERE userId = `userId`) 
AND userId != `userId`;
 
IF countRankingTop < 3 THEN
    SET limitBottom = 3 - countRankingTop + 3;
ELSE
    SET limitBottom = 2;
END IF;
 
IF countRankingBottom < 2 THEN
    SET limitTop = 2 - countRankingBottom + 2;
ELSE
    SET limitTop = 3;
END IF;
 
    SET @query = CONCAT("SELECT * FROM 
(SELECT * FROM bp_user_points b 
where points >= (SELECT points FROM bp_user_points b where userId = ",userId,"  limit 1) 
AND userId != ",userId," order by points desc limit ",limitTop,") T1
UNION
SELECT * FROM (SELECT * FROM bp_user_points b where userId = ",userId,") T2
UNION
SELECT * FROM 
(SELECT * FROM bp_user_points b 
where points <= (SELECT points FROM bp_user_points b where userId = ",userId,"  limit 1) 
AND userId != ",userId," order by points desc limit ",limitBottom,") T3;");
 
    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
 
END
 

El problema que estoy teniendo con esta consulta, es que por algun motivo los condicionales de arriba no funcionan como deberian o con la intension que tengo...

Esto basicamente lo que hace es contar cuantos registros tengo arriba mio y en caso que sean menor a 3 amplio la cantidad de registros que mostrare a abajo, y viceversa con los registros de abajo, de esta manera siempre mostraria 6 resultados sin importar en la posicion en la que estoy yo, si primero, ultimo o en el medio...

Como te mencionaba, parece no funcionar los condicionales, ya que por alguna razon me muestra mas resultados de los que supuestamente deberia limitar.

Gracias.

Best
C

[gnzsoloyo]

"Subquery return more than 1 result"

Eso significa simplemente que al menos una de las subconsultas está devolviendo más de un registro, y eso es tan simple como:

Código MySQL:

SELECT *
FROM
      (SELECT U.userId, U.nicename, MAX(H.points) points
      FROM bp_users u INNER JOIN bp_historypoints h ON U.userId = H.userId
      WHERE H.points > (SELECT MAX(H.points) 
                        FROM bp_users u INNER JOIN bp_historypoints h ON U.userId = H.userId
                        WHERE H.userId = 21
                        GROUP BY U.userId
                        LIMIT 1)
      GROUP BY U.userId
      ORDER BY H.points DESC
      LIMIT 3) T1
UNION
SELECT *
FROM
      (SELECT U.userId, U.nicename , MAX(H.points) points
      FROM bp_users u INNER JOIN bp_historypoints h ON U.userId = H.userId
      WHERE H.userId = 21
      GROUP BY U.userId
      LIMIT 1) T2
UNION
SELECT *
FROM
      (SELECT U.userId, U.nicename, MAX(H.points) points
      FROM bp_users u INNER JOIN bp_historypoints h ON U.userId = H.userId
      WHERE H.points < (SELECT MAX(H.points) 
                        FROM bp_users u INNER JOIN bp_historypoints h ON U.userId = H.userId
                        WHERE H.userId = 21
                        GROUP BY U.userId
                        LIMIT 1)
      GROUP BY U.userId
      ORDER BY H.userId
      LIMIT 2) T3;
 

Un consejo: Trata de no hacer códigos más complicados de lo necesario.
Si ya la query devuelve lo que necesitas, no tienes necesidad de implementarlo por medio de una sentencia preparada...
¿No te parece?

[cmarrero01]

Excelente, esta consulta casi funciona como espero... Me faltaria resolver 2 cosas en esta consulta...

1. Que me muestra una determinada cantidad de resultados arriba o abajo segun la posicion en la que caigo, por ejemplo, esta consulta, me devuelve el user 21 arriba de todo, y solo 2 resultados abajo, lo mismo pasaria con un usuario que aparezca abajo de todo, me mostraria solo 3 resultados por encima..

2. El ranking:
Esta parte, estoy pensando todavia como resolverla, necesito mostrar la posicion 1,2,3 segun sus puntos, por ejemplo el que mas puntos tiene esta en la posicion 1, y el que menos en X posicion, por ende, cuando muestro los resultados, deberia tener la posicion de cada uno, que dependeria de la tabla..


Aca hice un intento, pero veo que tenemos un problema con estas consultas, tanto la que me propones como la que yo intente hacer..

Código SQL:

BEGIN
 
    DECLARE countRankingTop INTEGER DEFAULT 0;
    DECLARE countRankingBottom INTEGER DEFAULT 0;
    DECLARE limitTop INTEGER DEFAULT 3;
    DECLARE limitBottom INTEGER DEFAULT 2;
 
    SELECT COUNT(userId) INTO countRankingTop FROM bp_user_points b 
WHERE points >= (SELECT points FROM bp_user_points b WHERE userId = `userId`) 
AND userId != `userId`;
 
    SELECT COUNT(userId) INTO countRankingBottom FROM bp_user_points b 
WHERE points <= (SELECT points FROM bp_user_points b WHERE userId = `userId`) 
AND userId != `userId`;
 
 
    SET @query = CONCAT("SELECT * FROM 
(SELECT b.userId,b.points,u.nicename as username,
(SELECT count(userId)+1 FROM bp_user_points WHERE points >= b.points and userId != b.userId) as rank 
FROM bp_user_points b 
INNER JOIN bp_users u ON u.userId = b.userId
WHERE points >= (SELECT points FROM bp_user_points b WHERE b.userId = ",userId,"  limit 1) 
AND b.userId != ",userId," order by b.points desc limit ",limitTop,") points 
UNION
SELECT * FROM 
(SELECT b.userId,b.points,u2.nicename as username,
(SELECT count(userId)+1 FROM bp_user_points WHERE points >= b.points and userId != b.userId) as rank  
FROM bp_user_points b 
INNER JOIN bp_users u2 ON u2.userId = b.userId
WHERE b.userId = ",userId,") points
UNION
SELECT * FROM 
(SELECT b.userId,b.points,u3.nicename as username,
(SELECT count(userId)+1 FROM bp_user_points WHERE points >= b.points and userId != b.userId) as rank 
 FROM bp_user_points b 
INNER JOIN bp_users u3 ON u3.userId = b.userId
WHERE b.points <= (SELECT points FROM bp_user_points b WHERE b.userId = ",userId,"  limit 1) 
AND b.userId != ",userId," order by points desc limit ",limitBottom,") points;");
 
    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
 
END
 

Resulta que los resultados de arriba de un usuario determinado estan ordenados de tal manera que me muestra los primeros resultados de la tabla, no los 2 anteriores a mi, si no los primeros de la consulta, por ende el resultado si lo pusiera en un raking es asi:

1
2
5 (YO)
6
7

Si lo ordenas al revez, queda mal tambien.

Muchas gracias por la ayuda, con respecto al store procedure que arme, visto lo de los condicionales????, tenes idea por que no funcionan?, eso me solucionara el primero puntoq ue te menciono aca..

Abrazo y gracias