@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 — дата регистрации (из

Имеем:

  • Вопрос задан
  • 2944 просмотра
Пригласить эксперта
Ответы на вопрос 2
@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),
и еще куча полей

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

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы