加权平均使用LINQ [英] 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    PCR
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.PCR*t2.LENGTH)/Sum(t2.LENGTH) AS WEIGHTED_AVERAGE
FROM Table1 t1, Table2 t2
WHERE t2.PCR <> -1
AND t2.ForeinKey = 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.PCR, 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. I have another version of this that simply returns 0 (because that's what is needed by the client).

在我所有的这个我使用十进制版本的< /强>而不是双。我有一些奇怪的情况下，双不够准确，从那时起，我已经使用十进制...

In all of my versions of this I use decimal instead of double. I had some odd cases where double was not accurate enough, since then I have used decimal...

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