Чередование выборки в MySQL31.03.2017 11:18

Итак, дано:
Две таблицы — 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. Выбрать товары, отсортировав их по пронумерованному полю

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

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

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

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

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

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

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. Для меня пока остался только один невыясненный вопрос.
Все хорошо, но что делать при пагинации?
Ведь каждая следующая страница — это новый запрос.
Соответственно, перемешанные в магазинах товары будут получать новый порядковый номер и могут попадаться в выборке не один раз.
Если есть мысли на этот счет — буду благодарен услышать их в комментах.

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

Комментарии (14)

• LeonidZ

  31 марта 2017 в 04:16

  +5

  ↑

  ↓

  Вы просто невероятный извращенец. Такое впечатление, что у вас там 2–3 магазина, и в каждом по 100 товаров. Т.к. иначе запросы у вас будут выполняться по много секунд, и это просто недопустимо для выполнения во время загрузки страницы.
  Возможно, вы это делаете раз в 10 минут и кешируете, а при загрузке просто берете подготовленные данные из кеша —, но все равно это очень-очень плохой способ.
  Я вам не предложу взамен готового решения, т.к. нет условий задачи. С моей точки зрения, перебирать все товары — уже очень плохо, перемешивать все товары — еще хуже (представьте, что их там несколько миллиардов).
  Скорее всего показывать на главной нужно самые популярные товары, а популярность у вас 100% обсчитывается по крону и пишется в отдельную табличку — и не важно на самом деле, что случайно попадет 2–3 товара из всей пачки из 1 магазина.
  Если же магазинов много (больше, чем товаров), то при обсчете популярности проще ы shops добавить колонку most_popular_product_id и обновлять ее, а затем выбирать случайные 20 магазинов с их самыми популярными продуктами.
  Если же вам нужен полный рандом среди абсолютно всех товаров, то зачем городить огород, если можно (это ужасно, но на порядок лучше того, что сделали вы) тогда select * from products order by rand () limit 20;
  И это полный рандом, с распределением среди магазинов, пропорциональным количеству товаров у них.
  

  • CrazyXoma

    31 марта 2017 в 10:20

    0

    ↑

    ↓

    Вы просто невероятный извращенец.

    
    Я буду считать это за комплимент =)

    А по сути:
    

    Такое впечатление, что у вас там 2–3 магазина, и в каждом по 100 товаров. Т.к. иначе запросы у вас будут выполняться по много секунд, и это просто недопустимо для выполнения во время загрузки страницы.

    
    Я при вел пример, что для 10 магазинов и 10000 товаров запрос выполняется 0.056с.

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

    
    Нет, нужны именно все товары в случайном порядке

    и не важно на самом деле, что случайно попадет 2–3 товара из всей пачки из 1 магазина

    
    Именно в этом и состояла задача, чтоб не попали 2–3 товара из 1-го магазина

    И это полный рандом, с распределением среди магазинов, пропорциональным количеству товаров у них.

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

    Ну и в заключении напишу, что это все таки пока теоретические изыскания =)

  • second_pilot

    31 марта 2017 в 11:08

    0

    ↑

    ↓

    Order by rand ()? Это примерно так же медленно будет

    • CrazyXoma

      31 марта 2017 в 11:10

      0

      ↑

      ↓

      примерно так-же медленно, но не вернет нужного результата

• belousovsw

  31 марта 2017 в 10:11

  0

  ↑

  ↓

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

  
  

  Получится то же эффект что и у Вас в статье, только генерировать Вы будете не на этапе выборки, а на этапе вставки. Что при выборке даст высочайшую производительность.

  • CrazyXoma

    31 марта 2017 в 10:21

    0

    ↑

    ↓

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

• danilychen

  31 марта 2017 в 10:22

  0

  ↑

  ↓

  Можно добавить в таблицу с магазинами и в таблицу с товарами по столбцу, где будут хранится рандомные числа. Соответственно заполняться они должны при создании магазина и товара. А потом сортируйте по этим полям. И пагинацию можно использовать.

  • CrazyXoma

    31 марта 2017 в 10:23

    0

    ↑

    ↓

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

    • danilychen

      31 марта 2017 в 10:26

      0

      ↑

      ↓

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

      • CrazyXoma

        31 марта 2017 в 10:28

        0

        ↑

        ↓

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

• tehSLy

  31 марта 2017 в 10:23

  0

  ↑

  ↓

  По чесноку, с первых строк задача вызвала недоумение, дочитал по диагонали, наблюдая простыни запросов, и минуты времени на их выполнение. Это опять же вызвало недоумение. Вообще у меня в это утро много чего вызывает недоумение Можете объяснить несколько подробнее преследуемые цели? Конечно, умные функции в запросах это круто, но забивать гвозди пневмопрессом — это перебор (А может тут все же и не гвозди, вот я и хочу разобраться).

  • CrazyXoma

    31 марта 2017 в 10:26

    0

    ↑

    ↓

    Ну суть проста — есть N магазинов, есть по M товаров в каждом.

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

    Чего и надо избежать.

    Т.е. несмотря на кол-во товаров в магазине (1000 или 10) их необходимо вставлять в топ 20 с одинаковой вероятностью.

• http3

  31 марта 2017 в 10:59

  0

  ↑

  ↓

  Согласен на счет популярных.

  А также можно было счетчик добавить в таблицу товаров.

  • CrazyXoma

    31 марта 2017 в 11:11

    0

    ↑

    ↓

    И на счет популярных, и на счет счетчика в таблице я ответил выше.
    1. В выборке нужны НЕ популярные товары, а случайные
    2. Счетчик в таблице делает выборку статичной, а не случайной.