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

本文是小编为大家收集整理的关于如何在最后4行数据上执行聚合功能?的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我有一张桌子的桌子.

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

我不知道从哪里开始.即使在SQL中解决它也有帮助.

thx!

推荐答案

我知道我为时已晚,但这是Linq版本:

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

其他推荐答案

考虑使用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

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

sql小提琴版本

本文地址:https://www.itbaoku.cn/post/1557130.html

问题描述

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