sistema de rating (ON DUPLICATE UPDATE) y promedio

[mayid]

Estoy reformulando un sistema de rating que hacia demasiadas consultas a base de datos (una por cada item a valorar, mas inserts y updates varios en cada click).

La cuestion es si esto me asegura que estoy valuando y revaluando bien un item:

Código MySQL:

"INSERT INTO puntuaciones (puntuaciones, promedio, id_foto) VALUES ('1', '3.5', '3') 
 
ON DUPLICATE KEY UPDATE puntuaciones = puntuaciones+1, promedio = (promedio+3.5)/2 "
 

La consulta ON DUPLICATE KEY les parece buena? Creo que funciona ok...

La logica de un promedio es sumar los puntajes, y restarlos por el numero de puntajes. En este caso, sumo el total existente al nuevo puntaje y lo divido por dos. Esa manera de evaluar un promedio "al vuelo" esta bien matematicamente?

[Dany_s]

suma de todos los puntajes dividido la cantidad de votos

voto1 = 5
voto2 = 5
voto3 = 7


5 + 5 + 7 = 17
17/3 = 5.6666

según lo tuyo seria

promedio - cantidadDeVotos
5+0 = 5
5/1 = 5
-------
5+5 = 10
10/2 = 5
-------
5+7 = 12
12/3 = 4

no da jeje

no debe ir promedio, debe ir suma de votos
cantidad - sumaAcumulador
1 - 5
si le dan otro 5 queda
2 - 10
si le dan un 7 queda
3 - 17

entonces sumaAcumulador/cantidad
17/3 = 5.6666

acumula la suma de los puntajes y otra columna la cantidad de voto, de eso haces la división

[mayid]

Gracias por tomarte el trabajo de hacer las cuentas. Yo para las matematicas... voy flojo.

Es cierto, promedio = (promedio+nuevoValor)/2 no es un numero fiable.

Tengo almacenados todos los votos y las puntuaciones individuales. Debería entonces sacar la cuenta cada vez que almacene los nuevos resultados? Como me gustaría saber stored procedures!

Alguien mas puede aportar algun dato a esto?

[Dany_s]

espera, primero que querés hacer? si ya sé que el promedio

pasa que creí que querias usar solo un registro por cada foto jeje
entonces ahi en una columna usas de acumulador (para tener la suma total) y en otra de contador (para saber cantidad de voto)

pero si me decis que tenes los votos en una fila cada una es otra cosa jeje usa AVG

[gnzsoloyo]

La logica de un promedio es sumar los puntajes, y restarlos por el numero de puntajes. En este caso, sumo el total existente al nuevo puntaje y lo divido por dos. Esa manera de evaluar un promedio "al vuelo" esta bien matemáticamente?

En realidad, a mi entender estás encarando mal el problema porque el SQL posee funciones para realizar ese tipo de operaciones estadísticas con mucha mayor facilidad y precisión de lo que puedes hacer en la forma que has planeado.
El tema pasa por dos detalles al menos:
1) Nunca se guardan valores calculables en las bases de datos, porque el tiempo que tarda la base de datos en realizar esos cálculos es demasiado breve, y carece de sentido agregar campos simplemente para guarda algo que puedes obtener en la misma consulta.
2) Los promedios son valores dinámicos. La cantidad de ítems intervinientes afecta el valor real del promedio, por lo que un promedio sólo tiene sentido para un momento dado, o bien para un período ya cerrado.

Lo que sería bueno, para poder ayudarte, es que nos dieses una idea de qué es lo que calculas y que promedios agrupados en base a qué conceptos son los que quieres obtener.

Para darte una idea, MySQL puede devolverte en una consulta no sólo las medias aritméticas (promedios), sino que te los puede segmentar agrupadas por conceptos de tiempo, con subtotales por ítem o por períodos, o como quiera que desees agrupar la info, y para lograrlo sólo necesita los valores a sumar y no sus "promedios" parciales.

Así, pues, ¿cuáles som los datos, los conceptos de agrupamiento y cómo quieres promediarlos?

[mayid]

Mil gracias por las respuestas!

Tengo dos tablas ( no las construi yo, sino que son una traduccion al español de un sistema que ya existia, pero que en PHP y cantidad de consultas era ineficiente ).

Una de las tablas guarda cada voto (la ip del usuario, su puntuacion, el id de la fotografia). Es decir, aqui se listan todos los votos.

La otra tabla guarda promedios (un id autoincremetal, la cantidad de votos para una foto, el promedio, el id de la foto ).

Quizas estas dos tablas puedan plantearse mejor, pero a mi me vale.

Lo que necesito es que cuando un usuario hace click en las estrellitas de rating, su puntuacion se guarde en la tabla de ips, y se actualice la cantidad de votos y el promedio en la tabla de votos.

Esta es la estructura que tengo:

Código MySQL:

-- --------------------------------------------------------
 
--
-- Estructura de tabla para la tabla `ips_puntuaciones`
--
 
CREATE TABLE IF NOT EXISTS `ips_puntuaciones` (
  `ip` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
  `id_foto` varchar(250) COLLATE utf8_unicode_ci NOT NULL,
  `puntaje` decimal(2,1) NOT NULL DEFAULT '0.0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
--
-- Volcar la base de datos para la tabla `ips_puntuaciones`
--
 
INSERT INTO `ips_puntuaciones` (`ip`, `id_foto`, `puntaje`) VALUES
('127.0.0.1', '2', '4.1'),
('127.0.0.1', '1', '3.2'),
('127.0.0.1', '4', '2.6'),
('127.0.0.1', '3', '2.4'),
('127.0.0.1', '26', '4.0'),
('127.0.0.1', '31', '5.0'),
('127.0.0.1', '30', '4.0'),
('127.0.0.1', '28', '4.0'),
('127.0.0.1', '27', '4.0'),
('127.0.0.1', '29', '4.0');
 
-- --------------------------------------------------------
 
--
-- Estructura de tabla para la tabla `puntuaciones`
--
 
CREATE TABLE IF NOT EXISTS `puntuaciones` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `puntuaciones` int(10) unsigned NOT NULL DEFAULT '0',
  `promedio` decimal(65,1) NOT NULL DEFAULT '0.0',
  `id_foto` varchar(250) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_foto` (`id_foto`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=12 ;
 
--
-- Volcar la base de datos para la tabla `puntuaciones`
--
 
INSERT INTO `puntuaciones` (`id`, `puntuaciones`, `promedio`, `id_foto`) VALUES
(1, 1, '4.1', '2'),
(2, 1, '3.2', '1'),
(3, 1, '2.6', '4'),
(4, 1, '2.4', '3'),
(6, 1, '4.0', '26'),
(7, 1, '5.0', '31'),
(8, 1, '4.0', '30'),
(9, 1, '4.0', '28'),
(10, 1, '4.0', '27'),
(11, 1, '4.0', '29');
 

Gonzalo: me encantaría usar algun metodo de mysql para calcular el nuevo promedio al ingresar la nueva puntuacion... Por ahora estoy con la consulta que ves en mi primer mensaje.

[Dany_s]

yo guardo cada voto como un registro porque lo guardo con fecha de voto porque quiero que un usuario pueda votar cada x cantidad de tiempo, o controlar ip, u otra cosa

entonces para el promedio uso un select avg
tengo una columna "puntuaciones" que es la calificación y otra "id_foto"

SELECT avg(puntuaciones) promedio FROM votos WHERE id_foto = 1

[gnzsoloyo]

Código MySQL:

SELECT avg(puntuaciones) promedio FROM votos WHERE id_foto = 1
 

Exactamente a esto me estaba refiriendo.
De esta forma, Mayd, el promedio es irrelevante porque obtener el de una imagen o tema de voto es realizar esa consulta, y recuperar los de todas las fotos es algo tan simple como (siguiendo el ejemplo de Dany_s):

Código MySQL:

SELECT id_foto, AVG(puntuaciones) promedio FROM votos GROUP BY id_foto;
 

[mayid]

O sea que avg() te genera un promedio al vuelo... cuando haces un SELECT. Y no habría necesidad de guardar el promedio realmente.

Estoy haciendo esta consulta y parece que va bien:

Código MySQL:

SELECT avg(puntaje) FROM ips_puntuaciones WHERE id_foto = 1
 

Pero por que en el ejemplo usatedes ponen avg(puntaje) promedio? Ese valor "promedio" que seria? En la documentacion no aparece ninguna "argumento" fuera del parentesis:
http://dev.mysql.com/doc/refman/5.0/...l#function_avg

[Dany_s]

es un alias, asi podes recuperar como nombre de campo :)

sino te sale como nombre "avg(puntuaciones) "

[mayid]

Entiendo. Mil gracias a los dos.

Voy a eliminar la tabla de "promedios" y conservar el listado que guarda las ip solamente.

Y me las ingeniaré para tomar los promedios de varias fotos a la vez pero usando un JOIN desde la tabla fotos...

[Dany_s]

gnzsoloyo: creo que lo que quicieron hacer primero con esas 2 tablas es que solo calcule el promedio al insertar un voto, capaz usan un trigger para que al insertar en la de votos, actualice el promedio de la otra tabla.
Supongo que con eso ahorran recursos al mostrar un linstado de fotos ya que no hace el promedio por cada visita...

No sé si en realidad se ahorra algo ¿se ahorra algo con eso? o es igual de una u otra forma

[mayid]

Sigo. Lo de usar el promedio en un JOIN no funciono del todo bien. Me devuelve un solo resultado. Pero que bueno que todo pueda entonces reducirse a un par de consultas, cuando el script original usaba unas 15!

Esta es la consulta con el JOIN:

Código MySQL:

SELECT img.id, img.titulo, img.nombre_foto, img.tecnica, img.medidas , AVG(rate.puntaje) promedio 
FROM fotos AS img 
LEFT OUTER JOIN fotos_principales AS principal ON (principal.id_foto = img.id) 
LEFT JOIN puntuaciones AS rate ON (rate.id_foto = img.id)
WHERE img.id_album = '2' AND principal.id_foto IS NULL 
ORDER BY img.id DESC
 

Me queda una duda mas. Necesito dar tres diferentes respuestas:
- ya votaste
- gracias por votar
- hubo un problema. Intentalo de nuevo.

Quizas esto lo solucione con PHP, pero creo que con mysql se puede. La idea es que si existe ya la clave ip/foto, me de un error de duplicado que yo pueda procesar. Que puedo hacer? Tengo esto:

Código MySQL:

"INSERT INTO puntuaciones (ip, id_foto, puntaje) VALUES ('1', 2', '3')"
 

Dany: no, no ahorraban en nada. Despilfarraban. Si vieras el codigo! Para empezar, había una consulta ajax para buscar el promedio de cada imagen en pantalla. En mi caso, 12. Y eso se puede tener desde un primer momento con un JOIN!

[mayid]

Listo, lo primero era porque falta el GROUP BY que me dijistes.

Lo segundo... aun no se. Existe algo como ON DUPLICATE KEY responde "algo"?

[gnzsoloyo]

No sé si en realidad se ahorra algo ¿se ahorra algo con eso? o es igual de una u otra forma

En realidad no se ahorra mucho, si es que algunos milisegundos más o menos resultan trascendentes. La operación de hacer los promedios como se planteó es demasiado rápida en el servidor como para darle mucha importancia. Además, tienes más delay en el enlace de internet de lo que la base tarda en resolver consultas tan simples....

Mayd:

- ya votaste
- gracias por votar
- hubo un problema. Intentalo de nuevo.

Estos son problemas de orden procedimiental. No interviene mucho la base de datos, porque casi todo son obtenidos por el PHP.
La primera requiere una lectura o cuenta de los registros de eses usuario vinculaos con el tema o la foto, puedes hacer un

Código MySQL:

SELECT * FROM tabla WHERE foto_id = iduser LIMIT 1;
 

Si la lectura devuelve al menos un registro, ya votó.
El "gracias por votar" y "Hubo un problema" son simplemente las respuestas al usuario cuando una inserción es exitosa o no. Eso lo puedes resolver perfectamente con el PHP.
No te olvides que siembre que uan consulta es ejecutada desde un lenguaje, MySQL responde con un valor numérico que expresa la cantidad de registros afectados. Cada lenguaje tiene a su vez funciones que traducen la respuesta para poder usarla.
Aprovecha esas funciones.

[gnzsoloyo]

Iniciado por mayid

Listo, lo primero era porque falta el GROUP BY que me dijistes.

Lo segundo... aun no se. Existe algo como ON DUPLICATE KEY responde "algo"?

No.
Eso lo resuelves con las funciones de cada lenguaje conforme haya o no tenido éxito la operación.

[gnzsoloyo]

Código MySQL:

SELECT img.id, img.titulo, img.nombre_foto, img.tecnica, img.medidas , AVG(rate.puntaje) promedio 
FROM fotos AS img 
LEFT OUTER JOIN fotos_principales AS principal ON (principal.id_foto = img.id) 
LEFT JOIN puntuaciones AS rate ON (rate.id_foto = img.id)
WHERE img.id_album = '2' AND principal.id_foto IS NULL 
ORDER BY img.id DESC
 

Esto tal vez requiera una modificación de lógica

Código MySQL:

SELECT 
    img.id, 
    img.titulo, 
    img.nombre_foto, 
    img.tecnica, 
    img.medidas , 
    AVG(rate.puntaje) promedio 
FROM 
    fotos img 
    LEFT JOIN fotos_principales principal ON img.id = principal.id_foto 
    LEFT JOIN puntuaciones rate ON img.id = rate.id_foto
WHERE 
    img.id_album = '2' OR  principal.id_foto IS NULL
GROUP BY img.id DESC
ORDER BY img.id DESC;
 

Tips:
- Pon los campos del ON en el mismo orden de las tablas.
- Comienza con la tabla que tenga todas las instancias (registros).
- OUTER es irrelevante si no usas una conexión ODBC. Sólo lo toma por compatibilidad, pero el parser lo transforma en LEFT JOIN.
- Si vas a usar una función de agregación, como AVG, se requiere la cláusula GROUP BY.

[mayid]

Edito: leyendo tus comentarios todo funciona a la perfeccion! Viene bien saber sobre las convenciones, que esta de mas, y por sobre todo me sirvió la última linea, que agrupa por img.id y no por rate.id_foto que era lo que me estaba ocultando las fotos no votadas.

Mil gracias de nuevo. Todo esta resuelto y me voy sabiendo varias cosas!

[gnzsoloyo]

WHERE img.id_album = '2' AND principal.id_foto IS NULL

Ten cuidado con esta condición. Si principal.id fuese NULL, no te devolvería puntuaciones, según me parece, porque es parte de las condiciones del FROM.
Si lo pones para que te devuelva ceros en las no votadas, lo que debe ser es OR no AND.

[mayid]

De esto ultimo, te cuento que cada album de fotos tiene una foto principal. Eso es obligatorio. Y no esta relacionado con el rating.

Igual, lo voy a pensar. Saludos!

[gnzsoloyo]

El detalle es porque tienes que verificar si las dos condiciones pueden darse realmente juntas (AND), si una es excluyente, no tendrás registros.
Pera ser exacto y que se entienda mejor: Si fotos.id_album = '2' y la foto aparece en la tabla fotos_principales, entonces no aparecerá en la consulta, porque estás consultando sólo por las fotos que no figuren en esa tabla.

Si esa es la condición, no hay problema, pero de lo contrario debes cambiar el AND por el OR.

[mayid]

Veo. Lo voy a pensar en frio. Pero te confirmo que la condicion es excluir la foto principal, porque esa se toma de una tabla aparte.

En fin, aqui esta el resultado de lo que venimos hablando: la galeria.

Saludos!