SQL для получения аналогичных результатов "соответствия" в процентах

В этой таблице хранятся голоса пользователей между совпадениями пользователей. Всегда есть один победитель, один неудачник и избиратель.

CREATE TABLE `user_versus` (
  `id_user_versus` int(11) NOT NULL AUTO_INCREMENT,
  `id_user_winner` int(10) unsigned NOT NULL,
  `id_user_loser` int(10) unsigned NOT NULL,
  `id_user` int(10) unsigned NOT NULL,
  `date_versus` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_user_versus`),
  KEY `id_user_winner` (`id_user_winner`,`id_user_loser`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=17 ;

INSERT INTO `user_versus` (`id_user_versus`, `id_user_winner`, `id_user_loser`, `id_user`, `date_versus`) VALUES
(1, 6, 7, 1, '2013-10-25 23:02:57'),
(2, 6, 8, 1, '2013-10-25 23:02:57'),
(3, 6, 9, 1, '2013-10-25 23:03:04'),
(4, 6, 10, 1, '2013-10-25 23:03:04'),
(5, 6, 11, 1, '2013-10-25 23:03:10'),
(6, 6, 12, 1, '2013-10-25 23:03:10'),
(7, 6, 13, 1, '2013-10-25 23:03:18'),
(8, 6, 14, 1, '2013-10-25 23:03:18'),
(9, 7, 6, 2, '2013-10-26 04:02:57'),
(10, 8, 6, 2, '2013-10-26 04:02:57'),
(11, 9, 8, 2, '2013-10-26 04:03:04'),
(12, 9, 10, 2, '2013-10-26 04:03:04'),
(13, 9, 11, 2, '2013-10-26 04:03:10'),
(14, 9, 12, 2, '2013-10-26 04:03:10'),
(15, 9, 13, 2, '2013-10-26 04:03:18'),
(16, 9, 14, 2, '2013-10-26 04:03:18');

Я работаю над запросом, который выбирает похожие профили. Профиль аналогичен, когда процент голосов (выигрыши против проигрышей) составляет +/- 10% от указанного профиля.

SELECT id_user_winner AS id_user,
    IFNULL(wins, 0) AS wins,
    IFNULL(loses, 0) AS loses,
    IFNULL(wins, 0) + IFNULL(loses, 0) AS total,
    IFNULL(wins, 0) / (IFNULL(wins, 0) + IFNULL(loses, 0)) AS percent
FROM
(
    SELECT id_user_winner AS id_user FROM user_versus 
    UNION
    SELECT id_user_loser FROM user_versus 
) AS u
LEFT JOIN
(
    SELECT id_user_winner, COUNT(*) AS wins
    FROM user_versus
    GROUP BY id_user_winner
) AS w
ON u.id_user = id_user_winner
LEFT JOIN
(
    SELECT id_user_loser, COUNT(*) AS loses
    FROM user_versus
    GROUP BY id_user_loser
) AS l
ON u.id_user = l.id_user_loser

Это текущий результат:

MySQL результат

В настоящее время он возвращает пустые строки, и их там быть не должно. Что еще нужно оптимизировать (и я не могу понять, как это сделать):

  1. приводить пользователей, похожих на пользователя ABC только
  2. укажите условие, которое определяет, кто является подобным пользователем, например, id пользователя = 6 (где у похожих пользователей разница в процентах с идентификатором пользователя 6 составляет +/- 10%)

Любая помощь будет оценена. Спасибо!

Источник

1 ответ

Решение

Чтобы рассчитать выигрыши и проигрыши каждого пользователя без необходимости присоединять таблицу к себе и использовать соединения OUTER, можно просто выбрать выигрыши и проигрыши отдельно и сделать между ними UNION ALL, но с дополнительной информацией, если данная строка представляет выигрыш для пользователь или потеря.

Затем легко рассчитать все выигрыши и проигрыши для каждого пользователя. Сложность заключалась в том, чтобы включить возможность указать, с каким пользователем вы хотите сравнить профили. Я сделал это с переменной, которая установлена ​​в значение percentage пользователя с данным user_id, который вы можете изменить с постоянной на переменную.

Вот мое предложение (по сравнению с пользователем с id = 6):

SELECT
    player_id AS id_user,
    wins,
    losses,
    wins + losses AS total,
    wins / (wins + losses) AS percent
  FROM (
    SELECT
        player_id,
        SUM(is_a_win) wins,
        SUM(is_a_loss) losses,
        CASE
          WHEN player_id = 6
            THEN @the_user_score := SUM(is_a_win) / (SUM(is_a_win) + SUM(is_a_loss))
          ELSE NULL
         END
      FROM (
        SELECT id_user_winner AS player_id, 1 AS is_a_win, 0 AS is_a_loss FROM user_versus
        UNION ALL SELECT id_user_loser, 0, 1 FROM user_versus
      ) games
    GROUP BY player_id
  ) data
WHERE
  ABS(wins / (wins + losses) - @the_user_score) <= 0.1
;

Выход:

ID_USER ВЫИГРЫВАЕТ ПОТЕРЮ ПОТЕРЯ
6       8       2       10      0,8
9       6       1       7       0,8571

Конечно, вы можете удалить пользователя, чей профиль является базой для сравнения, добавив player_id != 6 (или, в конечном решении, имя некоторой переменной) условие для внешней WHERE пункт.

Пример в SQLFiddle: соответствие профилей - пример

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

Источник
Другие вопросы по тегам