Последних N товаров из категорий одним запросом

[vier]

12 минут назад, chukcha сказал:

т.е. вы предлагаете выбрать "все" товары?

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

сорри - у меня нет времени на такие дискуссии.

спасибо за понимание.

[chukcha]

21 час назад, vier сказал:

 

SELECT p.* ,pd.* FROM `oc_product` p 
	LEFT JOIN `oc_product_description` pd ON (p.`product_id` = pd.`product_id`)
	LEFT JOIN `oc_product_to_category` p2c ON (p.`product_id` = p2c.`product_id`) 
	WHERE p2c.`category_id` IN (20,26,27) AND p.`status` = 1 AND (p.`date_available` <= NOW()) AND (p.`date_added` > DATE_SUB(NOW(),INTERVAL 60 DAY)) GROUP BY p.`product_id`

- изменить только на свой префикс в таблицах (oc_)

- p2c.`category_id` IN (20,26,27) - id категорий (если все нужны - их можно получить доп.зпросом)

- INTERVAL 60 DAY - количество дней, при котором будут считаться товары последне-добавленные.

@vier вас здесь немного

1

Цитата

если все нужны - их можно получить доп.зпросом

ДА? А зачем?

2.

 

21 час назад, vier сказал:

AND (p.`date_added` > DATE_SUB(NOW(),INTERVAL 60 DAY)

Что это если не ограничение?

27 минут назад, vier сказал:

у меня нет времени на такие дискуссии.

 

Это ваше право, не читать, то что вам пишут

 

[Wild]

 

1 час назад, chukcha сказал:

т.е. вы предлагаете выбрать "все" товары?
а потом разобрать результат?

Это и испугало еще больше чем 150 рапросов к категории.
Товаров около 17000.
Даже не хочу тестировать на время работы.

 

1 час назад, chukcha сказал:

А если есть категории где товары не добавлялись год?

Именно так и есть!
 

1 час назад, chukcha сказал:

поэту приведенный ТС запрос, и модифицированый мною - как раз и выберет по N товаров из каждой категории по дате ( в его случае по product_id)

Именно так!
Благодаря Вам поставленная задача решена.
Есть нюансы, особенно для начинающего, но опубликованный запрос хорош как базовый для дальнейшего улучшения.
Работает вполне сносно и достаточно быстро на таком объеме информации.

 

57 минут назад, vier сказал:

сорри - у меня нет времени на такие дискуссии.

спасибо за понимание.

В любом случае Вам тоже спасибо что откликнулись.
В споре рождается истина! ))

[Wild]

Обратил внимание на одну не состыковку.
Вычисляемое поле NumRow почему-то "скачет" через оно значение.
Т.е. если в запросе указываешь к примеру только 7 значений из каждой категории (HAVING RowNum <= 7), то в таблицу попадают только нечетные номера (1, 3, 5, 7).
Причем если указать RumRow четным, то значений будет на одно больше (при RumRow <= 8 будет 1, 3, 5, 7, 9)

Т.е. в результате в 2 раза меньше значений.
По этому сейчас приходится NumRow указывать по формуле (N * 2) -1.
Т.е. если нужно 5, то указываю 9, для 10 указываю 19.

 

Можете объяснить почему такое происходит?

[Wild]

Т.е. получается вот так:

HAWING RowNum <= 7
 

Спойлер

 

product_id	category_id	main_category	RowNum	@C:=B.category_id
9976	403	1	1	403
9984	403	1	3	403
9977	403	1	5	403
9985	403	1	7	403
1251	400	1	1	400
1255	400	1	3	400
3275	400	1	5	400
1247	400	1	7	400

 

 

HAWING RowNum <= 8

 

Спойлер

 

product_id	category_id	main_category	RowNum	@C:=B.category_id
9974	403	1	1	403
9985	403	1	3	403
9980	403	1	5	403
9975	403	1	7	403
9981	403	1	9	403
1247	400	1	1	400
1255	400	1	3	400
1263	400	1	5	400
3274	400	1	7	400
1266	400	1	9	400

 

 

[chukcha]

Я, например, ничего сказать не могу, нужно смотреть на реальных данных

и  вашем примере даже product_id пляшут

[Wild]

5 минут назад, chukcha сказал:

Я, например, ничего сказать не могу, нужно смотреть на реальных данных

На ваших данных есть и четные и нечетные?
 

4 минуты назад, chukcha сказал:

и  вашем примере даже product_id пляшут

Знаю, там были разные условия отбора.
Не обращайте внимания.

[Wild]

Мой последний вариант запроса, может кому пригодится:
 

Спойлер

SET @I=0;
SET @C='';
SELECT * FROM (
	SELECT B.*,
	IF(@C != B.category_id, @I:=1, @I:=@I+1) AS RowNum,
	@C:=B.category_id
	FROM (
		SELECT p.product_id, p.date_added, p2c.category_id, p2c.main_category, p2s.store_id, pd.language_id
		FROM oc_product p
		LEFT JOIN oc_product_description pd ON pd.product_id = p.product_id
		LEFT JOIN oc_product_to_category p2c ON p2c.product_id = p.product_id
		LEFT JOIN oc_product_to_store p2s ON p2s.product_id = p.product_id
		WHERE p2c.main_category = '1'
			AND p2s.store_id = '0'
			AND p.status = '1'
			AND p.quantity >= 0
			AND pd.language_id = '2'
			AND datediff(DATE(NOW()), DATE(p.date_added)) <= 90 # количество последних дней
			AND p.date_available <= DATE(NOW())
		GROUP BY p2c.category_id, p.product_id ORDER BY p2c.category_id, p.date_added DESC
	) AS B HAVING RowNum <= 7 # количество значений из каждой категории
) AS A

 

Использование NOW() отменяет кэширование запроса, по этому в PHP коде он заменен конструкцию вида date("Y-m-d H:i") . ":00"

 

 

[Wild]

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

[chukcha]

В 27.11.2018 в 11:58, Wild сказал:

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

попробуйте

 

AS B HAVING RowNum <= 7

 

 

Having RowNum <10
ORDER BY category_id, RowNum

 

[SooR]

Если нужно брать товары небольшими партиями, то можно обойтись и простым GROUP_CONCAT + PHP разбор

SELECT 

SUBSTRING_INDEX(GROUP_CONCAT(p.product_id ORDER BY p.product_id DESC SEPARATOR ','), ',', 15) AS products, /* 15 = лимит на категорию */
p2c.category_id

FROM product p
LEFT JOIN product_to_category p2c ON (p.product_id = p2c.product_id)
WHERE DATE(p.date_added) > "' . $this->db->escape(date('Y-m-d', strtotime('-60 day'))) . '" /* Чтобы кешировался */
GROUP BY p2c.category_id
ORDER BY p.date_added DESC

затем перебираем php циклом получившийся результат и выбираем уже отдельно товары по их product_id

 

$product_data = array();

foreach ($query->rows as $result) {
  $product_data[$result['category_id']] = array(
    'category' => ... ,
    'products' => array(),
  );

  foreach (explode(',', $result['products']) as $product_id) {
    $product_data[$result['category_id']]['products'][] = $this->model_catalog_product->getProduct($product_id);
  }
}

 

Дополнительно можно:

 

- итоговые массивы кешировать в общий системный кеш

- дополнить запрос другими фильтрами по товарам либо категориям

- дополнить выдачу другими данными из товаров, добавив CONCAT(p.product_id, ':', p.date_added .... ) и так далее.

 

P.S. На шареде 90к товаров и около 300 категорий запрос выполняется за 0.78 - 0.125s

[100napb]

On 11/25/2018 at 8:49 PM, Wild said:

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

В идеале хочется получить последние N товаров из каждой категории.

о! Интересная задачка. А давайте и я поучаствую ради спортивного интереса

 

• сделаем две одинаковые выборки из таблиц product и product_to_category. Назовем их "a" и "b".
• объединим обе таблицы, но с условием пересечения ТОЛЬКО по category_id и сравнением даты изменения (таким образом зададим нужную нам сортировку)
• обрежем результаты джоина условием having count до двух для каждой категории
• эээм...PROFIT! если, конечно, я не поторопился

Spoiler

SELECT
  a.*
FROM (SELECT
         optc.category_id,
         optc.product_id,
         op.date_added
       FROM oc_product_to_category optc,
            oc_product op
       WHERE op.product_id = optc.product_id
       GROUP BY optc.category_id,
                op.product_id) a,
     (SELECT
         optc.category_id,
         optc.product_id,
         op.date_added
       FROM oc_product_to_category optc,
            oc_product op
       WHERE op.product_id = optc.product_id
       GROUP BY optc.category_id,
                op.product_id) b
WHERE a.category_id = b.category_id
AND a.date_added <= b.date_added
GROUP BY a.category_id,
         a.product_id
HAVING COUNT(*) <= 2
ORDER BY 1, 3 DESC;

 

 

 

как вариант, задачу можно было бы относительно красиво решить курсорами (те же циклы). НО! для этого потребовалось бы писать процедуру для самого mysql. Но это уже другая история ¯\_(ツ)_/¯

[SooR]

@100napb , вроде бы как и работает, но лочит всю базу, запрос на i5 7600k выполнился за 36,735 sec.

[chukcha]

А если  убрать group by

 

[Wild]

В 30.11.2018 в 01:14, chukcha сказал:

попробуйте

AS B HAVING RowNum <= 7

 

Having RowNum <10
ORDER BY category_id, RowNum

 

 

Спасибо, помогло!

[Wild]

В 30.11.2018 в 01:49, SooR сказал:

Если нужно брать товары небольшими партиями, то можно обойтись и простым GROUP_CONCAT + PHP разбор

...

затем перебираем php циклом получившийся результат и выбираем уже отдельно товары по их product_id

...

Дополнительно можно:

 

- итоговые массивы кешировать в общий системный кеш

- дополнить запрос другими фильтрами по товарам либо категориям

- дополнить выдачу другими данными из товаров, добавив CONCAT(p.product_id, ':', p.date_added .... ) и так далее.

 

P.S. На шареде 90к товаров и около 300 категорий запрос выполняется за 0.78 - 0.125s

Интересное и весьма шустрое решение, спасибо!

[Wild]

11 часов назад, SooR сказал:

@100napb , вроде бы как и работает, но лочит всю базу, запрос на i5 7600k выполнился за 36,735 sec.

Да, скорость работы удручает.
 

11 часов назад, chukcha сказал:

А если  убрать group by

Там их 3, какой конкретно?
 

[chukcha]

в подзапросах

[Wild]

41 минуту назад, chukcha сказал:

в подзапросах

 

Попробовал убрать вложенные группировки, оставив последнюю.
Ситуация та же, быстрее не стало.
 

[Wild]

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

 

P.S. Ограничение на main_category ускоряет запрос раза в 4 (+/-), но всеравно медленно.

[100napb]

9 hours ago, Wild said:

Еще странноватый результат выборки.
Не попадают в нее некоторые категории 3-го уровня.

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

Спасибо за интересную задачку

 

Боюсь, что пытаться все решить одним единственным запросом - это не оптимальное решение. Так как оно проиграет по скорости и гибкости решению смеси циклов php и простых запросов к БД.

 

Как достаточно быстрый вариант (потому что селекты простые и выборки точные), можно написать хранимую процедурку в mysql, в которой бы с помощью банального курсора для каждой категории\подкатегории находились бы последние n-товаров. Результат бы записывался в отдельную табличку в базе (а вот тут может быть медленно, т.к. инсерты работают ощутимо медленнее селектов; ну да инсертов будет не много - столько же, сколько категорий). А уж потом, однострочным селектом можно было бы запрашивать результат.

 

Ну и что бы не быть голословным.

Вот 100% рабочий пример процедуры, которую надо создать в базе.

Spoiler

CREATE DEFINER = 'root'@'%'
PROCEDURE НАЗВАНИЕ_ВАШЕЙ_БАЗЫ.prod_counter()
BEGIN
  Declare cursor_category_id integer;
  Declare cursor_sub_category_id integer;
  Declare done_1 integer default 0;
  Declare done_2 integer default 0;
/*Объявление курсора*/
Declare Cursor_1 Cursor for SELECT DISTINCT optc.category_id FROM oc_product_to_category optc;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done_1 = 1;

/* временная табличка для результатов */
CREATE TEMPORARY TABLE IF NOT EXISTS tmp_prod_2_cat AS (
  SELECT
       optc.category_id,
       optc.product_id,
       op.date_added
     FROM oc_product_to_category optc,
          oc_product op
     WHERE op.product_id = optc.product_id LIMIT 1);
TRUNCATE tmp_prod_2_cat;

/* открытие курсора */
Open Cursor_1;
REPEAT
  FETCH Cursor_1 INTO cursor_category_id;
  IF NOT done_1 THEN 
    INSERT INTO tmp_prod_2_cat  
    SELECT
         optc.category_id,
         optc.product_id,
         op.date_added
       FROM oc_product_to_category optc,
            oc_product op
       WHERE op.product_id = optc.product_id AND optc.category_id = cursor_category_id ORDER BY 3 DESC LIMIT 2;
  END IF;
UNTIL done_1 END REPEAT;
/*закрытие курсора */
Close Cursor_1;

END

ну а тут нам остается только вызвать процедуру и заселектить результат.

CALL prod_counter;
SELECT * FROM tmp_prod_2_cat ORDER BY 1, 3, 2 DESC;

Уважаемые @Wild и @SooR

Возьметесь проверить? У меня... гм... стыдно сказать... база маленькая ))) все слишком быстро считается.

 

Ясное дело, что предлагаемое решение не в своем конечном варианте и требует доработки. В тех же подкатегориях не считаются товары (исправимо, просто поленился). Ну и может быть кто придумает, как можно избежать множественных инсертов в результирующую табличку

 

Змінено 4 грудня 2018 користувачем 100napb

[SooR]

1 час назад, 100napb сказал:

Возьметесь проверить?

Пока не проверял, по теории: задача не стоит свеч. Процедуры требуют свои привилегии, а от временных таблиц я недавно отказался, т.к. их конфигурация в разных окружениях, движках и платформах сильно отличается (в основном по выделяемому объему данных и их типу).

Лучше создать одну постоянную буферную табличку с префиксом temp_* и использовать под свои нужды.

 

Ну и, повторюсь, весь азарт как раз в том, чтобы сделать это одним запросом :)

[retvizan]

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

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