用mysql实现加权平均单价计算

各位大神，请教一下如何用mysql实现上图中红色列加权平均单价的计算呢？

入库的单价就是进价，出库的单价就是销售价；出库的时候，加权平均单价不会发生变化，每次入库加权平均单价需要重新计算，计算方式如下：

如：id为5的商品，2019.7.7日入库4个，入库单价为207，加权平均价为207；

2019.7.9日入库10个，入库单价为202，加权平均价为（207*4+202*10）/(10+4)=203.4285714;

2019.7.12日入库10个，入库单价为208，加权平均价为（203.4285714*11+208*10）/(4+10-3+10)=205.6054422

依次类推。。

SELECT AVG( qty*price) FROM `表名` GROUP BY billDate

注意：我给的答案也不是最终的答案，因为我值计算了入库数据，但是在出库时没有将其算入，所以有点小问题，但是希望对你有帮助。
 

sql：

select d.* from
(

	select c.transTypeName, c.ainvid, c.aamout, c.aprice, c.aqty, c.abilldate, (sum(c.bamount)+ c.aamout)/(sum(c.bqty)+ c.aqty) as avgValue from
	(
			select a.transTypeName as transTypeName , a.amount as aamout, a.invid as ainvid, 
						 a.price as aprice, a.qty as aqty, a.billdate as abilldate, 
						 b.amount as bamount, b.price as bprice, b.qty as bqty, b.billdate as bbilldate 
	from test1 a, test1 b where a.transTypeName='入库' and b.transTypeName ='入库' and a.invid = b.invid and a.billdate > b.billdate
	) c group by c.aamout

	union all

	select transTypeName,amount,invid,price,qty, min(billdate), price as avgValue  from test1 where transTypeName = '入库' GROUP BY invid

) d
order by d.abilldate

用到的 表结构和测试数据：

CREATE TABLE `test1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `transTypeName` varchar(255) DEFAULT NULL,
  `amount` varchar(255) DEFAULT NULL,
  `invid` int(11) DEFAULT NULL,
  `price` decimal(10,0) DEFAULT NULL,
  `qty` int(11) DEFAULT NULL,
  `billdate` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of test1
-- ----------------------------
INSERT INTO `test1` VALUES ('1', '入库', '828', '5', '207', '4', '2019-07-07 00:00:00');
INSERT INTO `test1` VALUES ('2', '入库', '200', '8', '10', '20', '2019-07-08 00:00:00');
INSERT INTO `test1` VALUES ('3', '入库', '2020', '5', '202', '10', '2019-07-09 00:00:00');
INSERT INTO `test1` VALUES ('4', '出库', '660', '5', '220', '3', '2019-07-10 00:00:00');
INSERT INTO `test1` VALUES ('5', '入库', '400', '5', '200', '2', '2019-07-11 00:00:00');
INSERT INTO `test1` VALUES ('6', '出库', '100', '5', '20', '5', '2019-07-12 00:00:00');
INSERT INTO `test1` VALUES ('7', '入库', '600', '5', '10', '60', '2019-07-20 00:00:00');

非常感谢大家细心认真的回答，加上出库又复杂了好多，如果要加上出库，大神们能提供下思路吗？