Как правильно сформировать запрос к mysql?

204

23 марта 2017, 19:48

Есть mysql таблица
id | username | number | created_at
1 | valera | 50 | 2017-03-19
...
99 | valera | 30 | 2017-03-18
...
321 | katya | 40 | 2017-03-19
...
444 | katya | 10 | 2017-03-17
...
999999 | alesha | 3 | 2017-01-12

Нужно получить N-ную запись с конца, всех пользователей.
Пока нашел две реализации, но они не совсем подходят.
1. вариант
Можно получить запись к примеру по дню CURRENT_DATE() - INTERVAL 1 DAY, но нюанс, если пользователь не оставил запись в предыдущий день, то мы не получим данных, а надо если в предпоследний день нет записи, брать еще за более ранний день пока не получим пред последнюю запись пользователя. В день юзер может оставить только 1 запись, либо не оставить вовсе.

SELECT * FROM content WHERE and created_at=CURRENT_DATE() - INTERVAL 1 DAY

вариант
- Собираю все username за CURRENT_DATE, далее собираю всех у кого больше 2 записей, чтобы быть уверенымы что пред последняя запись существует. Далее убираю все username не оставлявшие запись сегодня с помощью array_intersect();

И в цикле получаем уже данные за нужный день
$MY_LIMIT = "LIMIT 1, 1";//1 - Номер строки(0-последняя, 1-предпоследняя, 2- третья с конца и т.д.)
$query .= "(SELECT * FROM content where username='".$uniqum_username."' order by id DESC ".$MY_LIMIT.") union ";
$query = substr($query, 0, -7);

Да, этот метод работает мы получаем нужную запись с конца со всех пользователей базы данных. Но он очень, очень ресурсозатратный. По базе даже в 10к пользователей выполняет выборку 200 секунд что не подходит.

Вот пример данных что нужно получить с учетом того что сегодня 2017-03-19 число данные за предыдущий день. пред последняя запись valera 2017-03-18, а предпоследняя запись katya 2017-03-17.
Есть еще идеи реализации, более шустрые и элегантные?

99 | valera | 30 | 2017-03-18 //Вторая с конца запись юзера Валера
444 | katya | 10 | 2017-03-17 //Вторая с конца запись юзера Катя
...

Заранее благодарен, мозг кипит.

Answer 1

Попробуйте что нибудь в этом роде:

select C.*
  from content C
  join (
   select (select id from content B
            where B.username=A.username 
            order by username,created_at desc
            limit 1,1
          ) id
     from content A
    group by username
  ) X on C.id=X.id

Рекомендуется создать составной индекс из полей (username, created_at). После чего ориентироваться на план выполнения.

Если есть отдельная таблица, содержащая список существующих пользователей (где они уникальны), рекомендуется в подзапросе заменить content A на нее и убрать group by.

Answer 2

 SELECT t1.username, t2.id FROM
 (select max(n-1) as cnt, username from 
    (select id, if(@usr=username,@i:=@i+1,@i:=0) as n, 
        @usr:=username as prevname, username 
    from content order by username, dt) as z group by username
  ) as t1
  LEFT JOIN 
  (select id, if(@usr=username,@i:=@i+1,@i:=0) as n, 
    @usr:=username as prevname, username from content order by username, dt) as t2
 ON t1.username = t2.username and t1.cnt = t2.n;

Скорость незнаю какая будет, отпишитесь как попробуете, номер записи указываете вот здесь max(n-1) -1 это предпоследняя.