Linq-to-SQL GroupBy:如何计算百分比列
本文关键字:计算 百分比 GroupBy 何计算 Linq-to-SQL | 更新日期: 2023-09-27 18:30:32
>我有一个事件的数据库表。每个事件都有一个类别和一个日期/时间。
(实际上事件具有子类别,这些子类别包含在类别和各种其他属性中,在此简化示例中省略)
我想生成一个列表,其中包含每个小时和类别的条目,列出事件的数量及其百分比。
date | category | number of events | percentage
----------------------------------------------------------
2012-01-01 1:00 | cat-1 | 10 | 50%
2012-01-01 1:00 | cat-2 | 10 | 50%
2012-01-01 2:00 | cat-1 | 1 | 10%
2012-01-01 2:00 | cat-2 | 9 | 90%
2012-01-01 3:00 | cat-1 | 3 | 100%
...
我在有效计算百分比时遇到问题。这是我到目前为止所拥有的:
var results = datacontext.events.
groupBy(e=>new
{ // group by category and hour
category = e.category,
datetime = new DateTime (
e.datetime.Year, e.datetime.Month, e.datetime.Day,
e.datetime.Hour, 0, 0 )
// use date+hour for grouping, ignore minutes/seconds
}).
select(group => new
{
category = group.Key.category,
datetime = group.Key.datetime,
numberOfEvents = group.count(),
percentage = group.count() / ["total number of events for this hour"] * 100.0
}
如何获取 [此小时事件总数] 的值,理想情况下以优雅而高效的方式?
这是我想到的:
var results = from events in datacontext.events
let date = new DateTime(
events.datetime.Year, events.datetime.Month, events.datetime.Day,
events.datetime.Hour, 0, 0)
let total = datacontext.events.Where(x => x.datetime.Date == date.Date && x.datetime.Hour == date.Hour).Count()
group events by new
{ // group by category and hour
category = events.category,
datetime = date,
total = total
} into e
select new
{
category = e.Key.category,
day = e.Key.datetime.Day,
numberOfEvents = e.Count(),
percentage = (float)e.Count() / (float)e.Key.total
};
两个重点:
- 使用浮点数表示百分比
- 使用
let
计算Date
的总计
您必须先按天分组,然后从那里选择总计......像这样:
var resultsPerDay = datacontext.events.groupBy(e=>new {
datetime = new DateTime (
e.datetime.Year, e.datetime.Month, e.datetime.Day,
e.datetime.Hour, 0, 0 )
}
}).select(group=>new {
datetime = group.Key.datetime,
totalForADay = group.Key.count()
});
var results = datacontext.events.
groupBy(e=>new
{ // group by category and hour
category = e.category,
datetime = new DateTime (
e.datetime.Year, e.datetime.Month, e.datetime.Day,
e.datetime.Hour, 0, 0 )
// use date+hour for grouping, ignore minutes/seconds
}).
select(group => new
{
category = group.Key.category,
datetime = group.Key.datetime,
numberOfEvents = group.count(),
percentage = 100 * group.count() / resultsPerDay.where(p=>p.datetime == group.Key.datetime).select(p=>p.totalForADay).SingleOrDefault()
}
不过,不确定这是否是最有效的方法。另外,不确定语法是否好,因为我没有通过编译器将其放在一起,但是您明白了。