使用LINQ计算加权平均值 [英] Calculating Weighted Average with LINQ

问题描述

我的目标是从另一张表的主键获取一张表的加权平均值.

My goal is to get a weighted average from one table, based on another tables primary key.

示例数据:

表1

Key     WEIGHTED_AVERAGE

0200    0

表2

ForeignKey    Length    Value
0200          105       52
0200          105       60
0200          105       54
0200          105       -1
0200          47        55

我需要根据段的长度获得加权平均值，并且我需要忽略-1的值.我知道如何在SQL中执行此操作，但我的目标是在LINQ中执行此操作.在SQL中看起来像这样:

I need to get a weighted average based on the length of a segment and I need to ignore values of -1. I know how to do this in SQL, but my goal is to do this in LINQ. It looks something like this in SQL:

SELECT Sum(t2.Value*t2.Length)/Sum(t2.Length) AS WEIGHTED_AVERAGE
FROM Table1 t1, Table2 t2
WHERE t2.Value <> -1
AND t2.ForeignKey = t1.Key;

我对LINQ还是很陌生，并且很难弄清楚该如何翻译.结果加权平均值应约为55.3.谢谢.

I am still pretty new to LINQ, and having a hard time figuring out how I would translate this. The result weighted average should come out to roughly 55.3. Thank you.

推荐答案

我做了足够的工作，为LINQ创建了扩展方法.

I do this enough that I created an extension method for LINQ.

public static double WeightedAverage<T>(this IEnumerable<T> records, Func<T, double> value, Func<T, double> weight)
{
    double weightedValueSum = records.Sum(x => value(x) * weight(x));
    double weightSum = records.Sum(x => weight(x));

    if (weightSum != 0)
        return weightedValueSum / weightSum;
    else
        throw new DivideByZeroException("Your message here");
}

获取数据子集后，调用将如下所示.

After you get your subset of data the call looks like this.

double weightedAverage = records.WeightedAverage(x => x.Value, x => x.Length);

这变得非常方便，因为我可以基于同一记录中的另一个字段来获得任何一组数据的加权平均值.

This has become extremely handy because I can get a weighted average of any group of data based on another field within the same record.

更新

我现在检查是否被零除并抛出更详细的异常，而不是返回0.允许用户捕获异常并根据需要进行处理.

I now check for dividing by zero and throw a more detailed exception instead of returning 0. Allows user to catch the exception and handle as needed.

这篇关于使用LINQ计算加权平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！