Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / MySQL Новый топик    Ответить
 Получить последнее Имя пользователя  [new]
Tonny96
Member

Откуда:
Сообщений: 120
Здравствуйте.

SELECT @i := @i + 1 AS num, steamid, name, cmd_time FROM ( SELECT steamid, MAX(name) name, SUM(cmd_time) cmd_time FROM 227_27015 GROUP BY steamid ORDER BY 3 DESC LIMIT 0, 15 ) x, ( SELECT @i:=0) z ORDER BY 4 DESC

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

К сообщению приложен файл. Размер - 148Kb
26 дек 19, 07:55    [22049092]     Ответить | Цитировать Сообщить модератору
 Re: Получить последнее Имя пользователя  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20016
Версия MySQL?
26 дек 19, 11:01    [22049174]     Ответить | Цитировать Сообщить модератору
 Re: Получить последнее Имя пользователя  [new]
Tonny96
Member

Откуда:
Сообщений: 120
Версия сервера: 10.1.43-MariaDB-1~xenial - mariadb.org binary distribution
Спс что отозвались)

Сообщение было отредактировано: 26 дек 19, 11:05
26 дек 19, 11:04    [22049179]     Ответить | Цитировать Сообщить модератору
 Re: Получить последнее Имя пользователя  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20016
Хреново... оконные функции недоступны.

Тады вроде вот так:

SELECT @i := @i + 1 AS num, 
       steamid, 
       name, 
       cmd_time 
FROM ( SELECT t0.steamid, 
              MAX(t3.name) name, 
              SUM(t0.cmd_time) cmd_time 
       FROM 227_27015 t0
       JOIN ( SELECT t1.steamid, t1.name
              FROM 227_27015 t1
              NATURAL JOIN ( SELECT steamid, MAX(`end`) `end`
                             FROM 227_27015
                             GROUP BY steamid ) t2
            ) t3 USING (steamid)
       GROUP BY t0.steamid
       ORDER BY 3 DESC LIMIT 0, 15 ) x, 
     ( SELECT @i:=0 ) z 
ORDER BY 4 DESC
26 дек 19, 11:19    [22049194]     Ответить | Цитировать Сообщить модератору
 Re: Получить последнее Имя пользователя  [new]
Tonny96
Member

Откуда:
Сообщений: 120
Нажал отправить запрос и зависло вот так...(

К сообщению приложен файл. Размер - 103Kb
26 дек 19, 11:28    [22049210]     Ответить | Цитировать Сообщить модератору
 Re: Получить последнее Имя пользователя  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20016
Ты хоть логику-то проверил? начиная от самого внутреннего запроса... выполни сперва
SELECT steamid, MAX(`end`) `end`
FROM 227_27015
GROUP BY steamid

Убедился, что не бред? тогда выполни
SELECT t1.steamid, t1.name
FROM 227_27015 t1
NATURAL JOIN ( SELECT steamid, MAX(`end`) `end`
               FROM 227_27015
               GROUP BY steamid ) t2

И опять смотри...

И так далее.
26 дек 19, 12:38    [22049264]     Ответить | Цитировать Сообщить модератору
 Re: Получить последнее Имя пользователя  [new]
Tonny96
Member

Откуда:
Сообщений: 120
Понятно что ничего не понятно.. запрос сильно сложный. Как не крути не работает
26 дек 19, 18:09    [22049563]     Ответить | Цитировать Сообщить модератору
 Re: Получить последнее Имя пользователя  [new]
Tonny96
Member

Откуда:
Сообщений: 120
Если бы я знал как составить я бы не спрашивал ответа на форуме.Но спасибо что хоть кто-то ответил обычно на этом форуме все воды в рот набрали. Если соизвелите помочь готовым запросом и ответить на пару логический вопросов по нему то будет здорово.

Сообщение было отредактировано: 26 дек 19, 18:37
26 дек 19, 18:31    [22049581]     Ответить | Цитировать Сообщить модератору
 Re: Получить последнее Имя пользователя  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20016
Тогда выгружайте (или воссоздавайте) структуру, делайте образец данных (10-20 записей), и выкладывайте всё это вместе с требуемым результатом для этих данных. Будем крутить...
26 дек 19, 19:00    [22049612]     Ответить | Цитировать Сообщить модератору
 Re: Получить последнее Имя пользователя  [new]
Tonny96
Member

Откуда:
Сообщений: 120


К сообщению приложен файл. Размер - 25Kb
26 дек 19, 19:11    [22049619]     Ответить | Цитировать Сообщить модератору
 Re: Получить последнее Имя пользователя  [new]
Tonny96
Member

Откуда:
Сообщений: 120


К сообщению приложен файл (68_27018.sql - 3Kb) cкачать
26 дек 19, 19:12    [22049620]     Ответить | Цитировать Сообщить модератору
 Re: Получить последнее Имя пользователя  [new]
Tonny96
Member

Откуда:
Сообщений: 120
Прикрепил таблицу и требуемый результат.
26 дек 19, 19:12    [22049621]     Ответить | Цитировать Сообщить модератору
 Re: Получить последнее Имя пользователя  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20016
Отлично.

Копирую всё это в online fiddle (именно так нужно в будущем предоставлять материалы для тестирования). Добавляю свой запрос (и предварительные отладочные шаги для отслеживания логики).

Всё работает как часики, и даёт на первый взгляд верный результат (и уж точно тут виснуть не на чем...).

Сообщение было отредактировано: 26 дек 19, 20:08
26 дек 19, 20:07    [22049663]     Ответить | Цитировать Сообщить модератору
 Re: Получить последнее Имя пользователя  [new]
Tonny96
Member

Откуда:
Сообщений: 120
Спасибо, на маленькой таблице действительно работает, но вот на уже рабочей реальной таблице при отправки запроса через phpmyadmin по центру экрана "Загрузка..." и все повисло. ( https://dropmefiles.com/YC4wZ - тут выкинул таблицу)

В базе ~25000 строк (зафиксированных сессий).
26 дек 19, 20:23    [22049666]     Ответить | Цитировать Сообщить модератору
 Re: Получить последнее Имя пользователя  [new]
Tonny96
Member

Откуда:
Сообщений: 120
Проверил на чуть меньше таблице, ответа ждал без преувеличения секунд 30))

По всей видимости структура базы отвратная для таких задач.. порекомендуйте как устроить базу чтоб работало все как часики.. или запрос можно как-то оптимизировать?
26 дек 19, 20:27    [22049667]     Ответить | Цитировать Сообщить модератору
 Re: Получить последнее Имя пользователя  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20016
Опробовал на полном дампе дома (MySQL 8.0.18). Запросы из fiddle (записей - 25225 всего, 3538 уников) выполнились за 0,07; 0,16; 7,86 и 8,23 секунды. Система Windows 10 Pro x64, проц Е7500, 2,93 Ггц, ОЗУ 4 Гб, все влияющие настройки сервера дефолтные.

Так что с запросом проблем НЕТ. Проблемы или в сервере (MySQL, PHP или web), или в клиенте.

Ну и индексов для этого запроса можно добавить - но это рюшечки.

Сообщение было отредактировано: 26 дек 19, 21:38
26 дек 19, 21:34    [22049690]     Ответить | Цитировать Сообщить модератору
 Re: Получить последнее Имя пользователя  [new]
Tonny96
Member

Откуда:
Сообщений: 120
Спасибо вам больше за помощь, думаю стоит оптимизировать сбор данных.
26 дек 19, 23:38    [22049768]     Ответить | Цитировать Сообщить модератору
 Re: Получить последнее Имя пользователя  [new]
Karbafos
Member

Откуда:
Сообщений: 464
Вариант, который отрабатывает за 0.12с без всяких индексов на дохлой виртуалке c 1CPU 2.4ГГц, 1Гб RAM

select @rownum:=@rownum+1 as num, fin.* from (
    select rr.steamid, rr.name, sr.cmd_time  from (
        SELECT
            @dense_rank:=if(@prev_hid=s.steamid, @dense_rank + 1, 1) as dense_rank,
            @prev_hid:=s.steamid as steamid2,
            s.*
        FROM 227_27015 s,
        (SELECT @dense_rank:=0, @prev_hid:=0) var
        HAVING dense_rank=1
        ORDER BY s.steamid, s.`end` desc
    ) rr
    inner join (
        select z.steamid, sum(z.cmd_time) as cmd_time
        from 227_27015 z
        group by z.steamid
    ) sr ON rr.steamid=sr.steamid
) fin,
(select @rownum:=0) qq
order by fin.cmd_time desc
limit 25
27 дек 19, 02:23    [22049824]     Ответить | Цитировать Сообщить модератору
 Re: Получить последнее Имя пользователя  [new]
Karbafos
Member

Откуда:
Сообщений: 464
0,03с на той же виртуалке при наличии доп. индекса по steamid

select
    p.*,
    @rownum:=@rownum+1 as num
from (
    select
        rr.newid,
        rr.steamid,
        rr.name,
        sr.cmd_time,
        rr.end,
        sr.maxnewid
    from (
        select z.steamid, sum(z.cmd_time) as cmd_time, max(z.newid) as maxnewid
        from 227_27015 z
        group by z.steamid
        order by cmd_time desc, z.steamid 
        limit 25
    ) sr
    inner join 227_27015 rr ON sr.steamid=rr.steamid
    where rr.newid=sr.maxnewid
) p,
(SELECT @rownum:=0 ) var
order by p.cmd_time desc, p.steamid
27 дек 19, 13:45    [22050095]     Ответить | Цитировать Сообщить модератору
 Re: Получить последнее Имя пользователя  [new]
Tonny96
Member

Откуда:
Сообщений: 120
Karbafos,

ого ваш предпоследний вариант у меня занял Запрос занял 0.3071 сек, спасибо большое.. я в mysql к сожалению не силен, но как вы думаете стоит ли мне менять структуру таблицы или текущая структура считается вполне приемлемой для получения данных которые мне нужно? Может дадите какие-то свои опытные рекомендации как бы вы ее изменили? И еще давайте попоробуем разобраться чему 1 вариант запроса у меня выполняется 131.9464 сек.?
1 янв 20, 10:02    [22052521]     Ответить | Цитировать Сообщить модератору
 Re: Получить последнее Имя пользователя  [new]
Tonny96
Member

Откуда:
Сообщений: 120
Последний вариант разместил в свой код, оч круто, спасибо!
1 янв 20, 10:46    [22052530]     Ответить | Цитировать Сообщить модератору
 Re: Получить последнее Имя пользователя  [new]
Tonny96
Member

Откуда:
Сообщений: 120
Еще 2 вопроса интересуют:
1 - как получить все последние имена пользователей и их steamid
2 - как получить последние имена пользователей и их steamid из нескольких баз данных например из 227_2017, 331_27015, 123_27015 .. тут нужно учесть время последнего подключения игрока по полю end на любой из этих среверов.. (227_2017, 331_27015, 123_27015 ) ну и steamid не должны повторяться, берем только самые последние из баз данных.

Сообщение было отредактировано: 1 янв 20, 13:03
1 янв 20, 13:02    [22052549]     Ответить | Цитировать Сообщить модератору
 Re: Получить последнее Имя пользователя  [new]
Karbafos
Member

Откуда:
Сообщений: 464
Tonny96
Последний вариант разместил в свой код, оч круто, спасибо!


Рано, ибо это не последний возможный вариант.
Наиболее оптимальный, если двигаться в эту сторону это
select
	@rownum:=@rownum+1 as num,
    p.*
from (
    select
        rr.newid, rr.steamid, rr.name, sr.cmd_time, rr.end
    from (
        select
        	z.steamid, sum(z.cmd_time) as cmd_time, max(z.newid) as maxnewid
        from 227_27015 z
        group by z.steamid
        order by cmd_time desc, z.steamid 
        limit 0, 25
    ) sr
    inner join 227_27015 rr ON sr.maxnewid=rr.newid
) p,
(SELECT @rownum:=0 ) var
order by p.cmd_time desc, p.steamid


при этом у тебя в таблице должно быть всего два индекса: pk на newid (он есть внезапно) и второй (steamid, cmd_time)
3 янв 20, 03:41    [22053008]     Ответить | Цитировать Сообщить модератору
 Re: Получить последнее Имя пользователя  [new]
Karbafos
Member

Откуда:
Сообщений: 464
Tonny96
Karbafos,

ого ваш предпоследний вариант у меня занял Запрос занял 0.3071 сек, спасибо большое.. я в mysql к сожалению не силен, но как вы думаете стоит ли мне менять структуру таблицы или текущая структура считается вполне приемлемой для получения данных которые мне нужно? Может дадите какие-то свои опытные рекомендации как бы вы ее изменили? И еще давайте попоробуем разобраться чему 1 вариант запроса у меня выполняется 131.9464 сек.?


Нормальная структура, там есть всё необходимое за исключением пары индексов.
Мне не нравится использование переменной в запросе для нумерации, лучше бы это делать в коде.
Кто проектировал базу, включая схему шардирования? Все вопросы лучше задавать этому человеку, ибо, не зная полную архитектуру, можно наломать дров изменениями.

Первый вариант делает слишком много self join и не использует pk.
Чтобы понимать что к чему крайне рекомендую освоить конструкцию EXPLAIN и пройтись по всем вариантам, которые есть в данной теме.
3 янв 20, 03:59    [22053009]     Ответить | Цитировать Сообщить модератору
 Re: Получить последнее Имя пользователя  [new]
Karbafos
Member

Откуда:
Сообщений: 464
Tonny96
Еще 2 вопроса интересуют:
1 - как получить все последние имена пользователей и их steamid
2 - как получить последние имена пользователей и их steamid из нескольких баз данных например из 227_2017, 331_27015, 123_27015 .. тут нужно учесть время последнего подключения игрока по полю end на любой из этих среверов.. (227_2017, 331_27015, 123_27015 ) ну и steamid не должны повторяться, берем только самые последние из баз данных.


Тут уж сам пробуй, может union all поможет.
3 янв 20, 04:03    [22053010]     Ответить | Цитировать Сообщить модератору
Все форумы / MySQL Ответить