Построение рейтингов

Cage

Есть множество фильмов, за которые голосуют пользователи.
CREATE TABLE votes (
	id_film INT NOT NULL,
	year INT NOT NULL,
	month INT NOT NULL,
	votes_sum INT NOT NULL,
	votes_num INT NOT NULL,
	PRIMARY KEY (id_film, year, month),
	KEY (votes_num)
);
По результатам голосования формируются рейтинги лучших за год и за год/месяц.На основе этих данных хочу заранее рассчитать таблицы с рангами. Например, рейтинг за каждый год по убыванию средней оценки (votes_sum/votes_num) при условии наличия более 1000 голосов (votes_num>1000).
CREATE TABLE ratings_y (
	id_film INT NOT NULL,
	year NOT NULL,
	rank NOT NULL
	PRIMARY KEY (id_film, year),
	KEY (rank)
);
То же самое для месяцев:
CREATE TABLE ratings_ym (
	id_film INT NOT NULL,
	year NOT NULL,
	month INT NOT NULL,
	rank NOT NULL
	PRIMARY KEY (id_film, year),
	KEY (rank)
);
В качестве примера буду использовать таблицу ratings_y.Пока я вижу два варианта:1.Для каждого года (cur_year) выполняем запрос:
SELECT id_film, SUM(votes_sum)/SUM(votes_num) as vote_avg FROM votes WHERE year=cur_year
GROUP BY id_film HAVING SUM(votes_num)>1000 ORDER BY vote_avg DESC
И в скрипте, считывая построчно результат запроса, вычисляем ранг и добавляем информацию в таблицу ratings_y.У такого варианта два недостатка:- нужно будет выполнить много запросов, т.к. один запрос строит только один рейтинг за конкретный интервал времени (в примере это год);- при таком построении возможна ситуация, когда объекты с одинаковыми оценками (в примере фильмы с оценками vote_avg) получат разные позиции в рейтинге (разный ранг), что не очень верно.2.Одним запросом узнаем средние оценки для всех фильмов и лет:
SELECT id_film, year, SUM(votes_sum)/SUM(votes_num) as vote_avg FROM votes
GROUP BY id_film, year HAVING SUM(votes_num)>1000
Далее для каждого года (cur_year) и фильма (со средней оценкой cur_film_vote_avg) вычисляем ранг:
SELECT count(*)+1 as rank FROM
 (SELECT SUM(votes_sum)/SUM(votes_num) as vote_avg FROM votes WHERE year=cur_year
 GROUP BY id_film HAVING SUM(votes_num)>1000 AND vote_avg > cur_film_vote_avg) R1
Добавляем информацию в таблицу ratings_y.Недостаток этого метода в том, что нужно выполнить очень много запросов.Возможно ли во втором варианте (где вычисляются правильные ранги) уменьшить кол-во выполняемых запросов (в идеале использовать один запрос)?
1 ответ

Cage

Сам разобрался :)
SELECT id_film, year, rank FROM
 (SELECT
 IF (@prev_year=year, @n:=@n+1, @n:=1) as n,
 IF (@prev_val=vote_avg AND @n<>1, @rank, @rank:=@n) as rank,
 @prev_year:=year,
 @prev_val:=vote_avg,
 id_film, year
 FROM
 (SELECT id_film, year, SUM(votes_sum)/SUM(votes_num) as vote_avg FROM votes
 GROUP BY id_film, year HAVING SUM(votes_num)>1000 ORDER BY year, vote_avg DESC) R1,
 (SELECT @rank:=0, @n:=0, @prev_year:=NULL, @prev_val:=NULL) R2) R3