Linq GroupBy查询中空组的默认值 [英] Default values for empty groups in Linq GroupBy query

问题描述

我有一组要汇总的值的数据集.对于每个组，我想创建一个足够大的数组以包含最大组的值.当组包含的数量少于此最大数量时，我想为空键值插入默认值零.

I have a data set of values that I want to summarise in groups. For each group, I want to create an array big enough to contain the values of the largest group. When a group contains less than this maximum number, I want to insert a default value of zero for the empty key values.

数据集

Col1    Col2    Value
--------------------
A       X       10
A       Z       15
B       X       9
B       Y       12
B       Z       6

所需结果

X, [10, 9]
Y, [0, 12]
Z, [15, 6]

请注意，数据集中Col1中的值"A"在Col2中没有"Y"值.值"A"是外部系列中的第一组，因此它是缺少的第一个元素.

Note that value "A" in Col1 in the dataset has no value for "Y" in Col2. Value "A" is first group in the outer series, therefore it is the first element that is missing.

以下查询将创建结果数据集，但不会为Y组插入默认的零值.

The following query creates the result dataset, but does not insert the default zero values for the Y group.

result = data.GroupBy(item => item.Col2)
             .Select(group => new
             {
                 name = group.Key,
                 data = group.Select(item => item.Value)
                             .ToArray()
             })

实际结果

X, [10, 9]
Y, [12]
Z, [15, 6]

我需要怎么做才能插入零作为丢失的组值?

What do I need to do to insert a zero as the missing group value?

推荐答案

这是我的理解方式.

让我们说这个

class Data
{
    public string Col1, Col2;
    public decimal Value;
}

Data[] source =
{
    new Data { Col1="A", Col2 = "X", Value = 10 },
    new Data { Col1="A", Col2 = "Z", Value = 15 },
    new Data { Col1="B", Col2 = "X", Value = 9 },
    new Data { Col1="B", Col2 = "Y", Value = 12 },
    new Data { Col1="B", Col2 = "Z", Value = 6 },
};

首先，我们需要确定固定"部分

First we need to determine the "fixed" part

var columns = source.Select(e => e.Col1).Distinct().OrderBy(c => c).ToList();

然后我们可以进行常规分组，但是在组内，我们将左联接与组元素columns，这将使我们能够实现所需的行为

Then we can process with the normal grouping, but inside the group we will left join the columns with group elements which will allow us to achieve the desired behavior

var result = source.GroupBy(e => e.Col2, (key, elements) => new
{
    Key = key,
    Elements = (from c in columns
             join e in elements on c equals e.Col1 into g
             from e in g.DefaultIfEmpty()
             select e != null ? e.Value : 0).ToList()
})
.OrderBy(e => e.Key)
.ToList();

这篇关于Linq GroupBy查询中空组的默认值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！