查询表某列的加权平均值

以一个简单商品表为例,商品表包含商品编号,批次,数量,价格等字段,现在想要查询不同批次商品的加权平均价,具体问题描述如下:

建表语句(展开-复制-运行即可初始化数据):


表数据如下:


每个商品有不同的批次,每个批次又有不同的价格 如下图所示:

现在要实现的查询是: 根据商品ID,查询出该商品的所有批次及数量,以及加权平均价格;

查询显示结果如下:

 

分析过程:

1.销售总量字段容易被查出了,一个Sum语句就够了,难点在于将批次(字符串)求和。

   解决思路 :专门写一个自定义函数来获取批次及数量的叠加内容:

   CREATE FUNCTION GetString
   (@id int)
   RETURNS nvarchar(500) AS
   BEGIN
   declare @all nvarchar(500)
   set @all=''
   select @all=@all+ p.BatchNumber+'('+cast(sum(p.Amount)as char(100))+')'+','
   from Product as p where ProductID=@id
   group by p.BatchNumber
   return @all 
   END

2.考虑加权平均价的计算,这里可以用临时表的方法实现。

   第一步 :增加一个计算列,总价-total

      select *,Price*Amount as total from product where ProductID=1

   第二步: 查询出加权平均价

     select sum(total)/sum(Amount) from(select *,Price*Amount as total from product where ProductID=1)temp

   第三步 :将查询语句集中起来得到查询结果

   select replace(dbo.GetString (P.ProductID),' ','')as '批次及数量',
            sum(P.Amount)as '总销售量',
            (select sum(total)/sum(Amount)from(select *,Price*Amount as total from product where ProductID=1)temp)as '加权平均价'
            from product as P
            where ProductID=1
            group by ProductID

   注: replace函数用来除去查询结果中的空字符。

小结 :运行下面代码,即可查询结果。

   select replace(dbo.GetString (P.ProductID),' ','')as '批次及数量',
   sum(P.Amount)as '总销售量',
   (select sum(total)/sum(Amount)from(select *,Price*Amount as total from product where ProductID=1)temp)as '加权平均价'
    from product as P
    where ProductID=1
    group by ProductID

 

 

<div class="post-text" itemprop="text"> <p>Given the following table of stock transactions:</p> <pre><code>TID |DATE |TIME |SYMBOL|SIDE|QUANTITY |PRICE |OPENPOSITION 339791|2014-11-14|12:45:25|ABEV3 |Buy | -900.00|15.920000| -900 339780|2014-11-21|10:54:37|ABEV3 |Sell| 900.00|16.650000| 0 339775|2014-11-24|14:52:59|ABEV3 |Buy | -1500.00|16.950000| -1500 339725|2017-01-20|14:54:26|ABEV3 |Sell| 1500.00|17.280000| 0 339662|2017-02-03|10:43:31|ABEV3 |Buy | -5900.00|17.020000| -5900 339661|2017-02-03|11:44:57|ABEV3 |Buy | -5900.00|17.229492| -11800 339655|2017-02-03|12:37:08|ABEV3 |Sell| 10800.00|17.250000| -1000 339528|2017-02-15|11:04:07|ABEV3 |Buy |-15000.00|17.580000| -16000 339527|2017-02-15|12:07:30|ABEV3 |Sell| 2300.00|17.610000| -13700 339524|2017-02-15|12:10:36|ABEV3 |Sell| 100.00|17.620000| -13600 339522|2017-02-15|12:44:23|ABEV3 |Sell| 14900.00|17.640000| 1300 339518|2017-02-15|12:49:52|ABEV3 |Buy | -2300.00|17.670000| -1000 339474|2017-02-17|11:45:33|ABEV3 |Buy |-20000.00|17.860000| -21000 339472|2017-02-17|13:36:16|ABEV3 |Sell| 20000.00|17.960000| -1000 </code></pre> <p>How can I generate a mysql query to compute the avg weighted price of a transaction whether it is a buy or sell.</p> <p>In the example above, the trader started buying 900 shares and selling 900 shares for a position balance of 0 (see second row). He does the same thing with 1500 shares, but then he buys and sells several times and remains with 1000 shares left. When calculated by hand, the avg weighed price of purchase is </p> <p>5900*17.23+5900*17.02+2300*17.67+15000*17.58+20000*17.86/49100 = $17.59</p> <p>Is there a way to build a query or php functions that considers only the prices of transactions that are still open?</p> </div>
©️2020 CSDN 皮肤主题: 成长之路 设计师:Amelia_0503 返回首页