Consulta para crear un ranking

[rubiomike]

Hola a todos. Estoy haciendo una web de cine en la que quiero poner un ranking de las películas mejor valoradas por los usuarios. En el código que tengo programado, el ranking se ordena de forma descendente según la nota media de cada película y el número de votos que tiene, pero esto no es correcto. Necesito que la nota media valga un 98% y el número de votos un 2%.

Por ahora la consulta que tengo es la siguiente:

Código:

SELECT  peliculas.id,
	peliculas.titulo,
	peliculas.director,
	peliculas.year,
	votos_peliculas.id_pelicula,				 				 
  	IFNULL(AVG( votos_peliculas.nota ),0) AS promedio,
	COUNT(votos_peliculas.nota) AS votos
	FROM peliculas
   	LEFT JOIN votos_peliculas
     	ON peliculas.id = votos_peliculas.id_pelicula
   	GROUP BY peliculas.id 
  	ORDER BY promedio DESC, votos DESC

La estructura de la tabla que guarda los votos es:
id, id_pelicula, usuario_emisor, nota



Pero esa consulta no me vale porque si lo dejo de la forma que lo tengo pasaría lo siguiente:

El padrino Nota: 9 Votos: 2.353
El sexto sentido Nota: 8,5 Votos: 1
Matrix Nota: 8,4 Votos: 3.123

Como podéis ver una película con un solo voto podría situarse por encima de películas que han recibido miles de votos y tienen aproximadamente la misma nota media, y no lo puedo dejar así porque un fanboy podría dar a su película favorita un 10 y aparecer en lo más alto injustamente.

¿Sabe alguien como puedo realizar la consulta para que el orden de los resultados dependan de ese porcentaje?

Un saludo y gracias a todos

[gnzsoloyo]

Como podéis ver una película con un solo voto podría situarse por encima de películas que han recibido miles de votos y tienen aproximadamente la misma nota media, y no lo puedo dejar así porque un fanboy podría dar a su película favorita un 10 y aparecer en lo más alto injustamente.

Lo que pasa es que la lógica aplicada a las notas no se corresponde con la que se usa en los votos.
Votos suma la cantidad de votos totales de una película. Eso implica que el criterio empleado es "todas las películas", mientras que el promedio que estás calculando (a causa del GROUP BY) es "el promedio de nota que le aplicaron a esta sola película cuando la votaron"...
Es decir: Ambos valores usan un criterio que no es inclusivo uno de otro.
No puedes usar ambos valores como si fuesen conceptos relacionados. No lo están, porque el universo de muestra de cada cálculo es distinto.
Lo que te está faltando es algún factor de ponderación que relacione la nota sobre el total de las peliculas votadas...
Es algo que escapa a los temas de este foro (es un problema estadístico, en realidad), y desde la optica de las consultas sólo te puedo aconsejar que inviertas el orden: Primero los votos, y luego la nota.

[rubiomike]

Muchas gracias por responder. Creo que ya he conseguido lo que quería. Tan solo he tenido que modificar las líneas que dejo en negrita.

Código:

SELECT  peliculas.id,
	peliculas.titulo,
	peliculas.director,
	peliculas.year,
	votos_peliculas.id_pelicula,				 				 
  	IFNULL(AVG( votos_peliculas.nota ),0) AS promedio,
	COUNT(votos_peliculas.nota) AS votos,
    (IFNULL(AVG( votos_peliculas.nota ),0) * 0.98) + ( COUNT( * ) * 0.02 ) AS ponderacion
	FROM peliculas
   	LEFT JOIN votos_peliculas
     	ON peliculas.id = votos_peliculas.id_pelicula
   	GROUP BY peliculas.id 
  	ORDER BY ponderacion, DESC, promedio DESC, votos DESC

Si no me equivoco, con eso el promedio será un 98% del total y el número de votos el 2% ¿no?. Y solo tengo que ordenar 'ponderacion' descendentemente .

Por ahora parece que funciona, pero acepto sugerencias.

Un saludo y gracias de nuevo