Составить запрос для получения таблицы рейтинга

Question

[Ilusha]

Здравствуйте, попрошу еще помощи по созданию запроса.
О задаче: в базе храним историю шахматных игр между пользователем и компьютером.
Вот так выглядит исходная таблица:

CREATE TABLE IF NOT EXISTS `chess_historyui` (
  `userId` int(11) NOT NULL, /*id пользователя*/
  `numberSave` int(11) NOT NULL AUTO_INCREMENT, /*номер сохранения*/
  `historyArray` longtext NOT NULL, /*рудимент, уже не используется*/
  `timeUser` int(10) NOT NULL, /*время пользователя*/
  `timeUI` int(10) NOT NULL,  /*время компьютера*/
  `numberMove` int(11) NOT NULL,  /*номер хода*/
  `autoSave` tinyint(1) NOT NULL,  /*флаг автосохранения*/
  `timeSave` datetime NOT NULL,  /*время последнего сохранения*/
  `resultGame` int(11) NOT NULL,  /*результат игры*/
  `timeStart` datetime NOT NULL,  /*время начала игры*/
  `difficulty` tinyint(1) NOT NULL,  /*сложность*/
  `isWhite` tinyint(1) NOT NULL,  /*флаг цвета фигур пользователя*/
  PRIMARY KEY (`numberSave`),
  KEY `userId` (`userId`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=72360 ;

К ней относятся таблицы:
user_activity (в ней храним записи об активности пользователей каждую сессию):
к нашей задаче относятся только записи с gameVariationId=3

CREATE TABLE IF NOT EXISTS `user_activity` (
  `userId` int(11) NOT NULL,
  `sessionId` varchar(255) NOT NULL,
  `gameVariationId` int(11) NOT NULL,
  `lastBeacon` int(11) NOT NULL,
  `lastActivity` int(11) NOT NULL,
  PRIMARY KEY (`userId`,`gameVariationId`,`sessionId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

В таблице kosynka_users храним собственно пользователей.
Из нее нам нужны только поля:
username — имя пользователя
isGuest — флаг гостя (если = 1, то это незарегистрированный пользователь)

Необходимо построить запрос, который будет выдавать таблицу со следующими полями:
userId — id пользователя,
winGame — количество выигранных игр (resultGame = 3),
drawGame — количество игр в ничью (resultGame = 2 OR =4 OR =5 OR =6),
loseGame — количество проигранных игр (resultGame = 2 OR =4 OR =5 OR =6),
startGame — количество незаконченных игры (resultGame = 0),
allGame — количество всех игр (resultGame NOT IS NULL),
online — текущий статус пользователя,
onlineActive — активность пользователя,
countNewWinGame — количество новых выигранных игр(новая — сыгранная в последние 24 часа),
countNewDrawGame — количество новых игр в ничью,
countNewAllGame — количество всех новых игр,
rate — позиция в рейтинге, в котором присутствуют все пользователи,
rate1 — позиция в рейтинге, в котором присутствуют только зарегистрированные пользователи,
rate2 — позиция в рейтинге, в котором присутствуют только гости,
username — имя,
isGuest — флаг гостя,
sumTimeGame — суммарное время решения,
appearanceTS — дата регистрации (из

Имеем:

Comments

[Ilusha]

SELECT
                tables1.userId as userId,
                tables1.winGame as winGame,
                tables1.drawGame as drawGame,
                tables1.loseGame as loseGame,
                tables1.startGame as startGame,
                tables1.allGame as allGame,
                tables1.online as online,
                tables1.onlineActive as onlineActive,
                tables1.countNewWinGame as countNewWinGame,
                tables1.countNewDrawGame as countNewDrawGame,
                tables1.countNewAllGame as countNewAllGame,
                tables1.rate as rate,
                tables1.rate1 as rate1,
                tables1.rate2 as rate2,
                tables1.username as username,
                tables1.isGuest as isGuest,
                tables1.sumTimeGame as sumTimeGame,
                t2.appearanceTS as dateRegister
            FROM
                (
                    SELECT
                        tables.userId as userId,
                        tables.winGame as winGame,
                        tables.drawGame as drawGame,
                        tables.loseGame as loseGame,
                        tables.startGame as startGame,
                        tables.allGame as allGame,
                        tables.online as online,
                        tables.onlineActive as onlineActive,
                        tables.countNewWinGame as countNewWinGame,
                        tables.countNewDrawGame as countNewDrawGame,
                        tables.countNewAllGame as countNewAllGame,
                        tables.sumTimeGame as sumTimeGame,
                        t1.username as username,
                        t1.isGuest as isGuest,
                        IF(t1.isGuest=1,@c:=@c+1,0) as rate1,
                        IF(t1.isGuest=0,@d:=@d+1,0) as rate2,
                        @b:=@b+1 as rate
                    FROM
                    (
                        SELECT
                            t0.userId as userId,
                            t0.winGame as winGame,
                            t0.drawGame as drawGame,
                            t0.loseGame as loseGame,
                            t0.startGame as startGame,
                            t0.allGame as allGame,
                            t0.countNewWinGame as countNewWinGame,
                            t0.countNewDrawGame as countNewDrawGame,
                            t0.countNewAllGame as countNewAllGame,
                            t0.sumTimeGame as sumTimeGame,
                            IF(UNIX_TIMESTAMP()-MAX(v3.lastBeacon)<=120,1,0) as online,
                            IF(UNIX_TIMESTAMP()-MAX(v3.lastActivity)<=300,1,0) as onlineActive
                        FROM
                        (
                            (
                                SELECT
                                temp.userId as userId,
                                SUM(CASE WHEN temp.resultGame IS NOT NULL THEN 1 ELSE 0 END) as allGame,
                                SUM(CASE WHEN temp.resultGame = 3  THEN 1 ELSE 0 END) as winGame,
                                SUM(CASE
                                 WHEN (temp.resultGame = 2 OR
                                     temp.resultGame = 4 OR
                                     temp.resultGame = 5 OR
                                     temp.resultGame = 6)
                                 THEN 1 ELSE 0 END) as drawGame,
                                SUM(CASE WHEN temp.resultGame = 1  THEN 1 ELSE 0 END) as loseGame,
                                SUM(CASE WHEN temp.resultGame = 0  THEN 1 ELSE 0 END) as startGame,
                                SUM(CASE WHEN temp.resultGame=3 AND
                                UNIX_TIMESTAMP(temp.timeStart)>=UNIX_TIMESTAMP(CURDATE())
                                THEN 1 ELSE 0 END) as countNewWinGame,
                                SUM(CASE WHEN (temp.resultGame = 2 OR temp.resultGame = 4 OR
                                temp.resultGame = 5 OR temp.resultGame = 6) AND
                                UNIX_TIMESTAMP(temp.timeStart)>=UNIX_TIMESTAMP(CURDATE())
                                THEN 1 ELSE 0 END) as countNewDrawGame,
                                SUM(CASE WHEN UNIX_TIMESTAMP(temp.timeStart)>=UNIX_TIMESTAMP(CURDATE())
                                AND temp.numberMove>3
                                THEN 1 ELSE 0 END) as countNewAllGame,
                                SUM(temp.timeUser+temp.timeUI) as sumTimeGame
                                FROM `chess_historyui` temp
                                WHERE  ".$where."
                                GROUP BY temp.userId

                            ) as t0,
                            (SELECT @b:=0) AS `RowNumberTable`
                        )
                        LEFT JOIN
                          `user_activity` v3
                        ON (v3.gameVariationId=3 AND v3.userId=t0.userId)
                        GROUP BY t0.userId
                        ORDER BY t0.winGame DESC,t0.drawGame DESC, t0.allGame DESC, t0.userId DESC
                    ) as tables
                    INNER JOIN
                        `kosynka_users` t1
                    ON (tables.userId=t1.userId AND tables.allGame>1),
                    (SELECT @c:=0) AS `RowNumberTable1`,
                    (SELECT @d:=0) AS `RowNumberTable2`
                ) tables1
                LEFT JOIN
                  `user_games` t2
                ON (t2.userId=tables1.userId AND t2.gameVariationId=3)
                WHERE (".$where2.")".$order;

где

$where — условие выборки по времени из php
$where2 — условие выборки по статусу активности пользователя (онлайн, не онлайн) и по статусу гость/зарегистрированный
Например:
$where2 = 'tables1.isGuest=1';
$where = '1=1';
$where=$where.' AND YEAR(temp.timeStart)=2013';

а, $order — условие сортировки, например:
$order = 'ORDER BY ';
$order = $order.'tables1.winGame '.$vs.', tables1.drawGame '.$vs.', tables1.allGame '.$vs.', tables1.userId '.$vs.'';
где $vs = 'ASC' или 'DESC'

[Ilusha]

Этот запрос отрабатывается относительно не долго (1.5) секунды только если уже, как я понял, закешировался mysql.
В противном случае он работает секунд 20…

Очень буду рад если не решению, то помощи в выборе пути решения.

[hail3b]

Какая версия MySql? Добавьте дамп данных таблиц хотя бы по 50 строк.

Answer 1

[edogs]

Хрестоматийный пример того, когда наворачивать НЕ нужно.

winGame — количество выигранных игр (resultGame = 3),
drawGame — количество игр в ничью (resultGame = 2 OR =4 OR =5 OR =6),
loseGame — количество проигранных игр (resultGame = 2 OR =4 OR =5 OR =6),
startGame — количество незаконченных игры (resultGame = 0),
allGame — количество всех игр (resultGame NOT IS NULL),
и еще куча полей

Это все обновляется только при завершении/начале игры, добавляйте эти поля в основную таблицу и апдейтите ее во время завершения/начала игры. Да и последнее время активности пользователя и другие еще можно. Не надо выбирать их динамически.
В таком случае и такие вещи как «позиция в рейтинге» будут выбираться тоже простым запросом, а не этим хитро*ным (простите, другого слово тут не подобрать).

Comments

[Ilusha]

Согласен, запрос написан через ж*пу. Поэтому я и прошу помощи.
Поместить эти данные в отдельную таблицу, на первый взгляд, хорошее решение.
Однако, когда требуется выборка с условием времени: например, рейтинг за промежуток времени, например за год или месяц, то такая таблица теряет свой смысл. Здесь уже динамика.

[hail3b]

Если вы хотите оптимизировать запрос, то по мимо структуры нужен еще дамп данных.
Если вы хотите совет по структуре БД, то для подсчета рейтинга однозначно нужно создать отдельную таблицу, которую по определенному событию её нужно дополнять/обновлять. Если рейтинг необходимо выбирать за определенный период, то в этой таблице можно собирать агрегированные данные для быстрого расчета рейтинга.

[Ilusha]

Как я понял, Вы имеете ввиду, что нужно хранить информацию по каждому из периодов, по каждому пользователю.
Этого я и боялся…
Дамп участвующих в запросе таблиц отправил в ЛС.

[edogs]

Ваше положение может несколько облегчить разбиение этого запроса на несколько более мелких.
При чем достаточно серьезно может облегчить, особенно учитывая то, что если мы правильно разобрались в запросе, данные у Вас выбираются не сильно отфильтрованные.
Выберите нужных юзеров — возможно даже во «временную» таблицу (или просто в пхп), а потом выбирайте все данные по ним отдельными точечными запросами.

Answer 2

[hail3b]

У вас рейтинг игроков формируется сортировкой

winGame, drawGame, allGame

кажется это самый простой и правильный метод, но это не так.
Не простой потому, как сортировка это очень ресурсоёмкая задача.
А не правильный потому, как из двух игроков:
23 usr, 1 winGame, 0 drawGame, 50 drawGame
24 usr, 0 winGame, 10 drawGame, 10 drawGame
предпочтения отдается первому 23 usr, хотя второй явно играет лучше.

Мне кажется, что лучше сделать подсчет рейтинга/очков/баллов, т.е. если у вас три параметра определяющие рейтинг winGame, drawGame, allGame, то им просто можно назначить коэффициенты значимости. К примеру winGame*3, drawGame*2, allGame*1, а потом уже отсортировать по баллам.

select *, (t2.winGame*3 + t2.drawGame*2 + t2.allGame*1) rating
from(
SELECT
temp.userId as userId,
SUM(CASE WHEN temp.resultGame IS NOT NULL THEN 1 ELSE 0 END) as allGame,
SUM(CASE WHEN temp.resultGame = 3  THEN 1 ELSE 0 END) as winGame,
SUM(CASE WHEN temp.resultGame in(2,4,5,6) THEN 1 ELSE 0 END) as drawGame,
SUM(CASE WHEN temp.resultGame = 1  THEN 1 ELSE 0 END) as loseGame,
SUM(CASE WHEN temp.resultGame = 0  THEN 1 ELSE 0 END) as startGame,
SUM(CASE WHEN temp.resultGame=3 AND UNIX_TIMESTAMP(temp.timeStart)>=UNIX_TIMESTAMP(CURDATE()) THEN 1 ELSE 0 END) as countNewWinGame,
SUM(CASE WHEN temp.resultGame in(2,4,5,6) AND UNIX_TIMESTAMP(temp.timeStart)>=UNIX_TIMESTAMP(CURDATE()) THEN 1 ELSE 0 END) as countNewDrawGame,
SUM(CASE WHEN UNIX_TIMESTAMP(temp.timeStart)>=UNIX_TIMESTAMP(CURDATE()) AND temp.numberMove>3 THEN 1 ELSE 0 END) as countNewAllGame,
SUM(temp.timeUser+temp.timeUI) as sumTimeGame

FROM `chess_historyui` temp
GROUP BY temp.userId
) t2
ORDER BY rating DESC

Comments

[Ilusha]

Спасибо. Да, я понимаю, что сортировка здесь узкое место.
Если бы это я продумывал схему выставления рейтинга…
Но сейчас нужно именно так сортировать,
ORDER BY t0.winGame DESC,t0.drawGame DESC, t0.allGame DESC, t0.userId DESC.
Исключить можно разве только userId, но тогда одинаковые позиции будут скакать вверх-вниз.

Я, кстати, думал, что указание второй и далее сортировки выполняется в том случае, когда в первой появляются одинаковые значения.
Я ошибаюсь?

А есть идеи, как можно перекроить запрос, чтобы не было такой большой вложенности Select'ов?

[AgentSIB]

Составьте несколько маленких. Имхо, несколько простых запросов лучше чем один сложный.

[hail3b]

Я, кстати, думал, что указание второй и далее сортировки выполняется в том случае, когда в первой появляются одинаковые значения.
Я ошибаюсь?

Да все верно. Именно это я и показал на примере. Если «первый» выиграл 1 раз, а «второй» сыграл вничью 10 раз и ни разу не выиграл, то по DESC сортировке сначала будет «первый».
Если нужна именно такая сортировка то ваш запрос более менее оптимальный, не идеальный конечно, есть одинаковые участки сравнения пр:«temp.resultGame in(2,4,5,6)», от которых можно избавиться. От вложенности не избавиться, это нормально.
Кстати у меня ваш запрос выполняется за 0,5 сек без where, с сортировкой. Если у вас база больше, то однозначно помогут индексы.

[Ilusha]

У меня этот запрос выполняется 1.5 секунды с where, если, как я понял, он закеширован mysql.
Первое выполнение (скажем за сутки) заставляет его работать 15-20 секунд, что неприемлемо…

[hail3b]

По оптимизации запроса, то если вынести вложенные запросы выполнение происходит на порядки быстрее [0.0199 сек.]

select 
	temp.userId as userId,
	SUM(CASE WHEN temp.resultGame IS NOT NULL THEN 1 ELSE 0 END) as allGame,
	SUM(CASE WHEN temp.resultGame = 3  THEN 1 ELSE 0 END) as winGame,
	SUM(CASE WHEN temp.resultGame in(2,4,5,6) THEN 1 ELSE 0 END) as drawGame,
	SUM(CASE WHEN temp.resultGame = 1 THEN 1 ELSE 0 END) as loseGame,
	SUM(CASE WHEN temp.resultGame = 0 THEN 1 ELSE 0 END) as startGame,
	SUM(CASE WHEN temp.resultGame=3 AND UNIX_TIMESTAMP(temp.timeStart)>=UNIX_TIMESTAMP(CURDATE()) THEN 1 ELSE 0 END) as countNewWinGame,
	SUM(CASE WHEN temp.resultGame in(2,4,5,6) AND UNIX_TIMESTAMP(temp.timeStart)>=UNIX_TIMESTAMP(CURDATE()) THEN 1 ELSE 0 END) as countNewDrawGame,
	SUM(CASE WHEN UNIX_TIMESTAMP(temp.timeStart)>=UNIX_TIMESTAMP(CURDATE()) AND temp.numberMove>3 THEN 1 ELSE 0 END) as countNewAllGame,
	SUM(temp.timeUser+temp.timeUI) as sumTimeGame,
	IF(UNIX_TIMESTAMP()-MAX(v3.lastBeacon)<=120,1,0) as online,
	IF(UNIX_TIMESTAMP()-MAX(v3.lastActivity)<=300,1,0) as onlineActive,
    IF(t1.isGuest=1,@c:=@c+1,0) as rate1,
    IF(t1.isGuest=0,@d:=@d+1,0) as rate2,
    @b:=@b+1 as rate
from chess_historyui temp        
    LEFT JOIN `user_activity` v3 ON v3.userId=temp.userId and v3.gameVariationId=3
    INNER JOIN `kosynka_users` t1 ON t1.userId=temp.userId
    LEFT JOIN `user_games` t2 ON (t2.userId=temp.userId AND t2.gameVariationId=3)
    JOIN (SELECT @b:=0, @c:=0, @d:=0) t_var ON 1=1
GROUP BY temp.userId

[Ilusha]

Да, но тогда ведь rate1, rate2 и rate не будут соответствовать позициям в рейтинге. Они будут присвоены случайным пользователям.

Идея в том, что вычисляются позиции, а потом уже сортируются строки по нужным параметрам.

[hail3b]

Да согласен, тогда вот так:

select *,
    IF(t1.isGuest=1,@c:=@c+1,0) as rate1,
    IF(t1.isGuest=0,@d:=@d+1,0) as rate2,
    @b:=@b+1 as rate
FROM (
select 
    temp.userId as userId,
    SUM(CASE WHEN temp.resultGame IS NOT NULL THEN 1 ELSE 0 END) as allGame,
    SUM(CASE WHEN temp.resultGame = 3  THEN 1 ELSE 0 END) as winGame,
    SUM(CASE WHEN temp.resultGame in(2,4,5,6) THEN 1 ELSE 0 END) as drawGame,
    SUM(CASE WHEN temp.resultGame = 1 THEN 1 ELSE 0 END) as loseGame,
    SUM(CASE WHEN temp.resultGame = 0 THEN 1 ELSE 0 END) as startGame,
    SUM(CASE WHEN temp.resultGame=3 AND UNIX_TIMESTAMP(temp.timeStart)>=UNIX_TIMESTAMP(CURDATE()) THEN 1 ELSE 0 END) as countNewWinGame,
    SUM(CASE WHEN temp.resultGame in(2,4,5,6) AND UNIX_TIMESTAMP(temp.timeStart)>=UNIX_TIMESTAMP(CURDATE()) THEN 1 ELSE 0 END) as countNewDrawGame,
    SUM(CASE WHEN UNIX_TIMESTAMP(temp.timeStart)>=UNIX_TIMESTAMP(CURDATE()) AND temp.numberMove>3 THEN 1 ELSE 0 END) as countNewAllGame,
    SUM(temp.timeUser+temp.timeUI) as sumTimeGame,
    MAX(t1.isGuest) isGuest,
    IF(UNIX_TIMESTAMP()-MAX(v3.lastBeacon)<=120,1,0) as online,
    IF(UNIX_TIMESTAMP()-MAX(v3.lastActivity)<=300,1,0) as onlineActive
from chess_historyui temp        
    LEFT JOIN `user_activity` v3 ON v3.userId=temp.userId and v3.gameVariationId=3
    INNER JOIN `kosynka_users` t1 ON t1.userId=temp.userId
    LEFT JOIN `user_games` t2 ON (t2.userId=temp.userId AND t2.gameVariationId=3)
GROUP BY temp.userId
ORDER BY winGame DESC,drawGame DESC, allGame DESC, userId DESC
) t1
    JOIN (SELECT @b:=0, @c:=0, @d:=0) t_var ON 1=1

[Ilusha]

Да, нужно попробовать. Сейчас разберусь с завалом по другим делам и попробую запилить этот запрос. Спасибо большое)