我有下面的数据表:
数量价格日期5 2 1/256 1 1/258 3 4/251 2 4/25
我想要:
数量价格日期11 1.45 1/259 2.88 4/25
我已经用按日期对数据表进行了分组
var groups=ToCalc.AsEnumerable().GroupBy(r=>r.Field("trade_date"));
数量总和类似于:
foreach(分组中的var组){qty=Convert.ToDouble(group.Sum(r=>r.Field("EXEC_qty"));}
但我不知道如何计算加权平均价格。知道吗?
weightedAverage = group.Sum(r => r.Field("EXEC_QTY") * r.Field("price"))
/ group.Sum(r => r.Field("EXEC_QTY"));
如果您真的需要,请添加Convert.ToDouble。
既然你评论说数量是两倍,价格是小数:
weightedAverage = group.Sum(r => (decimal)r.Field("EXEC_QTY") * r.Field("price"))
/ group.Sum(r => (decimal)r.Field("EXEC_QTY"));
(价格是十进制,数量是双倍,或者两者都是十进制,这不是更有意义吗?)
class Data
{
public int Qty { get; set; }
public double Price { get; set; }
public string Date { get; set; }
}
static void Main(string[] args)
{
var data = new Data[] {
new Data { Qty = 5, Price = 2, Date = "1/25" },
new Data { Qty = 6, Price = 1, Date = "1/25" },
new Data { Qty = 8, Price = 3, Date = "4/25" },
new Data { Qty = 1, Price = 2, Date = "4/25" },
};
var weighted = data.GroupBy(x => x.Date)
.Select(group => new Data {
Date = group.Key,
Qty = group.Sum(i => i.Qty),
Price = group.Sum(i => i.Price * i.Qty) / group.Sum(i => i.Qty)
});
}
我会这样做:
var results = ToCalc.AsEnumerable()
.GroupBy(r => r.Field("trade_date"))
.Select(grp =>
{
var quantity = grp.Sum(r => r.Field("EXEC_QTY"));
var avg = grp.Sum(r => r.Field("EXEC_QTY") * r.Field("price")) / quantity;
return new {
TradeDate = grp.Key,
Quantity = quantity,
WeightedAverage = avg
};
});
results
将是一个可枚举的匿名对象,包含您想要的信息,这是一个更容易访问的结构。
您可以使用以下Enumerable.GroupBy
重载在一个方法内执行所有分组:
var groups = ToCalc.AsEnumerable()
.GroupBy(r => r.date, (k, g) => new
{
date = k,
qty = g.Sum(i => i.qty),
price = g.Sum(i => i.qty * i.price) / g.Sum(i => i.qty)
});
这会给你一些类似的东西:
{ date = "1/25", qty = 11, price = 1.4545454545454546 }
{ date = "4/25", qty = 9, price = 2.8888888888888888 }