SQL: обнаружение среднего увеличения цен на продукты по нескольким таблицам?
У меня проблема с SQL из-за моего вопроса по моему заданию. У меня есть образец базы данных электронной коммерции в MySQL5.7, который выглядит следующим образом. Все стили предметов имеют одинаковую цену, у одного предмета может быть несколько стилей. Меня попросили найти среднегодовой рост цен на товары. У меня есть общее представление о том, как мне подойти к этому вопросу, но у меня проблемы с переводом на SQL-запросы.
База данных SQL:
Мой подход заключается в объединении всех таблиц, кроме Items, а затем агрегировании с заказами GROUP BYitemsstyle.item, Years (с использованием переменной и Year(placed on)).
Итак, у меня есть два подхода к ответу на этот вопрос. Один из них - это усреднение всех купленных товаров (конечно, по годам), чтобы найти среднюю цену всех товаров в этом году. Это плохой подход ИМО из-за разницы в ценах в данных, и на него влияет количество людей, купивших товары в те годы. В любом случае, я подошел к этому,
SELECT Sum(total) / Sum(quantity) AS Average,
Year(placed_on) AS year
FROM orders AS a
INNER JOIN order_items AS b
ON a.id = b.order
GROUP BY year
Другой подход состоит в том, чтобы найти предметы, которые покупались каждый год, а затем вычислить средний процент увеличения. Вот с чем у меня сейчас проблемы.
Ответы 1
Это был вариант использования LAG. Но MySQL 5.7 не поддерживает ни аналитических оконных функций, ни общих табличных выражений (они входят в 8.0).
В приведенном ниже запросе используются два идентичных подзапроса для вычисления средней годовой цены каждого продукта, а затем их сравнения. Количество и цена заказанных товаров поступают из таблицы order_items, которую можно объединить с orders, чтобы получить дату заказа.
Правила расчета:
Годовая цена продукта средний определяется как среднее значение цены, взвешенной по объему, как указано в таблице
order_items. Следовательно, если в течение того же года и для одного и того же продукта у вас есть продажа 10 единиц по цене 1 за единицу и другая продажа количества 5 по цене 2, средняя цена составит 1,5.среднее увеличение:
(avg_price_this_year - avg_price_last_year) / avg_price_last_year
SELECT
x.items,
x.year_placed_on,
x.year_price,
y.year_price last_year_price,
( x.year_price - y.year_price) / NULLIF(y.year_price, 0) increase_rate
FROM
(
SELECT ist.items, YEAR(ord.placed_on) year_placed_on, SUM(oit.price * oit.quantity) / SUM(oit.quantity) year_price
FROM item_style ist
INNER JOIN order_items oit ON oit.items_styles = ist.id
INNER JOIN orders ord ON ord.id = oit.order
GROUP BY ist.items, YEAR(ord.placed_on)
) x
LEFT JOIN (
SELECT ist.items, YEAR(ord.placed_on) year_placed_on, SUM(oit.price * oit.quantity) / SUM(oit.quantity) year_price
FROM item_style ist
INNER JOIN order_items oit ON oit.items_styles = ist.id
INNER JOIN orders ord ON ord.id = oit.order
GROUP BY ist.items, YEAR(ord.placed_on)
) y ON y.items = x.items AND y.year_placed_on = x.year_placed_on - 1