Как высчитать позицию пользователя относительно других в каком-либо топе?

Question

[Дмитрий Тарасов]

В чем суть вопроса ?
На сайте есть топ пользователей по рефералам первого уровня.
То есть составляется топ у кого больше рефералов среди других участников проекта.
Топ участников рассчитывается до 100 пользователей, а место занимаемое в топе для любого пользователя, хоть даже если ты на 100000 месте.

У меня есть таблица

users (
    id int primary key,
    username varchar index, 
    referer varchar index, 
    rating float index,
    referals int
)

В таблице users есть поле referals которые постоянно обновлялось когда заходишь на сайт, то это рефералы первого уровня пользователя.

Топ пользователей и позиция человека в этом топе рассчитывалась вот так.
Сначала берем кол-во рефералов пользователя.
SELECT `referals` FROM `tb_users` WHERE `id` = 1
А потом высчитываем позицию.

SELECT COUNT(`tb_users`.`id`) as `row_number` FROM `tb_users`
WHERE `referals` >= 896 ORDER BY `referals` DESC, `tb_users`.`id` ASC

Получаем топ 100.

SELECT 
`tb_users`.`id`, `tb_users`.`username`, `tb_users`.`rating`, `tb_users`.`referals`
FROM `tb_users` 
ORDER BY `tb_users`.`referals` DESC, `tb_users`.`id` ASC LIMIT 0, 100

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

Также данный запрос не гарантирует твоего места в топе, потому что могут у два или 3 и более пользователя иметь одинаковое кол-во рефералов, да можно добавить проверку на соответствие id пользователя.

Я решил убрать поле `referals` из users и высчитывать его каждый раз для пользователя. Да может это не будет быстро работать потому что нету индекса.

В users около 100к записей.

Я решил сделать новый запрос и высчитать позицию

SELECT COUNT(`tb_users`.`id`) as `row_number`, count(`t1`.`id`) AS `referals`
FROM `tb_users` 
LEFT JOIN `tb_users` `t1` ON `t1`.`referer` = `tb_users`.`username`
GROUP BY `tb_users`.`id` 
HAVING `referals` >= 895 
ORDER BY `referals` DESC, `tb_users`.`id` ASC

Но не понимаю как высчитать позицию пользователя, row_number совпадает с referals.

Что я делаю не так ?

Хочу составить топ пользователей и высчитать позицию его.

Answer 1

[sepaker]

SET @i=0;
SELECT @i:=@i+1 AS `rank`, count(`t1`.`id`) AS `referals`
FROM `tb_users` 
LEFT JOIN `tb_users` `t1` ON `t1`.`referer` = `tb_users`.`username`
GROUP BY `tb_users`.`id` 
HAVING `referals` >= 895 
ORDER BY `referals` DESC, `tb_users`.`id` ASC

www.mysql.ru/docs/man/Variables.html

Comments

[Дмитрий Тарасов]

sepaker,
Результат.

[sepaker]

Не заметил у вас count, но думаю смысл не особо поменялся.

SET @i=0;
SELECT @i:=@i+1 AS `rank`, `new_t`.`referals`
FROM (
SELECT COUNT(`t1`.`id`) AS `referals`, `tbl_users`.`id` AS `id`
FROM `tb_users`
LEFT JOIN `tb_users` `t1` ON `t1`.`referer` = `tb_users`.`username`
GROUP BY `tb_users`.`id`
HAVING `referals` >= 895 
) AS new_t
ORDER BY `new_t`.`referals` DESC, `new_t`.`id` ASC

[Дмитрий Тарасов]

sepaker,
Улучшил запрос вот так.

SELECT count(*), `new_t`.`referals`
FROM (
SELECT COUNT(`t1`.`id`) AS `referals`, `tb_users`.`id` AS `id`
FROM `tb_users`
LEFT JOIN `tb_users` `t1` ON `t1`.`referer` = `tb_users`.`username`
GROUP BY `tb_users`.`id`
HAVING `referals` > 895 OR (`referals` = 895 AND `tb_users`.`id` = 1)
) AS new_t
ORDER BY `new_t`.`referals` DESC, `new_t`.`id` ASC

Мне кажется, что анализуется огромное кол-во строк можно ли это как-то оптимизировать этот запрос ?

[sepaker]

У вас сканирование всей таблицы идет, зачем вам вообще rank?

SELECT COUNT(`t1`.`id`) AS `referals`, `tbl_users`.`id` AS `id`
FROM `tb_users`
LEFT JOIN `tb_users` `t1` ON `t1`.`referer` = `tb_users`.`username`
GROUP BY `tb_users`.`id`
LIMIT 100

Далее в клиентском коде пройдитесь циклом по массиву, первая элемент массива и будет 1. Возможно еще стоит проверить индексы на полях используемых в join