php - 计算与平均值的百分比差异

Question

我想获取商店的价格水平百分比(低于或高于平均产品价格)，但我真的不知道从哪里开始。我该如何计算？

我有一个包含如下数据的 MySQL 表:

product_id | store_id | price | amount
1          | 1        | 10.00 | 1 
1          | 2        | 12.00 | 1 
1          | 3        | 8.00  | 1
2          | 1        | 21.20 | 1 
2          | 3        | 29.50 | 1
2          | 3        | 40.00 | 2 

我想计算每个商店的所有产品，但为了使这个示例更容易，我只使用 id 1 的产品。我认为:

10 是产品 1 的平均价格，因为它在 8 到 12 之间。因此 10 等于商店 1 的 0%。要计算商店 3 的价格水平，我可以这样做:100 - (8 * 100/10) = 20%。但我该如何对多种产品做到这一点呢？并不是每家商店都有所有产品，如果可能的话，最终我希望将其放入一个 MySQL 查询中，结果如下:

store_id  |  percentage
1         |  0
2         |  -20
3         |  20

Answer 1

我将其分成多个部分，然后将它们重新组合在一起。

首先，我得到了每个product_id的平均价格:

SELECT product_id, AVG(price) AS averagePrice
FROM myTable
GROUP BY product_id;

以下内容假设product_id 不能在商店中出现多次。因此，我将表格连接在一起，这样我就可以看到商店在该商品的平均价格旁边收取的费用:

SELECT m.product_id, m.store_id, m.price, t.averagePrice
FROM myTable m
JOIN(
  SELECT product_id, AVG(price) AS averagePrice
  FROM myTable
  GROUP BY product_id) t ON t.product_id = m.product_id;

一旦我有了这个，我就可以得到averagePrice和价格之间的差值，除以平均价格并乘以100，如下所示:

SELECT m.product_id, m.store_id, (100 * ((m.price - t.averagePrice) / t.averagePrice)) AS difference
FROM myTable m
JOIN(
  SELECT product_id, AVG(price) AS averagePrice
  FROM myTable
  GROUP BY product_id) t ON t.product_id = m.product_id;

它对我有用 SQL Fiddle .

编辑

要获得每个商店的平均价格差异(对于所有商品)，我相信您可以采用上述内容，并通过对每个商品的商店价格差异进行平均，您将获得该商店的平均价格差异，例如这个:

SELECT store_id, AVG(difference) AS averagePriceDifference
FROM(
  SELECT m.product_id, m.store_id, (100 * ((m.price - t.averagePrice) / t.averagePrice)) AS difference
  FROM myTable m
  JOIN(
    SELECT product_id, AVG(price) AS averagePrice
    FROM myTable
    GROUP BY product_id) t ON t.product_id = m.product_id) t
GROUP BY store_id;

这里是Fiddle .

编辑2

我将再次将其重新加工并尝试将其重新组合在一起。我知道我需要一个子查询来获取商店数量(所以我知道每个商店是否销售产品)，我可以使用这个:

SELECT COUNT(distinct store_id) AS storecount
FROM myTable;

现在，我可以使用它作为子查询来获取每个商店销售的产品。我可以按product_id和金额进行分组，这样如果每个商店都有金额为1的商品，并且每个商店都有金额为2的商品，那么每次都会显示出来。

SELECT product_id, amount
FROM myTable
GROUP BY product_id, amount
HAVING COUNT(distinct store_id) = (SELECT COUNT(distinct store_id) FROM myTable);

我可以添加到上面以获得该金额的每件商品的平均价格:

SELECT product_id, amount, AVG(price) AS averagePriceForAmount
FROM myTable
GROUP BY product_id, amount
HAVING COUNT(distinct store_id) = (SELECT COUNT(distinct store_id) FROM myTable);

一旦我有了这个，我就可以使用我之前使用的相同方法计算每个商店的平均价格差异，如下所示:

SELECT m.store_id, m.product_id, m.amount, (100 * ((m.price - t.averagePriceForAmount) / t.averagePriceForAmount)) AS differenceForItemAndAmount
FROM myTable m
JOIN(
  SELECT product_id, amount, AVG(price) AS averagePriceForAmount
  FROM myTable
  GROUP BY product_id, amount) t ON t.product_id = m.product_id AND t.amount = m.amount
GROUP BY m.store_id;

This将返回商店、product_id、产品金额以及商店与该金额的产品平均价格的差额。如果您想要商店所有商品的平均价格差异，请尝试以下操作:

SELECT store_id, AVG(differenceForItemAndAmount) AS averageDifferenceForStore
FROM(
  SELECT m.store_id, m.product_id, m.amount, (100 * ((m.price - t.averagePriceForAmount) / t.averagePriceForAmount)) AS differenceForItemAndAmount
  FROM myTable m
  JOIN(
    SELECT product_id, amount, AVG(price) AS averagePriceForAmount
    FROM myTable
    GROUP BY product_id, amount) t ON t.product_id = m.product_id AND t.amount = m.amount
  GROUP BY m.store_id) t
GROUP BY store_id;

同样，这仅包括在每个商店出售的商品，并且每个商店都有相同金额的折扣。