# 如何在最后4行数据上执行聚合功能?[英] How to perform aggregate function on last 4 rows of data?

### 问题描述

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

```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**
```

thx！

## 推荐答案

```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)
};
```

## 其他推荐答案

```select  yt1.*
,       hist.four_week_min
from    YourTable yt1
outer apply
(
select  min(col1) as four_week_min
from    YourTable yt2
where   yt2.dt between dateadd(wk, -3, yt1.dt) and yt1.dt
) hist
```

sql小提琴.

## 其他推荐答案

```var runningMins =   from weekNum in data
select new
{
FranchiseId = weekNum.FranchiseId,
WeekEnding = weekNum.WeekEnding,
Sales = weekNum.Sales,
LastThreeWeeks = data.OrderByDescending( x => x.WeekEnding )
.Where( x => x.WeekEnding <= weekNum.WeekEnding )
.Take( 4 )
.Min( x => x.Sales )
};
```

sql查询将返回当前的最小值和前三个查询，无论日期是否恰好相距三周:

:

```With RnkItems As
(
Select DateVal, Sales
, Row_Number() Over ( Order By DateVal ) As Rnk
From SourceData
)
Select *
, (
Select Min(Sales)
From  RnkItems As R1
Where R1.Rnk Between R.Rnk - 3 And R.Rnk
)
From RnkItems R
Order By 1
```

### 问题描述

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**
```

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

thx!

## 推荐答案

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)
};
```

## 其他推荐答案

Consider using outer apply:

```select  yt1.*
,       hist.four_week_min
from    YourTable yt1
outer apply
(
select  min(col1) as four_week_min
from    YourTable yt2
where   yt2.dt between dateadd(wk, -3, yt1.dt) and yt1.dt
) hist
```

Working example at SQL Fiddle.

## 其他推荐答案

```var runningMins =   from weekNum in data
select new
{
FranchiseId = weekNum.FranchiseId,
WeekEnding = weekNum.WeekEnding,
Sales = weekNum.Sales,
LastThreeWeeks = data.OrderByDescending( x => x.WeekEnding )
.Where( x => x.WeekEnding <= weekNum.WeekEnding )
.Take( 4 )
.Min( x => x.Sales )
};
```

SQL Query that will return minimum of the current and the three previous regardless of whether the dates are exactly three weeks apart:

```With RnkItems As
(
Select DateVal, Sales
, Row_Number() Over ( Order By DateVal ) As Rnk
From SourceData
)
Select *
, (
Select Min(Sales)
From  RnkItems As R1
Where R1.Rnk Between R.Rnk - 3 And R.Rnk
)
From RnkItems R
Order By 1
```

SQL Fiddle version