Ranking de parejas...

[X3mdesign]

Hola foro!!! Antes de nada, muchas gracias por leerme ;)

Tengo una tabla donde almaceno unas puntuaciones por parejas, pero dependiendo de cómo se introducen los usuarios para indicar las parejas así es como queda reflejado... me explido:

id | usuario1 | usuario2 | puntos
--------------------------------------------
1 | 58 | 71 | 3
2 | 4 | 35 | 4
3 | 71 | 58 | 2
4 | 58 | 71 | 3
5 | 42 | 15 | 1
6 | 71 | 58 | 2


La cuestión es que al hacer la query que me sume los puntos de las parejas repetidas me toma los ids 1, 4 iguales, pero el 3 como si fuera una pareja nueva...

¿Cómo debería formular la query para que me lo tomase como una misma pareja?

Muchas gracias!!!

[leonardo_josue]

Hola X3mdesign:

Aquí tienes un problema de modelado de tablas, lo ideal sería que tengas una tabla extra donde definas tus parejas de tal manera que en tu tabla de puntuaciones manejes un solo índice... sin embargo eso no quita el hecho de tener que validar que una combinación ya exista en la tabla... por lo pronto y como tienes tus datos, se me ocurre que reorganices tu tabla, de tal manera que el usuario1 siempre sea el id menor, y el usuario2 siempre sea el mayor (no debería darse el caso de que los id's sean los mismos, pero eso debería validarlo también). el script sería más o menos así:

Código MySQL:

mysql> CREATE TABLE parejas (id INT, usuario1 INT, usuario2 INT, puntos INT);
Query OK, 0 rows affected (0.11 sec)
 
mysql> INSERT INTO parejas VALUES
    -> (1, 58, 71, 3),(2, 4, 35, 4),(3, 71, 58, 2),
    -> (4, 58, 71, 3),(5, 42, 15, 1),(6, 71, 58, 2);
Query OK, 6 rows affected (0.05 sec)
Records: 6  Duplicates: 0  Warnings: 0
 
mysql> SELECT * FROM parejas;
+------+----------+----------+--------+
| id   | usuario1 | usuario2 | puntos |
+------+----------+----------+--------+
|    1 |       58 |       71 |      3 |
|    2 |        4 |       35 |      4 |
|    3 |       71 |       58 |      2 |
|    4 |       58 |       71 |      3 |
|    5 |       42 |       15 |      1 |
|    6 |       71 |       58 |      2 |
+------+----------+----------+--------+
6 rows in set (0.00 sec)
 
mysql> SELECT id, usuario1, usuario2, puntos
    -> FROM parejas WHERE parejas.usuario1 < parejas.usuario2
    -> UNION
    -> SELECT id, usuario2, usuario1, puntos
    -> FROM parejas WHERE parejas.usuario1 > parejas.usuario2;
+------+----------+----------+--------+
| id   | usuario1 | usuario2 | puntos |
+------+----------+----------+--------+
|    1 |       58 |       71 |      3 |
|    2 |        4 |       35 |      4 |
|    4 |       58 |       71 |      3 |
|    3 |       58 |       71 |      2 |
|    5 |       15 |       42 |      1 |
|    6 |       58 |       71 |      2 |
+------+----------+----------+--------+
6 rows in set (0.02 sec)
 
mysql> SELECT usuario1, usuario2, SUM(puntos) FROM
    -> (SELECT id, usuario1, usuario2, puntos
    -> FROM parejas WHERE parejas.usuario1 < parejas.usuario2
    -> UNION
    -> SELECT id, usuario2, usuario1, puntos
    -> FROM parejas WHERE parejas.usuario1 > parejas.usuario2
    -> ) T GROUP BY usuario1, usuario2;
+----------+----------+-------------+
| usuario1 | usuario2 | SUM(puntos) |
+----------+----------+-------------+
|        4 |       35 |           4 |
|       15 |       42 |           1 |
|       58 |       71 |          10 |
+----------+----------+-------------+
3 rows in set (0.00 sec)
 

Observa que con la consulta donde utilizo el UNION los registros 3 y 6 cambian, poniendo en primer lugar el id menor... finalmente en el último select agrupo por ambos id's para obtener el total de puntos por pareja.

Dale un vistazo para ver si es lo que necesitas.

Saludos
Leo.

[X3mdesign]

Gracias Leo por la rápida respuesta... lo que comentas a la hora de hacer la query con UNION lo podría hacer manualmente indicando siempre a la hora de hacer el INSERT del resultado colocando como usuario1 al jugador con el id más bajo... verdad??

¿¿Crees que es preferible esta otra opción para no saturar en un cálculo así al servidor mySQL??

Gracias de nuevo!!! Y sobre todo por la gran explicación, es lo más útil!!!

[leonardo_josue]

Hola de nuevo X3mdesign:

Sería ideal que siempre te aseguraras de poner el menor id como usuario1, poniendo la restricción a nivel de BD, pues efectivamente, hace una unión puede resultar bastante lento si manejas una cantidad considerable de registros.

Saludos
Leo

[X3mdesign]

Iniciado por leonardo_josue

... poniendo la restricción a nivel de BD...

A qué te refieres?? se puede indicar en la estructura una restricción así?

[leonardo_josue]

Por lo que se puede ver, no tienes mucha experiencia con el motor, por lo que te recomiendo que leas acerca de los TRIGGERS o Disparadores.

Estos se ejecutan antes o despues de insertar, actualizar o borrar los registros... por lo que te permite hacer validaciones o acciones adicionales.
Ahí podrías comparar que el usuario1 sea menor al usuario2, si no se cumple entonces intercambiarlos o marcar error, según tu lógica de negocio... sería más o menos así:

Código MySQL:

mysql> select * from parejas;
+------+----------+----------+--------+
| id   | usuario1 | usuario2 | puntos |
+------+----------+----------+--------+
|    1 |        1 |        2 |      1 |
|    2 |        1 |        2 |      1 |
+------+----------+----------+--------+
2 rows in set (0.09 sec)
 
mysql> #creamos el trigger o disparador
mysql> DELIMITER $$
mysql> CREATE TRIGGER parejas_chk BEFORE INSERT
    ->     ON parejas
    ->     FOR EACH ROW
    ->     BEGIN
    ->         DECLARE temp INT;
    ->  IF NEW.usuario1 > NEW.usuario2 THEN
    ->          SET temp = NEW.usuario1;
    ->          SET NEW.usuario1 = NEW.usuario2;
    ->          SET NEW.usuario2 = temp;
    ->  END IF;
    ->     END$$
Query OK, 0 rows affected (0.10 sec)
 
mysql> DELIMITER ;
 
mysql> #insertamos un registro con los id's correctos
mysql> insert into parejas values (3, 58, 71, 1);
Query OK, 1 row affected (0.09 sec)
 
mysql> select * from parejas;
+------+----------+----------+--------+
| id   | usuario1 | usuario2 | puntos |
+------+----------+----------+--------+
|    1 |        1 |        2 |      1 |
|    2 |        1 |        2 |      1 |
|    3 |       58 |       71 |      1 |
+------+----------+----------+--------+
3 rows in set (0.00 sec)
 
mysql> #insertamos un registro con los id's incorrectos;
mysql> insert into parejas values (4, 71, 58, 1);
Query OK, 1 row affected (0.17 sec)
 
mysql> select * from parejas;
+------+----------+----------+--------+
| id   | usuario1 | usuario2 | puntos |
+------+----------+----------+--------+
|    1 |        1 |        2 |      1 |
|    2 |        1 |        2 |      1 |
|    3 |       58 |       71 |      1 |
|    4 |       58 |       71 |      1 |
+------+----------+----------+--------+
4 rows in set (0.00 sec)
 

Observa que el registro 4, aunque se envían los id's de los usuarios al revés, el trigger los cambia, para dejar siempre el menor en primer lugar. Este tema es bastante amplio y te aseguro que te puede ser de mucha utilidad en el futuro, así es que tomate algún tiempo para investigar más y hacer algunos ejemplos (por ejemplo podrías validar que los puntos asignados estén entre un rango definido)

NOTA: el TRIGGER sólo se crea una vez, no hay necesidad de poner todo el código cada vez que ejecutas la consluta... lo hice así para demostrar el funcionamiento.

Saludos
Leo.

[X3mdesign]

qué bueno Leo... le echeré un vistazo a lo que comentas... anque parece quedesde phpMyAdmin no lo pueod ejecutar... lo tendré que hacer a través de la consola...

Tienes razón en cuanto al motor de mySQL... soy tenaz y encuentro la forma de realizar más o menos lo que quiero... pero soy consciente de que unos mySQL de una forma muy superficial... poco a poco ;)

Gracias de nuevo, Luis.