时间戳

首页 > 解决方案 > 如何在 c# 中按日期时间对特定范围进行分组？

c# - 如何在 c# 中按日期时间对特定范围进行分组？

问题描述

在我的 aspnet core 3.1 应用程序中，我使用的是 CQRS 模式。我有 Bulling 汇总表，每小时计算一次（Hangifre 后台工作）一些价格

示例数据如下所示：

Price - 10, StartDate - 2020/08/02 10:00 , EndDate - 2020/08/03 11:00
Price - 10, StartDate - 2020/08/02 11:00 , EndDate - 2020/08/03 12:00
Price - 10, StartDate - 2020/08/02 13:00 , EndDate - 2020/08/03 14:00
Price - 10, StartDate - 2020/08/02 14:00 , EndDate - 2020/08/03 15:00
Price - 10, StartDate - 2020/08/02 15:00 , EndDate - 2020/08/03 16:00
Price - 10, StartDate - 2020/08/02 16:00 , EndDate - 2020/08/03 17:00

我想达到类似的效果：如果我指定 periodDuration=3h

Price - 30, StartDate - 2020/08/02 10:00 , EndDate - 2020/08/03 13:00
Price - 30, StartDate - 2020/08/02 13:00 , EndDate - 2020/08/03 16:00
Price - 30, StartDate - 2020/08/02 19:00 , EndDate - 2020/08/03 22:00

我的方法看起来像：

        var billing = _context.BillingSummaries.AsQueryable();         

        switch (request.SortBy)
        {
            case "createdAt" when request.SortDirection == "asc":
                billing = billing.OrderBy(x => x.BeginApply);
                break;
            case "createdAt" when request.SortDirection == "desc":
                billing = billing.OrderByDescending(x => x.BeginApply);
                break;
        }

        if (request.StartDate.HasValue)
        {
            billing = billing.Where(x =>
                x.BeginApply >= request.StartDate);
        }

        if (request.EndDate.HasValue)
        {
            billing = billing.Where(x =>
                x.EndApply <= request.EndDate);
        }

        // Want to achieve this
        billing = request.PeriodDuration switch
        {
            "3h" => "calculate 3 hours range",
            "6h" => "calculate 6 hours range",
            "12h" => "calculate 12 hours range",
            "d" => "calculate daily range",
            "w" => "calculate weekly range",
            "m" => "calculate monthly range",
            _ => billing
        };

        var billings = await billing.Skip(request.Offset ?? 0).Take(request.Limit ?? 50)
            .ToListAsync(cancellationToken: cancellationToken);

        if (billings == null)
            throw new NotFoundException("Not found");

        return new BillingInfo
        {
            Data = _mapper.Map<List<BillingSummary>, List<BillingSummaryDto>>(billings),
            TotalCount = await billing.CountAsync(cancellationToken: cancellationToken),
            AllPrice = await billing.SumAsync(x => x.Price, cancellationToken: 
             cancellationToken),
            Currency = currency.ToString("G")
        };

标签： c#linqasp.net-core

解决方案

首先按日期对数据进行分组，然后尝试

 public class Billingdata
{
    public int Price { get; set; }
    public DateTime Start { get; set; }
    public DateTime End { get; set; }
}
public class GroupingData {
    public int Start { get; set; }
    public int End { get; set; } 
}
var timerangelist = new List<GroupingData> { 
        new GroupingData{ Start = 10, End=13 },
        new GroupingData{ Start = 13, End=16 },
        new GroupingData{ Start = 16, End=19 },
        new GroupingData{ Start = 19, End=22 },

        };
        var result = new List<Billingdata>();

        var billings = new List<Billingdata> {
        new Billingdata{Price = 10, Start = new DateTime(2020,08,02,10,00,00), End = new DateTime(2020,08,02,11,00,00) },
        new Billingdata{Price = 10, Start = new DateTime(2020,08,02,11,00,00), End = new DateTime(2020,08,02,12,00,00) },
        new Billingdata{Price = 10, Start = new DateTime(2020,08,02,12,00,00), End = new DateTime(2020,08,02,13,00,00) },
        new Billingdata{Price = 10, Start = new DateTime(2020,08,02,13,00,00), End = new DateTime(2020,08,02,14,00,00) },
        new Billingdata{Price = 10, Start = new DateTime(2020,08,02,14,00,00), End = new DateTime(2020,08,02,15,00,00) },
        new Billingdata{Price = 10, Start = new DateTime(2020,08,02,15,00,00), End = new DateTime(2020,08,02,16,00,00) },
        new Billingdata{Price = 10, Start = new DateTime(2020,08,02,16,00,00), End = new DateTime(2020,08,02,17,00,00) }
        };
        foreach (var item in timerangelist)
        {
            var data = billings.Where(w => item.End >= w.End.Hour && w.Start.Hour >= item.Start).ToList();
            var p = data.Sum(s => s.Price);
            var ss = data.FirstOrDefault(f => f.Start.Hour == item.Start)?.Start;
            var e = data.FirstOrDefault(f => f.End.Hour == item.End)?.End;
            if (ss == null || e == null) { continue; }
            result.Add(
                new Billingdata { Price = p, 
                    Start = (DateTime)ss,
                    End = (DateTime)e,
                }
                );
        }



enter code here

推荐阅读