如何使用LINQ从List中的List获取分组值

loading...


-1

我希望在列表中检索属性值总和的列表,该列表本身是另一个列表的属性,使用LINQ按父列表中的属性分组 .

为了解释,我有一个市场中的报价清单,其中包含一系列产品的交易日期和时段,以及每个报价中的价格和数量范围列表 . 我的课程是:

public class Offer
{
    public DateTime TradingDate { get; set; }
    public int HourOfDay { get; set; }
    public string ProductName { get; set; }
    public List<Band> OfferBands { get; set; }
}

public class Band
{
    public decimal Price { get; set; }
    public double Quantity { get; set; }
}

我想要检索的是 Quantity 的总和 Price ,每个 TradingDateHourOfDay ,每个 ProductName .

我没有't come up with a working solution, but as a start I'尝试类似的事情(包含所有优惠的 List<Offer> offers ,以检索优惠价格<10美元的数量):

List<double> quantities = offers.SelectMany(o => o.Bands).Where(b => b.Price < 10).Select(b => b.Quantity)

但我不知道如何 GroupBy TradingDateHourOfDay 并检索 Quantity 的总和 . 对于不同的产品,可以有多个 Offer 具有多个 OfferBand ,具有 Price 的各种组合,我只想以按日期和时间分组的特定价格获得所有产品的 Quantity 之和 .

我可以通过编程实现这一点,但我想要一个LINQ解决方案 . 谢谢你的帮助 .

Edit:

我忘了提到的是,如果 TradingDateHourOfDay 指定的 Price 没有 Quantity ,我想检索 double.NaN (或 0 ) .

使用示例数据 List<Offer> offers 包含六个 Offer

TradingDate | HourOfDay | ProductName |       OfferBands
===================================================================
01/01/2017  |     1     | Chocolate   | Price = 2, Quantity = 6
            |           |             | Price = 5, Quantity = 10
-------------------------------------------------------------------
01/01/2017  |     2     | Chocolate   | Price = 3, Quantity = 6
            |           |             | Price = 5, Quantity = 20
-------------------------------------------------------------------
02/01/2017  |     1     | Chocolate   | Price = 3, Quantity = 7
            |           |             | Price = 6, Quantity = 9
-------------------------------------------------------------------
01/01/2017  |     1     | Cake        | Price = 5, Quantity = 11
            |           |             | Price = 8, Quantity = 3
-------------------------------------------------------------------
01/01/2017  |     2     | Cake        | Price = 2, Quantity = 1
            |           |             | Price = 8, Quantity = 4
-------------------------------------------------------------------
02/01/2017  |     1     | Cake        | Price = 3, Quantity = 9
            |           |             | Price = 5, Quantity = 13
-------------------------------------------------------------------

选择按日期和时间分组的给定价格的数量总和,将得到 List<double> 输出:

价格> = 5的地方

{ 24, 24, 22 }

价格= 2的地方

{ 6, 1, double.NaN }

价格= 3的地方

{ double.NaN, 6, 16 }

...其中输出是指定价格的所有产品的数量总和,时间为01/01/2017小时,01/01/2017小时2和02/01/2017小时1 .

希望这很明显可以遵循 .

3回答

  • 0

    首先,您需要进行过滤以获得匹配的 OfferBands 所需的 Offer .

    你可以创建/传入一个过滤器,如果你想使它成为一个函数,我将只是内联定义:

    Func<Band, bool> filter = (Band b) => b.Price == 3;
    

    由于您不关心 ProductName ,我使用的是匿名类型,但您可以使用 Offer 代替 . 此时,我们也抛出空槽:

    var filteredOffers = offers.Select(o => new { TradingDate = o.TradingDate, HourOfDay = o.HourOfDay, OfferBands = o.OfferBands.Where(filter).ToList() }).Where(gb => gb.OfferBands.Count > 0);
    

    现在,由于您希望包含原始数据中但已过滤掉的 TradingDate HourOfDay 的空插槽,请对过滤后的数据进行分组并创建字典:

    var mapQuantity = filteredOffers.GroupBy(o => new { o.TradingDate, o.HourOfDay })
                                    .Select(og => new { og.Key.TradingDate, og.Key.HourOfDay, QuantitySum = og.Sum(o => o.OfferBands.Sum(ob => ob.Quantity)) })
                                    .ToDictionary(og => new { og.TradingDate, og.HourOfDay }, og => og.QuantitySum);
    

    然后,返回原始的 offers 组找到所有不同的插槽( TradingDate HourOfDday )并将它们匹配到 QuantitySum ,用 double.NaN 填充空插槽并转换为 List

    var ans = offers.Select(o => new { o.TradingDate, o.HourOfDay }).Distinct().OrderBy(g => g.TradingDate).ThenBy(g => g.HourOfDay).Select(g => mapQuantity.TryGetValue(g, out var sumq) ? sumq : double.NaN).ToList();
    

    重新思考后,我意识到你可以通过保留 filteredOffers 中空的插槽来简化,然后在分组后设置它们的值:

    var filteredOffers = offers.Select(o => new { TradingDate = o.TradingDate, HourOfDay = o.HourOfDay, OfferBands = o.OfferBands.Where(filter).ToList() });
    
    var ans = filteredOffers.GroupBy(o => new { o.TradingDate, o.HourOfDay })
                            .OrderBy(og => og.Key.TradingDate).ThenBy(og => og.Key.HourOfDay)
                            .Select(og => (og.Sum(o => o.OfferBands.Count) > 0 ? og.Sum(o => o.OfferBands.Sum(ob => ob.Quantity)) : double.NaN));
    

    通过使用 IGrouping Key 来记住插槽,您可以简化查询:

    var ans = offers.GroupBy(o => new { o.TradingDate, o.HourOfDay }, o => o.OfferBands)
                    .OrderBy(obg => obg.Key.TradingDate).ThenBy(obg => obg.Key.HourOfDay)
                    .Select(obg => {
                        var filteredOBs = obg.SelectMany(ob => ob).Where(filter).ToList();
                        return filteredOBs.Count > 0 ? filteredOBs.Sum(b => b.Quantity) : double.NaN;
                    });
    

    如果你愿意放弃 double.NaN 为零,你可以使这更简单:

    var ans = offers.GroupBy(o => new { o.TradingDate, o.HourOfDay }, o => o.OfferBands)
                    .OrderBy(obg => obg.Key.TradingDate).ThenBy(obg => obg.Key.HourOfDay)
                    .Select(obg => obg.SelectMany(ob => ob).Where(filter).Sum(b => b.Quantity));
    

    最后,为了完成死马,一些特殊的扩展方法可以保留 NaN 返回属性并使用简单的查询形式:

    public static class Ext {
        static double ValuePreservingAdd(double a, double b)  => double.IsNaN(a) ? b : double.IsNaN(b) ? a : a + b;
        public static double ValuePreservingSum(this IEnumerable<double> src) => src.Aggregate(double.NaN, (a, b) => ValuePreservingAdd(a, b));
        public static double ValuePreservingSum<T>(this IEnumerable<T> src, Func<T, double> select) => src.Select(s => select(s)).Aggregate(double.NaN, (a, b) => ValuePreservingAdd(a, b));
    }
    
    var ans = offers.GroupBy(o => new { o.TradingDate, o.HourOfDay }, o => o.OfferBands)
                    .OrderBy(obg => obg.Key.TradingDate).ThenBy(obg => obg.Key.HourOfDay)
                    .Select(obg => obg.SelectMany(ob => ob).Where(filter).ValuePreservingSum(b => b.Quantity));
    

  • 0

    我相信我已经能够管理你所追求的分组,虽然我还没有完成(数量)*的总和(无论价格符合某些条件),希望这是你可以自定义但你需要的东西 .

    为了对事物进行分组,我不得不使用几个嵌套的投影并分别进行每个分组(实际上这很有趣,最重要的一点是LINQ的IGrouping并不像你想象的那样直接使用,所以每个时间我分组我用Select做了一个投影:

    var projected = offers.GroupBy(x => x.ProductName)
                                      .Select(x => new
                                      {
                                          ProductName = x.Key,
                                          Dates = x.GroupBy(y => y.TradingDate).ToList()
                                                                .Select(y => new
                                                                {
                                                                    TradingDate = y.Key,
                                                                    Times = y.GroupBy(z => z.HourOfDay).ToList()
                                                                                          .Select(zx => new
                                                                                          {
                                                                                              Time = zx.Key,
                                                                                              Items = zx.ToList()
                                                                                          })
                                                                })
                                      }).ToList();
    

    希望,这将为您提供足够的开始,您可以使用0项目所需的额外检查来进行总结,价格不够高,等等 .

    请注意,如果您直接使用数据库,此查询可能不是最有效的 - 它可能会提取比此时实际需要的更多信息 . 但是,我不知道你正在努力开始优化它 .


  • 0
    var offers = new List<Offer>();
    
            // flatten the nested list linq-style
            var flat = from x in offers
                from y in x.OfferBands
                select new {x.TradingDate, x.HourOfDay, x.ProductName, y.Price, y.Quantity};
            var grouped = from x in flat
                group x by new {x.TradingDate, x.HourOfDay, x.ProductName}
                into g
                select new
                {
                    g.Key.TradingDate,
                    g.Key.HourOfDay,
                    g.Key.ProductName,
                    OfferBands = (from y in g
                        group y by new {y.Price}
                        into q
                        select new {Price = q.Key, Quantity = q.Sum(_ => _.Quantity)}).ToList()
                };
            foreach (var item in grouped)
            {
                Console.WriteLine(
                        "TradingDate = {0}, HourOfDay = {1}, ProductName = {2}",
                        item.TradingDate,
                        item.HourOfDay,
                        item.ProductName);
                foreach (var offer in item.OfferBands)
                    Console.WriteLine("    Price = {0}, Qty = {1}", offer.Price, offer.Quantity);
            }
    

loading...

评论

暂时没有评论!