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

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。

EDIT 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，该产品的量，和店里的均价从在t差异这个数量的帽子产品。如果你想对所有项目的商店中的平均差价，试试这个：

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; 

同样，这将只包括在每一家商店，有在每家商店的相同数量的折扣销售的项目。

Answer 2

select t1.product_id, t1.store_id, 
     100 - (t1.price * 100/t2.avg_price) 
from tab t1 join (select product_id, avg(price) as avg_price 
        from tab group by product_id) t2 
      on t1.product_id = t2.product_id; 

中计算平均的子查询每一个产品然后加入该表格可以生成您需要的结果。

Answer 3

写查询来获取每个产品的平均价格：

select product_id, avg(price) as average_price 
from mysqltable 
group by product_id 

使用它作为一个子查询，并从它指的平均价格（使用你的计算值平均价格的偏差）

select product_id, store_id, 100 - (price * 100/average_price) 
from mysqltable a 
inner join  (select product_id, avg(price) as average_price 
       from mysqltable 
       group by product_id) b 
on a.product_id = b.product_id