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

[WBS]

Сразу приведу простой пример. Есть множество фильмов, за которые голосуют пользователи. По результатам голосования формируются рейтинги лучших за год, месяц, неделю.

CREATE TABLE votes (
	id_film INT NOT NULL,
	dt DATE NOT NULL,
	votes INT NOT NULL,
	PRIMARY KEY (id_film, dt),
	KEY (dt),
	KEY (votes)
);

Составить рейтинг (например, за 2011 год) очень просто:

SELECT id_film, sum(votes) as votes_sum
	WHERE YEAR(dt)=2011
	GROUP BY id_film
	ORDER BY votes_sum DESC;

Интересует, каким образом наиболее эффективно (желательно с помощью одного запроса) можно достать информацию:

- об изменении позиции в рейтинге относительно предыдущего периода ("в текущем месяце относительно прошлого", "на этой неделе относительно прошлой" и т.д.);

- о лучшей позиции в рейтинге за указанный период ("лучшая позиция в недельных рейтингах" и т.д.).


В качестве примера, где реализовано нечто подобное, приведу музыкальный хит-парад. Скорее всего позиции в рейтинге тут хранятся в базе в явном виде (а не вычисляются). Поэтому отследить изменения в рейтинге (колонка "+/-") или определить лучшее место (колонка "Лучшее место") значительно проще.

 

[fixxxer]

Эффективно посчитать один раз (в месяц/неделю) и хранить в базе уже агрегированную информацию.

 

[WBS]

Немного изменю исходную таблицу.

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 (
	id_film INT NOT NULL,
	year NOT NULL,
	rank NOT NULL
	PRIMARY KEY (id_film, year),
	KEY (rank)
);

Пока я вижу два варианта, как это можно сделать:


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.

У такого варианта два недостатка:
- нужно будет выполнить много запросов, т.к. один запрос строит только один рейтинг за конкретный интервал времени (в примере это год);
- при таком построении возможна ситуация, когда объекты с одинаковыми оценками (в примере фильмы с оценками 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 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

и средствами PHP подсчитываем кол-во строк в результате (функция mysql_num_rows), прибавляем один - это и будет искомый ранг. Добавляем информацию в таблицу ratings.

Недостаток этого метода в том, что нужно выполнить очень много запросов.


Возможно ли во втором варианте (где вычисляются правильные ранги) уменьшить кол-во выполняемых запросов?

 

[WBS]

Нашел замечательное решение. Поделюсь со всеми:

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

 

[Vin-Diesel]

Нашел замечательное решение. Поделюсь со всеми:

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

а сгруппировать по году нельзя?

 

[WBS]

а сгруппировать по году нельзя?

В запросе 4 SELECT'а. Не понятно, в каком из них Вы хотите что-то группировать.
Причем в одном из них (R1) группировка в том числе по году и так есть ("GROUP BY id_film, year"), а во внешнем запросе группировать нет смысла, т.к. результат нужен для заполнения таблицы ratings. А в этой таблице (ее структуру я описал в 3-ем посте) содержится информация о разных фильмах и разных годах, например:

фильм_1	2011 позиция_в_рейтинге_3
фильм_2	2011 позиция_в_рейтинге_1
фильм_3	2011 позиция_в_рейтинге_2
фильм_4	2012 позиция_в_рейтинге_1
фильм_2	2012 позиция_в_рейтинге_3
и т.д.

В этом и заключается прелесть этого запроса: для заполнения таблицы ratings достаточно добавить в начало "INSERT INTO ratings ..."

 

[Vin-Diesel]

Я ошибся, предыдущий пост к этому фрагменту:

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.

У такого варианта два недостатка:
- нужно будет выполнить много запросов, т.к. один запрос строит только один рейтинг за конкретный интервал времени (в примере это год);
- при таком построении возможна ситуация, когда объекты с одинаковыми оценками (в примере фильмы с оценками vote_avg) получат разные позиции в рейтинге (разный ранг), что не очень верно.