我有一个字符串数据类型,用于存储持续时间。
我在寻找持续时间的总和,然后求那个总和的平均值。
我使用的是ASP.NET MVC。
示例:
00:30:21
00:40:01
00:21:10
型号等级
public DateTime? FeedbackDateTime { get; set; }
public DateTime? FeedbackSharedDateTime { get; set; }
public string AuditorAHT { get; set; }
ReportVM对数据进行分组并在视图中显示
public string FeedbackSharedBy { get; set; }
public int AuditCount { get; set; }
public string AudtAht { get; set; }
将审计员执行的操作保存为中持续时间的控制器
public string AuditorAHT { get; set; }
dto.FeedbackSharedDateTime = DateTime.Now;
string ahtString = string.Format("{0:hh\:mm\:ss}", dto.FeedbackSharedDateTime - dto.FeedbackDateTime);
dto.AuditorAHT = ahtString;
db.SaveChanges();
下面的操作应显示审核员名称、计数和平均花费时间。名称和计数工作,但不是的平均花费时间
var audtName = db.Chats.Where(x => System.Data.Entity.DbFunctions.TruncateTime(x.MSTChatCreatedDateTime) >= mostRecentMonday
&& System.Data.Entity.DbFunctions.TruncateTime(x.MSTChatCreatedDateTime) <= weekEnd && x.Feedback != null && x.FeedbackSharedBy != null).Select(x => new {
x.FeedbackSharedBy,
x.AuditorAHT
}).ToList() // this hits the database
// We need to do grouping in the code (rather than the db)
// because timespans are stored as strings
.GroupBy(e => e.FeedbackSharedBy)
.Select(g => new ReportVM
{
FeedbackSharedBy = g.Key,
AuditCount = g.Count(),
AudtAht = TimeSpan.FromSeconds(g.Sum(t => TimeSpan.Parse(t.AuditorAHT).TotalSeconds / g.Count())).ToString()
})
.OrderByDescending(s => s.AuditCount).ToList();
ViewBag.AudtReport = audtName;
Above COde正在为我工作,并设法使其工作。
您可以将字符串持续时间转换为TimeSpan
类型,并使用它进行时间计算。要转换它,您可以使用TimeSpan.Parse()
,或者如果您有固定格式,则使用TimeSpan.ParseExact()
。
使用TimeSpan
,您可以使用各种.Total*()
方法从中获得总计。您还可以使用.Ticks
获取内部刻度计数。这是精度最高的一个。
现在它是一个简单的数学:sum of all ticks / count = average ticks
您可以将此平均ticks计数再次传递到TimeSpan
中,以将其获取为.TotalMilliseconds()
,或将其输出为.ToString()
格式。
以下是一个基本示例:
using System;
public class Program
{
public static void Main()
{
var duration1 = TimeSpan.Parse("00:30:21");
var duration2 = TimeSpan.Parse("00:40:01");
var duration3 = TimeSpan.Parse("00:21:10");
var totalDuration = duration1.Add(duration2).Add(duration3);
var averageDurationTicks = totalDuration.Ticks / 3;
var averageDuration = TimeSpan.FromTicks(averageDurationTicks);
Console.WriteLine($"Total duration: {totalDuration}, Average duration: {averageDuration}");
}
}
这是一个.Net Fiddle:https://dotnetfiddle.net/1Q9tmV
在花费大量时间并在tymtam的帮助下,使代码能够使用以下代码。
var audtName = db.Chats.Where(x => System.Data.Entity.DbFunctions.TruncateTime(x.MSTChatCreatedDateTime) >= mostRecentMonday
&& System.Data.Entity.DbFunctions.TruncateTime(x.MSTChatCreatedDateTime) <= weekEnd && x.Feedback != null && x.FeedbackSharedBy != null).Select(x => new {
x.FeedbackSharedBy,
x.AuditorAHT
}).ToList() // this hits the database
// We need to do grouping in the code (rather than the db)
// because timespans are stored as strings
.GroupBy(e => e.FeedbackSharedBy)
.Select(g => new ReportVM
{
FeedbackSharedBy = g.Key,
AuditCount = g.Count(),
AudtAht = TimeSpan.FromSeconds(g.Sum(t => TimeSpan.Parse(t.AuditorAHT).TotalSeconds / g.Count())).ToString()
})
.OrderByDescending(s => s.AuditCount).ToList();
ViewBag.AudtReport = audtName;
如果第二个精度对你来说足够了,你可以将Linq的Sum和Average与TimeSpan的Parse和TotalSeconds:结合起来
Sum = TimeSpan.FromSeconds(g.Sum(t => TimeSpan.Parse(t.Time).TotalSeconds)),
Avg = TimeSpan.FromSeconds(g.Average(t => TimeSpan.Parse(t.Time).TotalSeconds))
下面是一个完整的例子:
var data = new []{
new { X = "A", Time = "00:30:21"},
new { X = "B", Time = "00:40:01"},
new { X = "B", Time = "00:21:10"}
};
var grouped = data
.GroupBy(e => e.X)
.Select( g => new {
X = g.Key,
Count = g.Count(),
Sum = TimeSpan.FromSeconds(g.Sum(t => TimeSpan.Parse(t.Time).TotalSeconds)),
Avg = TimeSpan.FromSeconds(g.Average(t => TimeSpan.Parse(t.Time).TotalSeconds))
});
foreach (var item in grouped)
{
Console.WriteLine( $"'{item.X}' has {item.Count} item(s), sum = {item.Sum}, avg = {item.Avg}");
}
这产生:
'A' has 1 item(s), sum = 00:30:21, avg = 00:30:21
'B' has 2 item(s), sum = 01:01:11, avg = 00:30:35.5000000
您可以使用TotalMilliseconds
+FromMillisecond
,甚至可以使用超精确的Ticks
。
带有Aggregate
的变体
另一个选项是更早解析:
Sum = g.Select(e => TimeSpan.Parse(e.Time)).Aggregate((t1, t2) => t1 + t2),
Avg = g.Select(e => TimeSpan.Parse(e.Time)).Aggregate((t1, t2) => t1 + t2) / g.Count()
对于LINQ to实体
正如您在评论中报告的那样,如果我们对您的真实代码尝试上述操作,则会导致LINQ to Entities无法识别方法"System.TimeSpan FromSeconds(Double("方法,并且此方法无法转换为存储表达式(与TimeSpan.Parse
.相同
因此,我们将需要在代码中进行分组。这比TimeSpan在数据库中作为TimeSpan时效率更低。
var grouped = db.Chats
.Where(...)
.Select( x => new {
x.FeedbackSharedBy,
x.AuditorAHT
})
.ToList() // this hits the database
// We need to do grouping in the code (rather than the db)
// because timespans are stored as strings
.GroupBy(e => e.FeedbackSharedBy)
.Select(g => new
{
FeedbackSharedBy = g.Key,
AuditCount = g.Count(),
AuditorAHTSumSeconds = TimeSpan.FromSeconds(g.Sum(t => TimeSpan.Parse(t.AuditorAHT).TotalSeconds) / g.Count())
.ToString(),
})
.OrderByDescending(s => s.AuditCount)
.ToList(); // Optional