Доброго времени суток! Есть три таблицы invoices = накладные invoices_items = товары и сырье, которые входят в invoices.id products = общая таблица товаров и сырья 1) Необходимо получить информацию по всем накладным за текущую дату и -30 дней (за последние 30 дней в общем). 2) Получить все товары для каждой накладной 3) Рассчитать среднее арифметическую цену за предоставленный промежуток. Один и тот же товар/сырье может быть и по 10 раз добавлено в течении 30 дней. 4) Обновить цену в таблице products Ниже структура и код: Спойлер: Структура таблицы invoices Код (Text): CREATE TABLE `invoices` ( `id` bigint NOT NULL, `id_provider` int NOT NULL, `text` text, `summ_all` double NOT NULL, `date` int DEFAULT NULL, `status` tinyint NOT NULL DEFAULT '0' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; INSERT INTO `invoices` (`id`, `id_provider`, `text`, `summ_all`, `date`, `status`) VALUES (49, 2, '1', 0, 1700320013, 1); ALTER TABLE `invoices` ADD PRIMARY KEY (`id`), ADD KEY `invoices_ibfk_1` (`id_provider`); ALTER TABLE `invoices` MODIFY `id` bigint NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2056; COMMIT; Спойлер: Структура таблицы invoices_items Код (Text): CREATE TABLE `invoices_items` ( `id` bigint NOT NULL, `invoices_id` bigint NOT NULL, `name` varchar(255) NOT NULL, `products_id` bigint NOT NULL, `type` int NOT NULL, `count` double NOT NULL, `price` double NOT NULL, `summ` double NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; INSERT INTO `invoices_items` (`id`, `invoices_id`, `name`, `products_id`, `type`, `count`, `price`, `summ`) VALUES (24, 49, 'Sir\'e 1', 6, 2, 1, 1, 1), (25, 49, 'Sir\'e 1', 6, 2, 1, 1, 1), (26, 49, 'Sir\'e 1', 6, 2, 1, 3, 3), (27, 49, 'Sir\'e 2', 7, 2, 1, 11, 11), (28, 49, 'Sir\'e 2', 7, 2, 1, 22, 22); ALTER TABLE `invoices_items` ADD PRIMARY KEY (`id`), ADD KEY `invoices_id` (`invoices_id`); ALTER TABLE `invoices_items` MODIFY `id` bigint NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=20418; COMMIT; Спойлер: Структура таблицы products Код (Text): CREATE TABLE `products` ( `id` bigint NOT NULL, `type` varchar(255) NOT NULL COMMENT 'Тип товара: Сырье или товар', `section` int NOT NULL COMMENT 'ID категории (кухня/бар например)', `name` varchar(255) NOT NULL, `price` double NOT NULL, `count` double NOT NULL COMMENT 'Количество', `vcode` varchar(255) NOT NULL COMMENT 'Артикул', `unit` smallint NOT NULL COMMENT 'Единица измерения', `text` text CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT 'Описание', `text2` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, `stop` smallint NOT NULL DEFAULT '0' ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; INSERT INTO `products` (`id`, `type`, `section`, `name`, `price`, `count`, `vcode`, `unit`, `text`, `text2`, `stop`) VALUES (6, '2', 14, 'Sir\'e 1', 1.6666666666667, 1, '', 1, '', '', 0), (7, '2', 14, 'Sir\'e 2', 16.5, 1, '', 1, '', '', 0); ALTER TABLE `products` ADD PRIMARY KEY (`id`,`section`) USING BTREE, ADD KEY `idx_section` (`section`), ADD KEY `idx_id` (`id`); ALTER TABLE `products` ADD FULLTEXT KEY `name` (`name`); ALTER TABLE `products` ADD FULLTEXT KEY `text` (`text`); ALTER TABLE `products` ADD FULLTEXT KEY `textname` (`text`,`name`); ALTER TABLE `products` MODIFY `id` bigint NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3011; COMMIT; PHP: $queryInvoices = " SELECT ii.name, AVG(ii.price) as average_price, ii.products_id, i.id FROM invoices_items ii JOIN invoices i ON ii.invoices_id = i.id WHERE i.date >= UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 30 DAY)) GROUP BY ii.name, ii.products_id, i.id; "; $resultInvoices = $db->query($queryInvoices, [], 'assoc'); if ($resultInvoices) { $productsPrice = []; // Перебираем все накладные foreach ( $resultInvoices as $invoice ) { // Получаем элементы каждой накладной $queryItems = "SELECT `name`, `price`, `products_id` FROM `invoices_items` WHERE `invoices_id` = " . $invoice['id']; $resultItems = $db->query($queryItems); if ($resultItems) { // Перебираем товары и сырье в накладной foreach ( $resultItems as $item ) { // Собираем данные для вычисления среднего $productsPrice[$item['products_id']][] = $item['price']; } } } // Вычисляем среднее и обновляем таблицу `products` foreach ($productsPrice as $id => $prices) { $averagePrice = array_sum($prices) / count($prices); $queryUpdateProduct = "UPDATE `products` SET `price` = ? WHERE `id` = ?i"; $db->query($queryUpdateProduct, [$averagePrice, $id]); } } Насколько "хорош" такой подход? Возможно есть более элегантные способы обновления данных. В общем, с подобным функционалом сталкиваюсь в первые, заранее спасибо и за ответы, и за советы.
Редактировать запрещено спустя какой-то промежуток времени. Добавлю вторым постом. Не хватало index-ов при EXPLAIN и поля status Пришлось добавить в таблицу invoices индексы на два поля `date` & `status` А запрос Спойлер: Запрос предыдущий Код (Text): $queryInvoices = " SELECT ii.name, AVG(ii.price) as average_price, ii.products_id, i.id FROM invoices_items ii JOIN invoices i ON ii.invoices_id = i.id WHERE i.date >= UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 30 DAY)) GROUP BY ii.name, ii.products_id, i.id; "; Был изменен на запрос Спойлер: Запрос новый Код (Text): $queryInvoices = " SELECT ii.name, AVG(ii.price) as average_price, ii.products_id, i.id FROM invoices_items ii JOIN invoices i ON ii.invoices_id = i.id WHERE i.date >= UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 30 DAY)) and `i`.`status`=1 GROUP BY ii.name, ii.products_id, i.id; ";
любой запрос в цикле - сразу говнокод. --- Добавлено --- PHP: $stmt = $pdo -> prepare( 'INSERT .... VALUES ( ?,?,? )' ); foreach ( $results AS $send ) { $stmt -> execute( $send ); }
Как вставлять несколько записей одним запросом я знаю, спасибо. Так как сделать иначе и обновить уже существующие данные в большом кол-ве без цикла-то? При тестировании 20к записей сожрало без проблем, демон раз в сутки запускаться будет сам. Более 5к записей даже не предусмотрено, все ради теста было сделано.
Произвести многотабличный запрос Например Код (Text): UPDATE `t1`, `t2` SET `t1`.`item` = `t2`.`item` WHERE `t1`.`id` = `t2`.`id` В качестве t2 может так же выступать представление. Так же возможны любые типы объединений, но без сортировок и ограничений Код (Text): UPDATE `t1` LEFT JOIN `t2` SET `t1`.`item` = `t2`.`item` WHERE `t1`.`id` = `t2`.`id` Или так Код (Text): UPDATE `t1` JOIN `t2` USING(`id`) SET `t1`.`item` = `t2`.`item`
Сделал так, нужно протестировать с большим кол-вом данных. Спасибо! PHP: $queryInvoices = " SELECT ii.name, AVG(ii.price) as average_price, ii.products_id, i.id FROM invoices_items ii JOIN invoices i ON ii.invoices_id = i.id WHERE i.date >= UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 30 DAY)) and `i`.`status`=?i GROUP BY ii.name, ii.products_id, i.id; "; $resultInvoices = $db->query($queryInvoices, [1], 'assoc'); if ($resultInvoices) { $productsPrice = []; // Перебираем все накладные foreach ( $resultInvoices as $invoice ) { // Получаем элементы каждой накладной $queryItems = "SELECT `name`, `price`, `products_id` FROM `invoices_items` WHERE `invoices_id` = " . $invoice['id']; $resultItems = $db->query($queryItems); if ($resultItems) { // Перебираем товары и сырье в накладной foreach ( $resultItems as $item ) { // Собираем данные для вычисления среднего $productsPrice[$item['products_id']][] = $item['price']; } } } $updateData = []; // Начало транзакции $db->query("START TRANSACTION"); // Сбор данных для обновления foreach ($productsPrice as $id => $prices) { $averagePrice = array_sum($prices) / count($prices); $updateData[$id] = $averagePrice; // Записываем среднюю цену с ключом ID } // Подготовка запроса обновления $queryUpdateProduct = "UPDATE `products` SET `price` = CASE `id` "; $params = []; foreach ($updateData as $id => $price) { $queryUpdateProduct .= "WHEN ? THEN ? "; $params[] = $id; // Параметр ID $params[] = $price; // Параметр цены } // Добавляем условие, чтобы обновить только те ID, для которых у нас есть цены $queryUpdateProduct .= "END WHERE `id` IN (" . implode(',', array_fill(0, count($updateData), '?')) . ")"; $params = array_merge($params, array_keys($updateData)); // Добавляем ID как параметры в конец // Выполнение обновления $db->query($queryUpdateProduct, $params); // Завершение транзакции $db->query("COMMIT"); }
Совсем забыл про select в цикле и когда добавил большое кол-во записей, понял насколько все плохо работает. Изменил код №3 может кому интересно будет. Спойлер: php code PHP: $queryInvoices = " SELECT ii.name, AVG(ii.price) as average_price, ii.products_id, i.id FROM invoices_items ii JOIN invoices i ON ii.invoices_id = i.id WHERE i.date >= UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 30 DAY)) and `i`.`status`=?i GROUP BY ii.name, ii.products_id, i.id; "; $resultInvoices = $db->query($queryInvoices, [1], 'assoc'); if ($resultInvoices) { $invoiceIds = array_column($resultInvoices, 'id'); // Получаем все ID накладных $queryItems = " SELECT `name`, `price`, `products_id`, `invoices_id` FROM `invoices_items` WHERE `invoices_id` IN (?list) "; // Выполняем запрос с массивом ID накладных $resultItems = $db->query($queryItems, [$invoiceIds]); $productsPrice = []; // Перебираем все накладные foreach ($resultItems as $item) { $productsPrice[$item['products_id']][] = $item['price']; } $updateData = []; // Начало транзакции $db->query("START TRANSACTION"); // Сбор данных для обновления foreach ($productsPrice as $id => $prices) { $averagePrice = array_sum($prices) / count($prices); $updateData[$id] = $averagePrice; // Записываем среднюю цену с ключом ID } // Подготовка запроса обновления $queryUpdateProduct = "UPDATE `products` SET `price` = CASE `id` "; $params = []; foreach ($updateData as $id => $price) { $queryUpdateProduct .= "WHEN ? THEN ? "; $params[] = $id; // Параметр ID $params[] = $price; // Параметр цены } // Добавляем условие, чтобы обновить только те ID, для которых у нас есть цены $queryUpdateProduct .= "END WHERE `id` IN (" . implode(',', array_fill(0, count($updateData), '?')) . ")"; $params = array_merge($params, array_keys($updateData)); // Добавляем ID как параметры в конец // Выполнение обновления $db->query($queryUpdateProduct, $params); // Завершение транзакции $db->query("COMMIT"); } Время выполнения скрипта: 0.49548888206482 секунд. Накладных 5940 Обновлено данных 17820 Меня более чем устраивает.