如何按数据分组，并按日期、时间和再次从每组中选择第一条记录？

Question

我有一张桌子如下所示：

JobSchedular:

JobId     ProcessId      DepartmentId         Status         ProcessedTime    
1         100            10                   Done           2022-11-08 19:41
2         101            10                   Done           2022-11-09 19:41
3         100            10                   Failed         2022-11-09 19:41

我想要group by ProcessId and select top 1 Status by latest ProcessedTime(order by ProcessedTime desc)，然后再按"Status“分组来计算"Done”和"Failed“统计数据。

最终输出：

Done = 1
Failed = 1

将基于最新的Fail考虑ProcessId = 100的“2022-11-09 19:41”状态。

代码：

var statistics = (from js in context.JobSchedular
                  where js.DepartmentId == 10
                  group by ProcessId //
                  select new StatisticsModel
                  {
                     Done = 1,
                     Failed = 1
                  }
                  ).FirstOrDefault();

在这里，我有点困惑于按ProcessId分组，根据最新的ProcessedTime选择前1状态，然后按“状态”进一步分组以计算统计数据。

有人能帮忙吗？

Answer 1

如果我正确地理解了预期的结果，那么它应该是以下查询：

var jobs = 
    from js in context.JobSchedular
    where js.DepartmentId == 10
    select js;

var staitsticQuery = 
    from js in jobs
    group js by js.ProcessId into g
    select new 
    {
        ProcessId = g.Key,
        Done = g.Sum(x => x.Status == "Done" ? 1 : 0),
        Failed = g.Sum(x => x.Status == "Failed" ? 1 : 0)
    };

var finalQuery = 
    from s in staitsticQuery
    from js in jobs
        .Where(js => js.ProcessId == s.ProcessId)
        .OrderByDescending(js => js.ProcessedTime)
        .Take(1)
    select new 
    {
        ProcessId = s.ProcessId,
        Done = s.Done,
        Failed = s.Failed,
        LastStatus = js.Status
    };

var statistics = finalQuery.ToList();