使用LINQ按小时分组
本文关键字：小时 LINQ 使用 | 更新日期: 2023-09-27 18:28:30
我每隔15分钟将数据记录到PowerStringHistory和PowerCombinaterHistory表中。我是LINQ的新手，正在尝试如何创建一个查询，按每小时对我的数据进行分组，并对该小时的当前数据进行平均。这是我迄今为止拥有的
TimeZoneInfo easternZone = TimeZoneInfo.FindSystemTimeZoneById("Eastern Standard Time");
DateTime UTC_StartingDate = TimeZoneInfo.ConvertTimeToUtc(StartingDate, easternZone);
DateTime UTC_EndingDate = TimeZoneInfo.ConvertTimeToUtc(EndingDate, easternZone);
var FirstQ = from p in db.PowerStringHistorys
join t in db.PowerStrings on p.string_id equals t.id
join u in db.PowerCombiners on t.combiner_id equals u.id
join s in db.PowerCombinerHistorys on p.recordTime equals s.recordTime
where p.recordTime >= UTC_StartingDate
where p.recordTime <= UTC_EndingDate
select new
{
Combiner = u.id,
Current = p.current,
RecordTime = p.recordTime,
Voltage = s.voltage
};
现在我需要按组合器和小时进行分组，这样我就可以对电流进行平均，并获得指定日期范围内每小时每个组合器的千瓦时。
我需要以某种方式在查询中应用这个简单的公式：（平均瓦特/小时）/1000=千瓦时
因此，我将以以下内容结束。如有任何帮助，我们将不胜感激。
Combiner 1 03/19/2012 1:0:0 1.85 Kwh
Combiner 1 03/19/2012 2:0:0 1.98 Kwh
Combiner 1 03/19/2012 3:0:0 2.05 Kwh
Combiner 1 03/19/2012 4:0:0 2.11 Kwh
Combiner 1 03/19/2012 5:0:0 2.01 Kwh
Combiner 1 03/19/2012 6:0:0 1.96 Kwh
Combiner 1 03/19/2012 7:0:0 1.85 Kwh
Combiner 2 03/19/2012 1:0:0 1.77 Kwh
Combiner 2 03/19/2012 2:0:0 1.96 Kwh
Combiner 2 03/19/2012 3:0:0 2.03 Kwh
Combiner 2 03/19/2012 4:0:0 2.11 Kwh
Combiner 2 03/19/2012 5:0:0 2.02 Kwh
Combiner 2 03/19/2012 6:0:0 1.98 Kwh
Combiner 2 03/19/2012 7:0:0 1.83 Kwh
Combiner 3 03/19/2012 1:0:0 1.77 Kwh
Combiner 3 03/19/2012 2:0:0 1.96 Kwh
Combiner 3 03/19/2012 3:0:0 2.03 Kwh
Combiner 3 03/19/2012 4:0:0 2.11 Kwh
Combiner 3 03/19/2012 5:0:0 2.02 Kwh
Combiner 3 03/19/2012 6:0:0 1.98 Kwh
Combiner 3 03/19/2012 7:0:0 1.83 Kwh
编辑
以上是我最初的问题。在处理了我收到的两个建议后，我得到了下面显示的代码。现在，我只是将日期和总Kwhs返回到视图中。我确实计划将stringGroupedKwhlist列表放入HighChart中供用户查看，并将firstQ查询结果放入Telerik网格中供用户过滤/排序/分组，以便他们可以处理详细信息。虽然代码确实有效并产生了我所期望的结果，但我不确定它是否有效。由于我必须使用foreach循环，我猜一旦它获得了大量数据，它可能会减慢速度。有没有更有效的方法来处理这个问题？
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web.Mvc;
using AESSmart.Models;
namespace AESSmart.Controllers
{
public class StringKwh
{
public int CombinerID;
public int StringID;
public DateTime Interval;
public Double KWH;
public StringKwh(int combiner, int stringid, DateTime interval, double kwh)
{
CombinerID = combiner;
StringID = stringid;
Interval = interval;
KWH = kwh;
}
}
public class HomeController : Controller
{
private readonly AESSmartEntities db = new AESSmartEntities();
public ActionResult Index()
{
//REPRESENTS DATE RANGE FOR A FULL DAY
DateTime startingDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 0, 0, 1);
DateTime endingDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 23, 59, 59);
TimeZoneInfo easternZone = TimeZoneInfo.FindSystemTimeZoneById("Eastern Standard Time");
DateTime utcStartingDate = TimeZoneInfo.ConvertTimeToUtc(startingDate, easternZone);
DateTime utcEndingDate = TimeZoneInfo.ConvertTimeToUtc(endingDate, easternZone);
var firstQ = from p in db.PowerStringHistorys
from s in db.PowerCombinerHistorys
join t in db.PowerStrings on p.string_id equals t.id
join u in db.PowerCombiners on t.combiner_id equals u.id
where p.recordTime == s.recordTime
where p.recordTime >= utcStartingDate
where p.recordTime <= utcEndingDate
select new
{
Combiner = u.id,
StringId = p.string_id,
Current = p.current,
RecordTime = p.recordTime,
Voltage = s.voltage
};
var groups = firstQ.ToList().GroupBy(q => new
{
q.Combiner,
q.StringId,
Date = q.RecordTime.Date,
Hour = q.RecordTime.Hour
});
List<StringKwh> stringGroupedKwhlist = new List<StringKwh>();
foreach (var group in groups)
{
stringGroupedKwhlist.Add(new StringKwh(
group.Key.Combiner,
group.Key.StringId,
new DateTime(group.Key.Date.Year, group.Key.Date.Month, group.Key.Date.Day, group.Key.Hour, 0, 0),
group.Average(g => g.Voltage * g.Current) / 1000d
));
}
var groupCombiner = stringGroupedKwhlist.GroupBy(q => new { q.CombinerID });
double myTotalKwh = 0;
foreach (var combinerGroup in groupCombiner)
{
myTotalKwh = Math.Round(combinerGroup.Sum(g => g.KWH), 3);
}
ViewBag.LifeTimeGeneration = myTotalKwh;
ViewBag.myUTCStartDate = utcStartingDate;
ViewBag.myUTCEndDate = utcEndingDate;
return View();
}
public ActionResult About()
{
return View();
}
}
}
这可能会让你开始：
// Group by combiner ID, date, and hour
var groups = FirstQ.ToList()
.GroupBy(q => new
{ q.Combiner, Date = q.RecordTime.Date, Hour = q.RecordTime.Hour });
foreach (var group in groups)
{
var combinerId = group.Key.Combiner;
var interval = new DateTime(group.Key.Date.Year, group.Key.Date.Month, group.Key.Date.Day, group.Key.Hour, 0, 0);
// power = voltage * current
var kwh = group.Average(g => g.Voltage * g.Current) / 1000d;
}
尝试用group by替换select，如下所示：
TimeZoneInfo easternZone = TimeZoneInfo.FindSystemTimeZoneById("Eastern Standard Time");
DateTime UTC_StartingDate = TimeZoneInfo.ConvertTimeToUtc(StartingDate, easternZone);
DateTime UTC_EndingDate = TimeZoneInfo.ConvertTimeToUtc(EndingDate, easternZone);
var FirstQ = from p in db.PowerStringHistorys
join t in db.PowerStrings on p.string_id equals t.id
join u in db.PowerCombiners on t.combiner_id equals u.id
join s in db.PowerCombinerHistorys on p.recordTime equals s.recordTime
where p.recordTime >= UTC_StartingDate
where p.recordTime <= UTC_EndingDate
group new
{
Combiner = u.id,
Current = p.current,
RecordTime = p.recordTime,
Voltage = s.voltage
}
by new
{
Combiner = u.id,
Date = p.RecordTime.Date,
Hour = p.RecordTime.Hour
};