mysql - SQL:跨多个表查找产品价格的平均涨幅？

Question

我对我的作业有一个问题的 SQL 有疑问。我在 MySQL5.7 中有一个示例电子商务数据库，如下所示。不同的商品款式都具有相同的价格，一件商品可以有多种款式。我被要求 求产品价格的年均涨幅 .我对如何处理这个问题有一个大致的了解，但我在翻译为 SQL 查询时遇到了麻烦。

SQL 数据库:



我的方法是内部加入每个表，但 Items然后与 GROUP BY 聚合订单 itemsstyle.item , Years (使用变量和 Year(placed on) )。

所以我有两种方法来回答这个问题。一种是平均所有购买的元素(当然按年份汇总)，以找到当年所有元素的平均价格。这是 IMO 一种不好的方法，因为数据中的价格存在差异，并且受到那些年有多少人购买商品的影响。无论如何，我已经接近它，

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  

另一种方法是找出似乎每年都被购买的元素，然后平均增加的百分比。这就是我现在遇到的问题。

Answer 1

这是 LAG 的一个用例.但是 MySQL 5.7 既不支持分析窗口函数，也不支持公用表表达式(它们在 8.0 中出现)。

下面的查询使用两个相同的子查询来计算每种产品的年平均价格，然后将它们进行比较。订购商品的数量和价格见表order_items , 可以与 orders 连接获取订单日期。

计算规则:

产品的年平均价格定义为按数量加权的平均价格，如表 order_items 所示。 .因此，如果在同一年内，对于同一产品，您以单价 1 销售 10 件商品，并以价格 2 销售数量 5，则平均价格为 1.5

平均增幅为:(avg_price_this_year - avg_price_last_year) / avg_price_last_year

见 this db fiddle .

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