Посоветуйте трюк чтобы сделать выборку в 1 запрос

[nalim]

Посоветуйте трюк чтобы сделать выборку в 1 запрос

Есть таблица

agent_list
------------
user_id INT
user_name VARCHAR(255)
rating INT
region_id INT

Нужен вывод

user_id user_name rating region_id
3 Pasha 20 12
12 Sasha 19 12
120 Misha 5 12
32 Oleg 6 12
43 Lera 33 8
58 Inna 31 8
11 Ganna 11 8
65 Pepa 3 8
. . . .

Тоесть по 4 юзера с максимальным рейтингом из каждого региона?
Можно это сделать в 1 зарос?

 

[x-yuri]

пишешь запрос для каждого региона и склеиваешь их с помощью UNION

 

[MuXaJIbI41981]

Можно выбрать айди всех регионов а потом с помощью UNION выбрать для всех этих регионов по 4 пользователя с максимальным рейтингом (всего 2 запроса)

 

[x-yuri]

в общем я это и имел в виду

 

[nalim]

Удалось без юниона, щас напишу))))

 

[Dl]

Автор оригинала: MuXaJIbI41981
Можно выбрать айди всех регионов а потом с помощью UNION выбрать для всех этих регионов по 4 пользователя с максимальным рейтингом (всего 2 запроса)

А это как?

 

[nalim]

Вот примерный запрос,
я его не тестировал ибо данные другие в таблице на самом деле

всё круто жалко COUNT так просто не сделаешь

SELECT
al.*,
if(@rownum=4, @t_id:= tdl.region_id, '') as teh1 ,
if(@rownum=4,@rownum:=0,'') as teh2 ,
@t_id as ttid
FROM
(SELECT @rownum:=0) r,
(SELECT @t_id:=0) iii,
agent_list al
WHERE
al.region_id > @t_id
ORDER BY al.region_id ASC

у меня аналогичный работает запрос

как вам ?
каковы минусы моего решения?)

 

[x-yuri]

а у тебя есть _серьезные_ основания делать это в один запрос?

-~{}~ 12.01.09 14:25:

минус - читабельность

 

[Gas]

nalim
минус - скорость, зачем перебирать всю таблицу пользователей, если нужно выбрать 4*количество_регионов записей.

 

[nalim]

Gas
EXPLAIN вроде не показывает filesort ов
где же перебор всей таблицы?
тут же вроде как перескоки по ней)

 

[x-yuri]

я не знаю, что там EXPLAIN показывает, но не вижу для mysql способов выдать результат не проходя по всей таблице

кроме того если у тебя в регионе меньше 3х пользователей, результат будет неверный

-~{}~ 12.01.09 14:39:

и у тебя просто выбираются первые 4 пользователя, вне зависимости от рейтинга

-~{}~ 12.01.09 14:42:

и @t_id as ttid похоже лишнее
но главный недостаток читабельность имхо

 

[nalim]

x-yuri
ой, спасибо про неверность
не заметил
@t_id as ttid
это для отладки просто

-~{}~ 12.01.09 15:46:

значит нужно сбросить счетчик
if(@t_id<> tdl.region_id, @rownum:=0 , '') as teh0 ,

 

[x-yuri]

ну и по рейтингу сортировку добавить надо, тоже забыл? или я что-то не понял?

 

[nalim]

x-yuri
спасибо, тоже забыл от радости)

всем большое спасибо задачу решил

PS COUNT кстати тоже отлично делается
PPS упс не работает COUNT, собственно и не должен

 

[Gas]

Я тут поэкспериментировал с этим запросом, разные интересные вещи получаются.

Запрос, который тестил:

SELECT 
  n.id, n.time, n.topic_id,
  if(@rownum=4, @t_id:= n.topic_id, '') as teh1 ,
  if(@rownum=4,@rownum:=1,@rownum:=@rownum+1) as teh2 ,
  @t_id as ttid
FROM 
  (SELECT @rownum:=1) r,
  (SELECT @t_id:=0) iii,
  news n
WHERE n.topic_id > @t_id
ORDER BY n.topic_id ASC
LIMIT 20

он работает даже правильно, выдаёт по 4 новости (из ~2K) в 2-х имеющихся категориях.

Но:
1. если убрать LIMIT, то выводятся все записи, если оставить limit даже в 5000, всё ok;
2. сортировка только по категории не интересна, нужна сортировка внутри категории - по дате, например, если добавить в order ещё поле, то результат - все записи, делаем составной ключ (чтоб не было filesort) - всё работает (если нет мешанины asc|desc)
3. проверка как запрос работает:

FLUSH LOCAL STATUS;
_ЗАПРОС_
SHOW SESSION STATUS LIKE 'handler%';

и видно что сканируется весь индекс, что хуже аналогичного результат чем склейка нескольких запросов через union all, где выбираются только нужные данные.

 

[nalim]

Gas
у тебя нет счетчика по умолчанию
SELECT @rownum:= @rownum+1 rownum
наверное LIMIT поэтому требуется у меня без лимита все работает

 

[Gas]

как нет
SELECT @rownum:=1) r,

?

 

[nalim]

Gas
инкремента нет в селекте, только по условию
а он там нужен, забыл его написать

-~{}~ 12.01.09 18:26:

SELECT
al.*,
@rownum:= @rownum+1 rownum,
if(@t_id<> tdl.region_id, @rownum:=0 , '') as teh0 ,
if(@rownum=4, @t_id:= tdl.region_id, '') as teh1 ,
if(@rownum=4,@rownum:=0,'') as teh2 ,
@t_id as ttid
FROM
(SELECT @rownum:=0) r,
(SELECT @t_id:=0) iii,
agent_list al
WHERE
al.region_id > @t_id
ORDER BY al.region_id ASC

 

[Gas]

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

-~{}~ 12.01.09 17:32:

if(@t_id<> tdl.region_id, @rownum:=0 , '') as teh0 ,

эта проверка там же не нужна, в ней смысла нет, обнуляется счётчик в
if(@rownum=4,@rownum:=1,@rownum:=@rownum+1) as teh2,
кстати, в твоей версии я не вижу чтоб счётчик инкрементировался - [update] успел уже добавить

Мне сначала показалось что твой запрос (самый первый) действительно не будет сканировать все записи или индекс, а это уже интересно, но к сожалению это не так.

 

[nalim]

Gas
угу похоже не оч оптимальный но и беды нет большой если бегает только по индексам.

я в результате именно его использовал,
хотя отличий от UNION не много:
постаничку пришлось делать после получения всех данных(списка всех id), так COUNT и LIMIT здесь не помогают