25 December 2009

Хранимые процедуры и временные таблицы. MySQL для начинающих

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

Суть задачи проста: есть некий рейтинг пользователей. Необходимо показать пользователю его текущее положение, а также положение его ближайших конкурентов. Проще говоря, показать N-5..N+5 позиций в рейтинге, где N положение пользователя в рейтинге.

Таблица с рейтингами пользователей содержит четыре поля: id, points, time, uid и answered. Нас будут интересовать только два поля: points и time по которым и строится рейтинг.

Очевидно, что одним запросом здесь не обойтись, поэтому сразу создадим хранимую процедуру get_user_rating(user_id), которая будет получать рейтинг пользователя.

  1. drop procedure if exists get_user_rating //
  2. create procedure get_user_rating (in uid int unsigned)
  3. end //
* This source code was highlighted with Source Code Highlighter.


Так как нам понадобиться возвращать результат из процедуры, а именно выборку, состоящую из [6..11] строк, то для ее хранения будем использовать временную таблицу, создаваемую при вызове процедуры. К слову сказать, получить результат запроса, выполненного в теле хранимой процедуры, можно и другим способом, но мы не будем его сейчас затрагивать.

  1. drop procedure if exists get_user_rating //
  2. create procedure get_user_rating (in uid int unsigned)
  3. begin
  4. drop table if exists rating_tmp;
  5. create temporary table rating_tmp (
  6. `points` int unsigned,
  7. `time` int unsigned,
  8. `uid` int unsigned,
  9. `answered` int unsigned
  10. );
  11. end //
* This source code was highlighted with Source Code Highlighter.


Далее мы выбираем баллы пользователя:

  1. select @points := points from rating r where r.uid = uid;
* This source code was highlighted with Source Code Highlighter.


Теперь, используя полученное выше значение, выбираем близлежащие 5 строк с большим и 5 строк с меньшим значением баллов.

  1. select h.points, h.time, h.uid, h.answered
  2. from rating h
  3. where @points < h.points
  4. order by h.points desc, h.time desc
  5. limit 5
* This source code was highlighted with Source Code Highlighter.


и

  1. select b.points, b.time, b.uid, b.answered
  2. from rating b
  3. where @points > points
  4. order by b.points desc, b.time desc
  5. limit 5)
* This source code was highlighted with Source Code Highlighter.


Объединим все три результата и вставим в нашу временную таблицу. Получим результирующую хранимую процедуру:

  1. drop procedure if exists get_user_rating //
  2. create procedure get_user_rating (in uid int unsigned)
  3. begin
  4. drop table if exists rating_tmp;
  5. create temporary table rating_tmp (
  6. `points` int unsigned,
  7. `time` int unsigned,
  8. `uid` int unsigned,
  9. `answered` int unsigned
  10. );
  11. select @points := points from rating r where q.uid = uid;
  12. insert into rating_tmp (points, `time`, uid, answered)
  13. (select h.points, h.time, h.uid, h.answered
  14. from rating h
  15. where @points < h.points
  16. order by h.points desc, h.time desc
  17. limit 5)
  18. union
  19. (select points, `time`, uid, answered from rating r1 where r1.uid = uid)
  20. union
  21. (select b.points, b.time, b.uid, b.answered
  22. from rating b
  23. where @points > points
  24. order by b.points desc, b.time desc
  25. limit 5)
  26. order by points desc, `time` desc;
  27. end //
* This source code was highlighted with Source Code Highlighter.


Главное не забыть отсортировать результат объединения. Теперь в приложении получаем наш рейтинг:

  1. call get_user_rating(1);
  2. seleсt * from rating_tmp;
* This source code was highlighted with Source Code Highlighter.


Готово. Надеюсь данная информация окажется полезной.

Tags:mysql
Hubs: Lumber room
+1
2.5k 26
Comments 12