Чередование выборки в MySQL

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

Итак, дано:
Две таблицы — shops и products

Грубо говоря — площадка, где разные магазины размещают свои товары.
И вот, встала необходимость сделать на главной странице выдачу товаров, но так, чтоб пользователь не видел кучу товаров одного магазина. Магазины надо чередовать.

shops:

• id int not null auto_increment primary key,
• name varchar(255) null

products:

• id int not null auto_increment primary key,
• shop_id int not null,
• name varchar(255) null,

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

Изначально опишу алгоритм:

1. Необходимо выбрать все товары и отсортировать их по магазину.
2. Далее пронумеровать каждый товар, начиная с 1 с интервалом, равным количеству магазинов.
3. При нумерации, как только заканчиваются товары одного магазина — нумерация сбрасывается на ноль, сдвигается на единицу, и начинается снова
4. Выбрать товары, отсортировав их по пронумерованному полю

И все это средствами MySQL. И желательно одним запросом.

Сложив в голове план — можно приступить к реализации.
Что нам понадобиться?

1. @i — Счетчик, который будет нумеровать наши товары
2. @cnt — Количество магазинов
3. @delta — дельта, на которую сдвигается счетчик при нумерации товаров следующего магазина
4. @cur — id текущего магазина, для добавления дельты и сброса счетчика при нумерации нового магазина

Объявим наши переменные:

set @cnt = 0;
set @i = 0;
set @delta = 0;
set @cur = 0;

Далее присвоим начальные значения (кол-во магазинов и id первого магазина)

select @cur:=id from shops order by id limit 1;
select @cnt:=count(id) from shops;

Теперь можно приступить к самой выборке. Что нам необходимо?

• Необходимо нумеровать товары с интервалом, равным кол-ву магазинов.
• При окончании товаров одного магазина — сбрасывать счетчик, добавлять дельту и менять текущий магазин.

У меня получился такой запрос:

select id, shop_id, @i:=@i+@cnt as counter,
                     IF(@cur<>shop_id,@delta:=@delta+1,@delta) as delta,
                     IF(@cur<>shop_id,@i:=@delta,@i) as cur,
                     IF(@cur<>shop_id,@cur:=shop_id,@cur) as curshop
  from t_product order by shop_id

Подробнее о том, что здесь есть что:

@i:=@i+@cnt

В каждой строке мы увеличиваем наш счетчик на число, равное кол-ву магазинов.
Т.е., если у нас 5 магазинов, то у нас получится следующая нумерация: 0, 5, 10, 15 и т.д.

IF(@cur<>shop_id,@delta:=@delta+1,@delta) as delta

Как только у нас появляется новый магазин — мы увеличиваем сдвиг на единицу. Т.е. для первого магазина сдвиг будет равен 0, для второго — 1 и т.д.

IF(@cur<>shop_id,@i:=@delta,@i) as cur,

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

IF(@cur<>shop_id,@cur:=shop_id,@cur) as curshop

И в конце концов — обновить текущий магазин, товары которого мы нумеруем…

В результате мы получим выборку типа:

Тут мы видим, что счетчик прибавляется корректно, при смене магазина он сбрасывается, добавляется сдвиг и нумерация начинается с начала (с учетом сдвига).

Собственно, дело осталось за малым. Обернуть полученную выборку в подзапрос и отсортировать по нашему счетчику:

select id as product_id, shop_id, cur from (
    select id, shop_id, @i:=@i+@cnt as counter,
        IF(@cur<>shop_id,@start:=@delta+1,@delta) as delta,
        IF(@cur<>shop_id,@i:=@delta,@i) as cur,
        IF(@cur<>shop_id,@cur:=shop_id,@cur) as curmag
        from  products order by shop_id
    ) as A order by cur ;

Вуаля! У нас получилась выборка товаров с чередующимися магазинами:

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

Дабы избавиться от этой закономерности нам необходимо перемешать товары в первоначальной выборке, обернув ее в еще один подзапрос:

select id as product_id, shop_id, cur from (
    select id, shop_id, @i:=@i+@cnt as counter,
        IF(@cur<>shop_id,@start:=@delta+1,@delta) as delta,
        IF(@cur<>shop_id,@i:=@delta,@i) as cur,
        IF(@cur<>shop_id,@cur:=shop_id,@cur) as curmag
        from  products order by shop_id
    from (select id, shop_id from products order by shop_id, rand()) as A order by shop_id) as B order by cur;

Так наши товары уже будут перемешаны еще до их нумерации.

Собственно на этом решение задачи и закончилось.
Полный запрос можно посмотреть под катом.

set @cnt = 0;
set @i = 0;
set @start = 0;
set @cur = 0;

select @cur:=id from shops order by id limit 1;
select @cnt:=count(id) from shops;

select id as product_id, shop_id, cur from (
    select id, shop_id, @i:=@i+@cnt as counter,
        IF(@cur<>shop_id,@start:=@delta+1,@delta) as delta,
        IF(@cur<>shop_id,@i:=@delta,@i) as cur,
        IF(@cur<>shop_id,@cur:=shop_id,@cur) as curmag
        from  products order by shop_id
    from (select id, shop_id from products order by shop_id, rand()) as A order by shop_id) as B order by cur;

Ради интереса — посмотрел скорость выборки. Результаты на мой взгляд получились неплохие:
10 магазинов, 10000 товаров — ~16ms (0.016s)
100 магазинов, 1000000 товаров — ~2568ms (2.568s)
100 магазинов, 10000000 товаров — 129951ms (2m 9.951s)

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

P.S. Для меня пока остался только один невыясненный вопрос.
Все хорошо, но что делать при пагинации?
Ведь каждая следующая страница — это новый запрос.
Соответственно, перемешанные в магазинах товары будут получать новый порядковый номер и могут попадаться в выборке не один раз.
Если есть мысли на этот счет — буду благодарен услышать их в комментах.

Всем спасибо за внимание =)

Автор: Олег

Источник