我正在開發一個項目，我想用日志數據制作圖表。所以我已經將日志數據加載到我的 SQL 服務器中，并找到了生成我需要的結果的查詢。每個日志條目包含每 2 秒的測量值。SELECT '7am' as timeslot, MAX(Odometer_km) - MIN(Odometer_km) as distance, AVG(Tanktemperature_C) tanktemp, AVG(Pressure_bar) avgBar FROM [FC66294] where [epochasdate] >= '2018-05-03 07:00:00' AND [epochasdate] <= '2018-05-03 08:00:00'UNION SELECT '8am' as timeslot, MAX(Odometer_km) - MIN(Odometer_km) as distance, AVG(Tanktemperature_C) tanktemp, AVG(Pressure_bar) avgBar FROM [FC66294] where [epochasdate] >= '2018-05-03 08:00:00' AND [epochasdate] <= '2018-05-03 09:00:00'UNION SELECT '9am'as timeslot, MAX(Odometer_km) - MIN(Odometer_km) as distance, AVG(Tanktemperature_C) tanktemp, AVG(Pressure_bar) avgBar FROM [FC66294] where [epochasdate] >= '2018-05-03 09:00:00' AND [epochasdate] <= '2018-05-03 10:00:00'此示例顯示了三個時隙以及該時隙上的一些計算值?，F在，我嘗試使用 linq 查詢在 C# 中重現此 SQL 結果，因為我想在圖表中顯示結果。我想出了以下幾點：using (var entity = new DBEntity.Entities()) { var startDatetime = new DateTime(2018, 05, 03, 7, 0, 0); var endDatetime = new DateTime(2018, 05, 03, 8, 0, 0); var firstResult = entity.M_FC66294 .Where(x => x.epochasdate >= System.Data.Entity.DbFunctions.AddHours(startDatetime, 0) && x.epochasdate <= System.Data.Entity.DbFunctions.AddHours(endDatetime, 0)) .GroupBy(x => 1) .Select(g => new { TankTemp = g.Average(x => x.Tanktemperature_C), Bar = g.Average(x => x.Pressure_bar), MinOdo = g.Min(x => x.Odometer_km), MaxOdo = g.Max(x => x.Odometer_km) });現在變量 test1 實際上包含了我需要的 2 行數據。但我希望變量 test2 包含 24 行（1 天）的數據。但它不包含任何行。那么，如何連接多個結果變量，然后在 FinalQuery 上運行 ToList() ？編輯：或者如何在循環內使用 concat？
1 回答
慕斯709654
TA貢獻1840條經驗 獲得超5個贊
您在詢問連接，但您似乎使用它，所以我不太確定您需要什么，但是
如果您希望變量中的 24 條記錄按小時分組，您只需按數據的小時運行分組即可。像這樣
var groupedbyhour = entity.M_FC66294.GroupBy(entry => entry.epochasdate.Date.AddHours(entry.epochasdate.Hour))//group by hour
.Select(HourGroup => new
{
TankTemp = HourGroup.Average(x => x.Tanktemperature_C),
Bar = HourGroup.Average(x => x.Pressure_bar),
MinOdo = HourGroup.Min(x => x.Odometer_km),
MaxOdo = HourGroup.Max(x => x.Odometer_km),
epochasdate = HourGroup.Max(x => x.epochasdate.Date.AddHours(x.epochasdate.Hour))
}).ToList();
請記住，紀元現在將是幾小時組，因此例如 2019/07/30 - 7:00 和 2019/07/30 - 8:00
更新
這是我的完整測試，以便您可以復制。
public class entry
{
public int Odometer_km;
public DateTime epochasdate;
public int Tanktemperature_C;
public int Pressure_bar;
}
class Program
{
static void Main(string[] args)
{
List<entry> entries = new List<entry>(){
new entry() {epochasdate = new DateTime(2019, 07, 29, 7, 2, 1), Odometer_km = 10, Pressure_bar = 1, Tanktemperature_C = 23},
new entry() { epochasdate = new DateTime(2019, 07, 29, 7, 1, 1), Odometer_km = 20, Pressure_bar = 2, Tanktemperature_C = 25 },
new entry() { epochasdate = new DateTime(2019, 07, 29, 7, 3, 1), Odometer_km = 22, Pressure_bar = 2, Tanktemperature_C = 24 },
new entry() { epochasdate = new DateTime(2019, 07, 29, 7, 22, 1), Odometer_km = 25, Pressure_bar = 4, Tanktemperature_C = 22 },
new entry() { epochasdate = new DateTime(2019, 07, 29, 8, 24, 1), Odometer_km = 36, Pressure_bar = 2, Tanktemperature_C = 20 },
new entry() { epochasdate = new DateTime(2019, 07, 29, 8, 21, 1), Odometer_km = 42, Pressure_bar = 3, Tanktemperature_C = 19 },
new entry() { epochasdate = new DateTime(2019, 07, 29, 8, 29, 1), Odometer_km = 50, Pressure_bar = 2, Tanktemperature_C = 21 },
new entry() { epochasdate = new DateTime(2019, 07, 29, 8, 22, 1), Odometer_km = 55, Pressure_bar = 4, Tanktemperature_C = 20 },
new entry() { epochasdate = new DateTime(2019, 07, 29, 8, 52, 1), Odometer_km = 62, Pressure_bar = 2, Tanktemperature_C = 19 },
new entry() { epochasdate = new DateTime(2019, 07, 29, 9, 43, 1), Odometer_km = 80, Pressure_bar = 3, Tanktemperature_C = 17 },
new entry() { epochasdate = new DateTime(2019, 07, 29, 9, 22, 1), Odometer_km = 120, Pressure_bar = 1, Tanktemperature_C = 18 },
new entry() { epochasdate = new DateTime(2019, 07, 29, 9, 12, 1), Odometer_km = 140, Pressure_bar = 3, Tanktemperature_C = 16 },
new entry() { epochasdate = new DateTime(2019, 07, 29, 9, 31, 1), Odometer_km = 156, Pressure_bar = 2, Tanktemperature_C = 17 }
};
var groupedbyhour = entries.GroupBy(entry => entry.epochasdate.Date.AddHours(entry.epochasdate.Hour))//group by hour
.Select(HourGroup => new
{
TankTemp = HourGroup.Average(x => x.Tanktemperature_C),
Bar = HourGroup.Average(x => x.Pressure_bar),
MinOdo = HourGroup.Min(x => x.Odometer_km),
MaxOdo = HourGroup.Max(x => x.Odometer_km),
MeasureTime = HourGroup.Max(x => x.epochasdate.Date.AddHours(x.epochasdate.Hour))
}).ToList();
}
}
如果您的 epochasdate 類型不是日期時間并且由于某種原因無法分組，請使用 .addhour 并向值添加 0 小時，它應該返回您的 groupby 功能可以理解的 DateTime 結果
- 1 回答
- 0 關注
- 148 瀏覽
添加回答
舉報
0/150
提交
取消