c# - LINQ/Lambda 表达式:加入列表并使用给定公式查找数据的平均计数
问题描述
我有 500 多条EventData记录的列表。EventData的模型如下所示
public class EventData
{
public int preEventId { get; set; }
public int empNum { get; set; }
public int EventId { get; set; }
public DateTime CreateDate { get; set; }
public string UserId { get; set; }
}
这里的 EventId 可以是 1、2、3、4 和 5(枚举)。从这个列表中,我想过滤一个月中所有星期的记录,然后计算平均值。注意:weekList是当月星期一的列表。
(EventId = 1 的记录数)/(EventId = 2 或 5 和 EventId = 1 的 empNum 的记录数(CreateDate 5 天前或更新))
我所做的是: -
int calledCount = lstEventData?.Where(e => e.EventId == 1 &&
e.CreateDate >= weekList.ToList()[week] &&
e.CreateDate <= weekList.ToList()[week].AddDays(4)).Count() ?? 0;
int totalCount = (lstEventData?
.Where(e => (e.EventCd == 2 || e.EventCd == 5) &&
e.CreateDate >= weekList.ToList()[week] &&
e.CreateDate <= weekList.ToList()[week].AddDays(4))
.GroupBy(e => e.empNum)
.Select(x => x.First())
.Count()) ?? 0;
int avgCalls = Convert.ToDecimal(calledCount) / Convert.ToDecimal(totalCount);
相同的示例 SQL 结构:
SELECT * FROM CalledList c INNER JOIN
Events e
ON (c.empNum = e.empNum AND c.EventId IN(2,5)
AND c.CreateDate > e.CreateDate - 5
AND c.EventId = 1
我怎样才能实现我的实际目标?
解决方案
根据我从您的代码和解释中可以推断出的内容,我认为这就是您所追求的:
// first, to reduce parsing your weekList EVERY time, get a reference to your start/end dates
var wkStart = weekList.ToList()[week];
// This may not quite work since if your weekstart is Monday midnight,
// adding 4 days is Friday midnight, so 12:01 AM Friday won't fall into your range.
// I'd recommend doing AddDays(5), and using less than (<), not less than or equal (<=)
var wkEnd = wkStart.AddDays(5);
// using consts to avoid "magic numbers"
const int CALLED_LIST = 1;
const int OTHER_EVENTS = 2;
// Then get all of the events in that period that match the EventIds you want,
// just so your followup queries are against a smaller set
var events =
lstEventData?
.Where(e => (
(e.EventId == 1 && e.CreateDate >= wkStart && e.CreateDate <= wkEnd) // event ID 1 within date range
|| e.EventId == 2 || e.EventId == 5 // or event ID 2 or 5, ingoring date range for the moment
))
// Create a lookup to split by event ID to have two separate lists to join below
.ToLookup(e => e.EventId == 1 ? CALLED_LIST : OTHER_EVENTS);
var calledList = events[CALLED_LIST].ToList();
var otherEvents = events[OTHER_EVENTS].ToList();
if ((calledList?.Count ?? 0) == 0)
{
// you need to handle empty or null to
// avoid a DivdeByZero error below.
return;
}
// by handling the null/empty above, you no longer need to handle it everywhere below;
var joinedResults =
calledList.Join(otherEvents,
c => c.empNum,
e => e.empNum,
(c,e) => new {Parent = c, Child = e})
.Where(joined => joined.Parent.CreateDate > joined.Child.CreateDate.AddDays(-5));
// From here, you'll need to figure out what average you're trying to get.
// I made a note in the comments that I'm unclear what average you're trying to calculate.
}
推荐阅读
- grafana - 如何支持所有仪表板通用的 Grafana 中的全局变量?
- javascript - 基于 v-for 选择的选项返回数组
- ag-grid - Svelte 中的 ag-grid 中的 cellRenderer 如何将行高动态设置为 CSV 字符串中的项目数?
- python - How to open hidden app on Windows with Python?
- swift - 如何在swift ios中的特定位置建立这个凹曲线到uiview
- python - 消息:元素不可交互。Xpath 正确
- matlab - 经过一些处理后,在循环中绘制许多不同的图像
- c++ - std::setprecision 设置有效数字的数量。如何使用 iomanip 设置精度?
- database - TerminusDB 可以用作文档数据库吗?
- python - 使用 Selenium 抓取 JS 页面