在linq中找到“最有进步”

The following code is what i used to identify the Top 10 earning items

以下代码是我用来识别前十名收入项目的代码

var top = (from m in db.Stats
           where m.Item.AccountID == AccountID
           && m.DateTime >= month
           && m.DateTime < month.AddMonths(1)
           group m by m.Item into g
           orderby g.Sum(p => p.Earnings) descending
           select g.Key).Take(10).ToArray();

I need to identify which item has improved the most (%) when comparing 2 different months. The Data available is only daily totals. I might need to filter out items which are below a certain number of 'Visitors' i.e. where

在比较2个不同月份时,我需要确定哪个项目的改进率最高(%)。可用数据仅为每日总计。我可能需要过滤出低于一定数量“访客”的项目,即在哪里

m.Visitors >= 20

What is the best way to do this in Linq-Sql?

在Linq-Sql中执行此操作的最佳方法是什么?

1 个解决方案

#1

If you split the query into two distinct groups, current month (or time period) and previous month (or time period) it should be easier to manage. For clarity I'm using a private class to keep the results we're interested in. You may be able to use an existing class instead. Or, the compiler might even be smart enough to realize that the properties are identical and thus generate the same anonymous class for both queries. In any event...

如果将查询拆分为两个不同的组,即当前月(或时间段)和上个月(或时间段),则应该更容易管理。为了清楚起见,我使用私有类来保持我们感兴趣的结果。您可以使用现有的类来代替。或者,编译器甚至可能足够聪明地意识到属性是相同的,因此为两个查询生成相同的匿名类。在任何情况下...

private class C {
    public object Key { get; set; }

    public object Earnings { get; set; }
}

var thisMonth = (from m in db.Stats
                 where m.Item.AccountID == AccountID
                 && m.DateTime.Month == month
                 group m by m.Item into g
                 select new C { g.Key, g.Earnings });

var lastMonth = (from m in db.Stats
                 where m.Item.AccountID == AccountID
                 && m.DateTime.Month == month.AddMonths(-1)
                 group m by m.Item into g
                 select new C { g.Key, g.Earnings });

From here it's a matter of combining the results to calculate percent gain or loss between the two sets. Once that's done you can sort by highest gains to get your top n.

从这里开始,将结果组合起来计算两组之间的增益或损失百分比。一旦完成,你可以按最高收益排序,以获得你的前n。