如何对最后4行数据执行汇总功能? [英] How to perform aggregate function on last 4 rows of data?

问题描述

我有一张下面的桌子的桌子.

I've got a table off the following model.

public class WeeklyNums
{
   public int FranchiseId { get; set; }
   public DateTime WeekEnding { get; set; }
   public decimal Sales { get; set; }
}

我需要第四列来计算本周和前三周的最小值.所以输出看起来像这样.

I need a fourth column that calculates the minimum for this week and the previous three weeks. So the output would look like this.

1   7-Jan   $1  
1   14-Jan  $2  
1   21-Jan  $3  
1   28-Jan  $4  **1**  
1   4-Feb   $4  **2**  
1   11-Feb  $6  **3**  
1   18-Feb  $4  **4**  
1   25-Feb  $8  **4**  
1   3-Mar   $7  **4**  

我什至不知道从哪里开始.即使是一些在SQL中解决它的帮助也会很有帮助.

I have no idea where to even start. Even some help with solving it in SQL would be helpful.

thx！

推荐答案

我知道我来晚了，但是这里是linq版本:

I know I'm too late, but here's the linq version:

var result = from w1 in db.Table
             from w2 in db.Table.Where(x => x.WeekEnding >= w1.WeekEnding.AddDays(-28))
             select new
             {
                 FranchiseId = w1.FranchiseId,
                 WeekEnding = w1.WeekEnding,
                 Sales = w1.Sales,
                 SalesMin = w2.Min(x => x.Sales)
             };

这篇关于如何对最后4行数据执行汇总功能?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！