Перейти до вмісту
Пошук в
  • Детальніше...
Шукати результати, які ...
Шукати результати в ...

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


Recommended Posts

12 минут назад, 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 сказал:

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

 

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

 

Надіслати
Поділитися на інших сайтах

 

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

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

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

 

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

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

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

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

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

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

 

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

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

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

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

Надіслати
Поділитися на інших сайтах

Обратил внимание на одну не состыковку.
Вычисляемое поле 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.

 

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

Надіслати
Поділитися на інших сайтах

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


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

 

 

Надіслати
Поділитися на інших сайтах

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

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

Надіслати
Поділитися на інших сайтах

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

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

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

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

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

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

Надіслати
Поділитися на інших сайтах

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

Спойлер

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"

 

 

Надіслати
Поділитися на інших сайтах

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

Надіслати
Поділитися на інших сайтах

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

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

попробуйте

 

AS B HAVING RowNum <= 7

 

 

Having RowNum <10
ORDER BY category_id, RowNum

 

  • +1 1
Надіслати
Поділитися на інших сайтах

Если нужно брать товары небольшими партиями, то можно обойтись и простым 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

  • +1 2
Надіслати
Поділитися на інших сайтах

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. Но это уже другая история ¯\_(ツ)_/¯

Надіслати
Поділитися на інших сайтах

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

Надіслати
Поділитися на інших сайтах

В 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


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

Надіслати
Поділитися на інших сайтах

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

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

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

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

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

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

Надіслати
Поділитися на інших сайтах

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

 

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

Надіслати
Поділитися на інших сайтах

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

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

 

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

 

Змінено користувачем 100napb
Надіслати
Поділитися на інших сайтах

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

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

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

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

 

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

Надіслати
Поділитися на інших сайтах

  • 3 months later...

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

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

Надіслати
Поділитися на інших сайтах


Створіть аккаунт або увійдіть для коментування

Ви повинні бути користувачем, щоб залишити коментар

Створити обліковий запис

Зареєструйтеся для отримання облікового запису. Це просто!

Зареєструвати аккаунт

Вхід

Уже зареєстровані? Увійдіть тут.

Вхід зараз
  • Зараз на сторінці   0 користувачів

    • Ні користувачів, які переглядиють цю сторінку
×
×
  • Створити...

Important Information

На нашому сайті використовуються файли cookie і відбувається обробка деяких персональних даних користувачів, щоб поліпшити користувальницький інтерфейс. Щоб дізнатися для чого і які персональні дані ми обробляємо перейдіть за посиланням . Якщо Ви натиснете «Я даю згоду», це означає, що Ви розумієте і приймаєте всі умови, зазначені в цьому Повідомленні про конфіденційність.