Рефакторинг обновления SQL с помощью подзапроса для воздействия на несколько строк
У меня есть работающий SQL-запрос, который рассчитывает скорость оборачиваемости запасов и обновляет указанную запись о продукте. Я хотел бы реорганизовать этот запрос, чтобы рассчитать и обновить оборот для нескольких строк таблицы. Моя проблема в том, что рабочий запрос (ниже) включает Product_id в предложение соединения подзапроса, и я не могу понять, как его удалить.
UPDATE products
SET turnover = (
WITH RECURSIVE
-- Generate a table of dates from the last year
daterange AS (
SELECT CURDATE() AS date, 1 AS n
UNION ALL
SELECT date - INTERVAL 1 DAY, n + 1
FROM daterange
WHERE n < 365
),
-- Generate a table of historic inventory AS worth
history AS (
SELECT daterange.date, IFNULL(SUM(units.price), 0) worth
FROM daterange
LEFT JOIN units
-- Include units that were acquired before the date
ON DATE(units.acquired_at) < daterange.date
-- ...and released after the date, or not at all
AND (DATE(units.released_at) > daterange.date OR units.released_at IS NULL)
-- ...and units that are not our specified product
AND units.product_id = 20593
GROUP BY daterange.date
),
-- Generate a table of inventory sale totals from the last year
sales AS (
SELECT product_id, SUM(price) AS year_total
FROM units
WHERE released_at > DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
GROUP BY product_id
)
-- Calculate turnover as annual sales divided by average inventory
SELECT (sales.year_total) / AVG(history.worth) turnover
FROM history
LEFT JOIN sales ON sales.product_id = products.id
)
WHERE id = 20593;
Я хотел бы иметь возможность удалить предложениеwhere во внешнем запросе или заменить его чем-то вроде WHERE products.type_id = 5 для работы с несколькими строками. Но это невозможно сделать, не удалив units.product_id = 20593 из подзапроса истории и заменив units.product_id = products.id, выдает ошибку «Неизвестный столбец Products.id в предложении».
Для наглядности здесь используются две таблицы: продукты и единицы измерения. Как минимум, они имеют следующие столбцы:
Любой совет приветствуется! Я открыт для изучения различных подходов к этой проблеме. Я использую MariaDB (v15.1).
@Charlieface, если я правильно понимаю, ты предлагаешь что-то вроде этого: history AS (SELECT daterange.date, units.product_id, IFNULL(SUM(units.price), 0) AS worth [...] GROUP BY daterange.date, units.product_id). Проблема в том, что AVG(history.worth) затем усредняет стоимость всех продуктов. Может быть, есть способ выбрать только строки истории с совпадающим Product_id (или нулевым значением)? Я поиграю с этим.
Нет, это будет среднее значение для каждого набора group by, то есть для даты и идентификатора продукта.
Вы можете использовать стандартную процедуру вместо одного слишком сложного запроса. Таким образом, вы сможете разбить сложность на более управляемые части. Также было бы проще оптимизировать.
что показывает select version();? v15.1 это не вещь
вам не нужно присоединяться к таблице дат, вместо этого вы можете для каждой строки подсчитать, сколько дней каждая единица находилась на складе, и сделать sum(price * inventory_days) / days in year
DATE(units.acquired_at) < daterange.date так не должно быть <=? не учитывается ли единица товара в качестве запаса в день ее приобретения? как есть, он засчитывается только начиная со следующего дня. Я думаю, об этом говорится в комментарии
для продаж вы рассматриваете полный год (включая текущую дату), а для запасов - только 365 дней? кажется, что они должны быть последовательными.
и последний комментарий: возможно, менструация закончится вчера, а не сегодня? как есть, если вы запустите это, а позже сегодня будет выпущен модуль, ваше обновление будет неверным, поэтому вы сможете запустить его только после того, как все обновления за день будут завершены.
Ответы 2
Существует гораздо более простой (и, вероятно, более быстрый) способ создания таблицы последовательных дат в MariaDB:
SELECT '2023-01-01' + INTERVAL seq-1 DAY AS a_date
FROM seq_1_to_365
Затем бросьте WITH и получите что-то вроде
UPDATE ( the above query ) AS dates
LEFT JOIN products AS p ON p.date = dates.a_date
SET p...
Ого, это фантастическая альтернатива рекурсивному оператору, который я использовал. Спасибо! К сожалению, у меня все еще возникает проблема с переписыванием подзапроса для получения исторической стоимости запасов.
Обновите свой вопрос, используя улучшенный запрос.
Используя условное агрегирование, вы можете рассчитать как продажи, так и средние запасы в одном запросе без объединений.
Я сохранил вашу существующую логику рассмотрения запасов только в течение нескольких дней между датой приобретения и датой выпуска, не включая, хотя мне это кажется странным. Но я скорректировал диапазон дат для инвентаря, чтобы он соответствовал продажам.
select
product_id,
sum(case when date(released_at) > curdate() - interval 1 year and date(released_at) <= curdate() then price end) sales,
sum(greatest(0, datediff(
least(curdate() + interval 1 day, coalesce(date(released_at), curdate() + interval 1 day)),
greatest(curdate() - interval 1 year, date(acquired_at))
) - 1) * price) / datediff(curdate(), curdate() - interval 1 year) avg_inventory
from units
group by product_id
Обновить оборот на основе этого просто; здесь я обновляю только те продукты, у которых в этом году были запасы, при необходимости корректирую:
update products
join (
select
product_id,
sum(case when date(released_at) > curdate() - interval 1 year and date(released_at) <= curdate() then price end) sales,
sum(greatest(0, datediff(
least(curdate() + interval 1 day, coalesce(date(released_at), curdate() + interval 1 day)),
greatest(curdate() - interval 1 year, date(acquired_at))
) - 1) * price) / datediff(curdate(), curdate() - interval 1 year) avg_inventory
from units
group by product_id
having avg_inventory
) summary on id = product_id
set turnover = coalesce(sales,0) / avg_inventory
Для понимания, вот промежуточный запрос, показывающий влияние на продажи и запасы, а также расчет запасов для каждой строки единиц:
select id, acquired_at, released_at,
product_id,
case when date(released_at) > curdate() - interval 1 year and date(released_at) <= curdate() then price end sales,
greatest(curdate() - interval 1 year, date(acquired_at)) after_date,
least(curdate() + interval 1 day, coalesce(date(released_at), curdate() + interval 1 day)) before_date,
greatest(0, datediff(
least(curdate() + interval 1 day, coalesce(date(released_at), curdate() + interval 1 day)),
greatest(curdate() - interval 1 year, date(acquired_at))
) - 1) inventory_days,
price,
greatest(0, datediff(
least(curdate() + interval 1 day, coalesce(date(released_at), curdate() + interval 1 day)),
greatest(curdate() - interval 1 year, date(acquired_at))
) - 1) * price / datediff(curdate(), curdate() - interval 1 year) avg_inventory
from units
Это фантастический подход. Он работает так, как и ожидалось, и выполняется намного быстрее, чем шаблоны запросов, которые я использовал. Спасибо, что нашли дополнительное время для объяснения и включили промежуточный запрос!
LEFT JOIN sales ON sales.product_id = history.product_idконечно? А затемselectэтот столбец вhistoryCTE. Это вообще действительно, разрешено ли вам помещать CTE в такой подзапрос?