Накладные. Расчет и обновление стоимости.

Доброго времени суток!

Есть три таблицы
invoices = накладные
invoices_items = товары и сырье, которые входят в invoices.id
products = общая таблица товаров и сырья

1) Необходимо получить информацию по всем накладным за текущую дату и -30 дней (за последние 30 дней в общем).
2) Получить все товары для каждой накладной
3) Рассчитать среднее арифметическую цену за предоставленный промежуток. Один и тот же товар/сырье может быть и по 10 раз добавлено в течении 30 дней.
4) Обновить цену в таблице products

Ниже структура и код:

Спойлер: Структура таблицы invoices

Код (Text):

1.  
2. CREATE TABLE `invoices` (
3.   `id` bigint NOT NULL,
4.   `id_provider` int NOT NULL,
5.   `text` text,
6.   `summ_all` double NOT NULL,
7.   `date` int DEFAULT NULL,
8.   `status` tinyint NOT NULL DEFAULT ‘0’
9. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
10.  
11. INSERT INTO `invoices` (`id`, `id_provider`, `text`, `summ_all`, `date`, `status`) VALUES
12. (49, 2, ‘1’, 0, 1700320013, 1);
13.  
14. ALTER TABLE `invoices`
15.   ADD PRIMARY KEY (`id`),
16.   ADD KEY `invoices_ibfk_1` (`id_provider`);
17.  
18. ALTER TABLE `invoices`
19.   MODIFY `id` bigint NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2056;
20. COMMIT;

Спойлер: Структура таблицы invoices_items

Код (Text):

1.  
2. CREATE TABLE `invoices_items` (
3.   `id` bigint NOT NULL,
4.   `invoices_id` bigint NOT NULL,
5.   `name` varchar(255) NOT NULL,
6.   `products_id` bigint NOT NULL,
7.   `type` int NOT NULL,
8.   `count` double NOT NULL,
9.   `price` double NOT NULL,
10.   `summ` double NOT NULL
11. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
12.  
13. INSERT INTO `invoices_items` (`id`, `invoices_id`, `name`, `products_id`, `type`, `count`, `price`, `summ`) VALUES
14. (24, 49, ‘Sir\’e 1’, 6, 2, 1, 1, 1),
15. (25, 49, ‘Sir\’e 1’, 6, 2, 1, 1, 1),
16. (26, 49, ‘Sir\’e 1’, 6, 2, 1, 3, 3),
17. (27, 49, ‘Sir\’e 2’, 7, 2, 1, 11, 11),
18. (28, 49, ‘Sir\’e 2’, 7, 2, 1, 22, 22);
19.  
20. ALTER TABLE `invoices_items`
21.   ADD PRIMARY KEY (`id`),
22.   ADD KEY `invoices_id` (`invoices_id`);
23.  
24. ALTER TABLE `invoices_items`
25.   MODIFY `id` bigint NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=20418;
26. COMMIT;

Спойлер: Структура таблицы products

Код (Text):

1.  
2. CREATE TABLE `products` (
3.   `id` bigint NOT NULL,
4.   `type` varchar(255) NOT NULL COMMENT ‘Тип товара: Сырье или товар’,
5.   `section` int NOT NULL COMMENT ‘ID категории (кухня/бар например)’,
6.   `name` varchar(255) NOT NULL,
7.   `price` double NOT NULL,
8.   `count` double NOT NULL COMMENT ‘Количество’,
9.   `vcode` varchar(255) NOT NULL COMMENT ‘Артикул’,
10.   `unit` smallint NOT NULL COMMENT ‘Единица измерения’,
11.   `text` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT ‘Описание’,
12.   `text2` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
13.   `stop` smallint NOT NULL DEFAULT ‘0’
14. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
15.  
16. INSERT INTO `products` (`id`, `type`, `section`, `name`, `price`, `count`, `vcode`, `unit`, `text`, `text2`, `stop`) VALUES
17. (6, ‘2’, 14, ‘Sir\’e 1’, 1.6666666666667, 1, », 1, », », 0),
18. (7, ‘2’, 14, ‘Sir\’e 2’, 16.5, 1, », 1, », », 0);
19.  
20. ALTER TABLE `products`
21.   ADD PRIMARY KEY (`id`,`section`) USING BTREE,
22.   ADD KEY `idx_section` (`section`),
23.   ADD KEY `idx_id` (`id`);
24. ALTER TABLE `products` ADD FULLTEXT KEY `name` (`name`);
25. ALTER TABLE `products` ADD FULLTEXT KEY `text` (`text`);
26. ALTER TABLE `products` ADD FULLTEXT KEY `textname` (`text`,`name`);
27.  
28. ALTER TABLE `products`
29.   MODIFY `id` bigint NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3011;
30. COMMIT;

PHP:

1. $queryInvoices = «
2. SELECT
3.  ii.name,
4.  AVG(ii.price) as average_price,
5.  ii.products_id,
6.  i.id
7. FROM
8.  invoices_items ii
9. JOIN
10.  invoices i ON ii.invoices_id = i.id
11. WHERE
12.  i.date >= UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 30 DAY))
13. GROUP BY
14.  ii.name, ii.products_id, i.id;
15. «;
16. $resultInvoices = $db->query($queryInvoices, [], ‘assoc’);
17. if ($resultInvoices)
18. {
19.     $productsPrice = [];
20.     // Перебираем все накладные
21.     foreach ( $resultInvoices as $invoice )
22.     {
23.         // Получаем элементы каждой накладной
24.         $queryItems = «SELECT `name`, `price`, `products_id` FROM `invoices_items` WHERE `invoices_id` = « . $invoice[‘id’];
25.         $resultItems = $db->query($queryItems);
26.         if ($resultItems)
27.         {
28.             // Перебираем товары и сырье в накладной
29.             foreach ( $resultItems as $item )
30.             {
31.                 // Собираем данные для вычисления среднего
32.                 $productsPrice[$item[‘products_id’]][] = $item[‘price’];
33.             }
34.         }
35.     }
36.     // Вычисляем среднее и обновляем таблицу `products`
37.     foreach ($productsPrice as $id => $prices)
38.     {
39.         $averagePrice = array_sum($prices) / count($prices);
40.         $queryUpdateProduct = «UPDATE `products` SET `price` = ? WHERE `id` = ?i»;
41.         $db->query($queryUpdateProduct, [$averagePrice, $id]);
42.     }
43. }

Насколько «хорош» такой подход? Возможно есть более элегантные способы обновления данных.
В общем, с подобным функционалом сталкиваюсь в первые, заранее спасибо и за ответы, и за советы.

 

Редактировать запрещено спустя какой-то промежуток времени.
Добавлю вторым постом.

Не хватало index-ов при EXPLAIN и поля status

Пришлось добавить в таблицу invoices индексы на два поля `date` & `status`

А запрос

Спойлер: Запрос предыдущий

Код (Text):

1. $queryInvoices = «
2. SELECT
3. ii.name,
4. AVG(ii.price) as average_price,
5. ii.products_id,
6. i.id
7. FROM
8. invoices_items ii
9. JOIN
10. invoices i ON ii.invoices_id = i.id
11. WHERE
12. i.date >= UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 30 DAY))
13. GROUP BY
14. ii.name, ii.products_id, i.id;
15. «;

Был изменен на запрос

Спойлер: Запрос новый

Код (Text):

1. $queryInvoices = «
2. SELECT
3. ii.name,
4. AVG(ii.price) as average_price,
5. ii.products_id,
6. i.id
7. FROM
8. invoices_items ii
9. JOIN
10. invoices i ON ii.invoices_id = i.id
11. WHERE
12. i.date >= UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 30 DAY)) and `i`.`status`=1
13. GROUP BY
14. ii.name, ii.products_id, i.id;
15. «;

 

любой запрос в цикле — сразу говнокод.
— Добавлено —

PHP:

1. $stmt = $pdo -> prepare( ‘INSERT …. VALUES ( ?,?,? )’ );
2. foreach ( $results AS $send )
3. {
4.     $stmt -> execute( $send );
5. }

 

Как вставлять несколько записей одним запросом я знаю, спасибо.
Так как сделать иначе и обновить уже существующие данные в большом кол-ве без цикла-то?
При тестировании 20к записей сожрало без проблем, демон раз в сутки запускаться будет сам. Более 5к записей даже не предусмотрено, все ради теста было сделано.

 

Произвести многотабличный запрос
Например

Код (Text):

1.  
2. UPDATE `t1`, `t2`
3. SET `t1`.`item` = `t2`.`item`
4. WHERE `t1`.`id` = `t2`.`id`

В качестве t2 может так же выступать представление.

Так же возможны любые типы объединений, но без сортировок и ограничений

Код (Text):

1.  
2. UPDATE `t1`
3. LEFT JOIN `t2`
4. SET `t1`.`item` = `t2`.`item`
5. WHERE `t1`.`id` = `t2`.`id`

Или так

Код (Text):

1.  
2. UPDATE `t1`
3. JOIN `t2`
4. USING(`id`)
5. SET `t1`.`item` = `t2`.`item`

 

В коем-то веке вменяемый и годно оформленный вопрос на этом форуме!

 

препарировать запрос, а потом в цикле «значения» посылать в поток. Пример же давал

 

Сделал так, нужно протестировать с большим кол-вом данных. Спасибо!

PHP:

1. $queryInvoices = «
2. SELECT
3.  ii.name,
4.  AVG(ii.price) as average_price,
5.  ii.products_id,
6.  i.id
7. FROM
8.  invoices_items ii
9. JOIN
10.  invoices i ON ii.invoices_id = i.id
11. WHERE
12.  i.date >= UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 30 DAY)) and `i`.`status`=?i
13. GROUP BY
14.  ii.name, ii.products_id, i.id;
15. «;
16.  
17. $resultInvoices = $db->query($queryInvoices, [1], ‘assoc’);
18.  
19.  
20. if ($resultInvoices)
21. {
22.     $productsPrice = [];
23.  
24.     // Перебираем все накладные
25.     foreach ( $resultInvoices as $invoice )
26.     {
27.  
28.         // Получаем элементы каждой накладной
29.         $queryItems = «SELECT `name`, `price`, `products_id` FROM `invoices_items` WHERE `invoices_id` = « . $invoice[‘id’];
30.         $resultItems = $db->query($queryItems);
31.  
32.         if ($resultItems)
33.         {
34.             // Перебираем товары и сырье в накладной
35.             foreach ( $resultItems as $item )
36.             {
37.                 // Собираем данные для вычисления среднего
38.                 $productsPrice[$item[‘products_id’]][] = $item[‘price’];
39.             }
40.         }
41.  
42.     }
43.  
44.  
45.  
46.     $updateData = [];
47.     // Начало транзакции
48.     $db->query(«START TRANSACTION»);
49.  
50.     // Сбор данных для обновления
51.     foreach ($productsPrice as $id => $prices)
52.     {
53.         $averagePrice = array_sum($prices) / count($prices);
54.         $updateData[$id] = $averagePrice; // Записываем среднюю цену с ключом ID
55.     }
56.  
57.     // Подготовка запроса обновления
58.     $queryUpdateProduct = «UPDATE `products` SET `price` = CASE `id` «;
59.     $params = [];
60.  
61.     foreach ($updateData as $id => $price)
62.     {
63.         $queryUpdateProduct .= «WHEN ? THEN ? «;
64.         $params[] = $id; // Параметр ID
65.         $params[] = $price; // Параметр цены
66.     }
67.  
68.     // Добавляем условие, чтобы обновить только те ID, для которых у нас есть цены
69.     $queryUpdateProduct .= «END WHERE `id` IN (« . implode(‘,’, array_fill(0, count($updateData), ‘?’)) . «)»;
70.     $params = array_merge($params, array_keys($updateData)); // Добавляем ID как параметры в конец
71.  
72.     // Выполнение обновления
73.     $db->query($queryUpdateProduct, $params);
74.  
75.     // Завершение транзакции
76.     $db->query(«COMMIT»);
77.  
78.  
79.  
80.  
81.  
82.  
83.  
84. }

 

Совсем забыл про select в цикле и когда добавил большое кол-во записей, понял насколько все плохо работает.
Изменил код №3 может кому интересно будет.

Спойлер: php code

PHP:

1. $queryInvoices = «
2. SELECT
3.  ii.name,
4.  AVG(ii.price) as average_price,
5.  ii.products_id,
6.  i.id
7. FROM
8.  invoices_items ii
9. JOIN
10.  invoices i ON ii.invoices_id = i.id
11. WHERE
12.  i.date >= UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 30 DAY)) and `i`.`status`=?i
13. GROUP BY
14.  ii.name, ii.products_id, i.id;
15. «;
16.  
17. $resultInvoices = $db->query($queryInvoices, [1], ‘assoc’);
18.  
19.  
20. if ($resultInvoices)
21. {
22.  
23.  
24.     $invoiceIds = array_column($resultInvoices, ‘id’); // Получаем все ID накладных
25.  
26.     $queryItems = «
27.     SELECT `name`, `price`, `products_id`, `invoices_id`
28.     FROM `invoices_items`
29.     WHERE `invoices_id` IN (?list)
30.    «;
31.  
32.     // Выполняем запрос с массивом ID накладных
33.     $resultItems = $db->query($queryItems, [$invoiceIds]);
34.  
35.     $productsPrice = [];
36.  
37.     // Перебираем все накладные
38.     foreach ($resultItems as $item)
39.     {
40.       $productsPrice[$item[‘products_id’]][] = $item[‘price’];
41.     }
42.  
43.     $updateData = [];
44.     // Начало транзакции
45.     $db->query(«START TRANSACTION»);
46.  
47.     // Сбор данных для обновления
48.     foreach ($productsPrice as $id => $prices)
49.     {
50.         $averagePrice = array_sum($prices) / count($prices);
51.         $updateData[$id] = $averagePrice; // Записываем среднюю цену с ключом ID
52.     }
53.  
54.     // Подготовка запроса обновления
55.     $queryUpdateProduct = «UPDATE `products` SET `price` = CASE `id` «;
56.     $params = [];
57.  
58.     foreach ($updateData as $id => $price)
59.     {
60.         $queryUpdateProduct .= «WHEN ? THEN ? «;
61.         $params[] = $id; // Параметр ID
62.         $params[] = $price; // Параметр цены
63.     }
64.  
65.     // Добавляем условие, чтобы обновить только те ID, для которых у нас есть цены
66.     $queryUpdateProduct .= «END WHERE `id` IN (« . implode(‘,’, array_fill(0, count($updateData), ‘?’)) . «)»;
67.     $params = array_merge($params, array_keys($updateData)); // Добавляем ID как параметры в конец
68.  
69.     // Выполнение обновления
70.     $db->query($queryUpdateProduct, $params);
71.  
72.     // Завершение транзакции
73.     $db->query(«COMMIT»);
74.  
75.  
76.  
77. }

Время выполнения скрипта: 0.49548888206482 секунд.
Накладных 5940
Обновлено данных 17820

Меня более чем устраивает.