使用“ Row_Number”功能和WHILE循环创建滚动平均值报告

介绍 ( Introduction )

A few days ago I received an email from a gentleman in the healthcare industry. He had read an article that I had previously published a few years back on a well known SQL Server website. Based on the original article, he requested a few ideas on how to expand the sample code to do a rolling three-month revenue summary, in addition to a rolling three-month revenue average.

几天前，我收到了医疗行业一位绅士的电子邮件。 他读了几年前我在一个著名SQL Server网站上发表的文章。 在原始文章的基础上，他提出了一些关于如何扩展示例代码以进行三个月收入汇总的想法，以及三个月收入平均值。

In today’s get together, we shall be looking at doing just this!

在今天的聚会中，我们将考虑做到这一点！

入门 ( Getting Started )

As a starting point, let us have a quick look at the sample data that the gentleman provided.

首先，让我们快速看一下这位先生提供的样本数据。

Column A contains a six character field containing the year and month of the revenue. We shall utilize this field as a sorting field. Column B contains the month name and Column C the number of articles sold that month.

列A包含六个字符的字段，其中包含收入的年份和月份。 我们将利用该字段作为排序字段。 B列包含月份名称，C列包含该月售出商品的数量。

Columns D and E contain financial data. We shall be looking exclusively at Column D.

D和E列包含财务数据。 我们将仅关注D列。

Columns F and G are of most interest to us as they contain the “Rolling 3 Month Total” and “Rolling 3 Month Average” that the gentleman expected to be calculated at runtime.

F和G列是我们最感兴趣的列，因为它们包含了绅士期望在运行时计算的“滚动3个月总计”和“滚动3个月平均值”。

We now import this data into the Rolling3 database. The imported data may be seen immediately below and the table structure may be seen in Addenda 2.

现在，我们将这些数据导入Rolling3数据库。 导入的数据可以在下面立即看到，表结构可以在附录2中看到。

The astute reader will note that neither the summary nor the average figures from the spreadsheet have been imported into the data table. Once again, these will be calculated at runtime.

精明的读者会注意到，电子表格的摘要或平均数字都未导入到数据表中。 再次，这些将在运行时计算。

Now that we have a high-level understanding of the data, let us get busy producing a query to produce the desired results.

现在，我们已经对数据有了一个高级的了解，现在让我们开始忙于生成查询以产生所需的结果。

We begin by opening SQL Server Management Studio and open a new query. As we shall be utilizing two temporary tables, we need to develop some helpful code that will delete these two temporary tables prior to each run (see the screenshot below). In reality, the user will probably utilize a stored procedure in which case this code is not necessary and must be commented out prior to creating the stored procedure.

我们首先打开SQL Server Management Studio，然后打开一个新查询。 由于我们将利用两个临时表，因此我们需要开发一些有用的代码，这些代码将在每次运行之前删除这两个临时表（请参见下面的屏幕截图）。 实际上，用户可能会使用存储过程，在这种情况下，此代码不是必需的，必须在创建存储过程之前将其注释掉。

The screenshot above shows the code to delete any existing local temporary tables with the names #rawdata1 and #rawdata2. This will ensure that each time we run the query that the two temporary tables do not exist and that we are able to create them at runtime. Should the table already exist as a result of a previous run of this query (and do not utilize this code) then the query execution will terminate with an error condition. The complete code listing may be seen in Addenda 1.

上面的屏幕截图显示了删除名称为＃rawdata1和＃rawdata2的任何现有本地临时表的代码。 这将确保每次我们运行查询时，两个临时表都不存在，并且能够在运行时创建它们。 如果该表由于先前的查询运行而已经存在（并且不使用此代码），则查询执行将以错误条件终止。 完整的代码清单可以在附录1中看到。

Additionally, we declare two variables: @Max and @Kount. We set the value of @Kount to 1.

此外，我们声明两个变量：@Max和@Kount。 我们将@Kount的值设置为1。

We are now in a position to pull all the relevant data from our table “Rolling31”

现在，我们可以从“ Rolling31”表中提取所有相关数据

The reader will note that in the screenshot above, that we have created a row number for each record that we shall be pulling and that the data has been sorted by increasing “YearMth”. The purpose of the “row” will become apparent in a few minutes.

读者会注意到，在上面的屏幕截图中，我们为将要提取的每条记录创建了一个行号，并且通过增加“ YearMth”对数据进行了排序。 几分钟后，“行”的目的将变得显而易见。

IT SHOULD ALSO BE NOTED THAT THE SOURCE DATA WAS INSERTED INTO THE TABLE, SORTED BY “YEARMTH” IN ASCENDING ORDER. If this is not the case with your data, then you will probably have to do a pre-sort step on your data.

还 应该 指出的是，源数据插入到表 ， 排序方式“YEARMTH”升序排列。 如果您的数据不是这种情况，那么您可能必须对数据进行预排序。

The results for this portion of this query may be seen in the screenshot below:

在下面的屏幕快照中可以看到此查询的此部分的结果：

We now set the value of “@Max” based upon the maximum row number within the temporary table #rawdata1.

现在，我们根据临时表＃rawdata1中的最大行数设置“ @Max”的值。

 
  set @Max = (Select Max(row) from #rawdata1)
 

In our case it is 9.

在我们的例子中是9。

As may be seen in the screenshot above, we have done something a bit unorthodox by creating a temporary table called #rawdata2. Creating the temporary table at this point requires a bit of explanation.

从上面的屏幕快照中可以看出，我们通过创建名为＃rawdata2的临时表做了一些不合常规的事情。 此时创建临时表需要一些解释。

As we shall be calculating the running three month revenue totals and the running three month averages for each “YearMth” we need to process the calculations and insert them into the temporary table #rawdata2 from within the “While Loop”.

正如我们应计算连续3个月份之收益总额与投放3个月平均值为每一个“YearMth”我们需要处理的计算，并将其插入到临时表＃rawdata2 从 “While循环” 中 。

@Max (which we calculated above), will be the maximum number of times that we iterate through the while loop. The loop code may be seen below:

@Max（我们上面计算过的）将是我们在while循环中迭代的最大次数。 循环代码如下所示：

 
while (@Kount <= @max)
 Begin 
   Insert into #rawdata2
     select @Kount as Row , sum(Revenue1) as Rolling , Avg(Revenue1) as
     RollingAvg from #rawdata1
   where row between @Kount - 2  and @Kount  
   set @Kount = @Kount + 1
  end
 

The “eagle-eyed” reader will note that each time we iterate through the loop that we insert the current record into the temporary table #rawdata2 (using an “Insert into” clause).

“老鹰眼”的读者会注意到，每次迭代循环时，我们都会将当前记录插入临时表＃rawdata2中（使用“ Insert into”子句）。

We select the row number (the importance of which we shall see in a few minutes) , the sum of the revenue from the prior two months PLUS the“current month” (for this current iteration) and the average of the revenue for the same two prior months and include “current month” (for this current iteration). This being achieved via the predicate:

我们选择行号（我们将在几分钟内显示其重要性），前两个月的收入之和加上“当前月份”（针对当前迭代）以及该收入的平均值前两个月，包括“当前月份”（针对当前迭代）。 这是通过谓词实现的：

 
  where row between @Kount - 2  and @Kount
 

@Kount is then incremented by 1. Once @Kount is greater than @max, we break out of the loop and processing continues.

然后@Kount递增1。一旦@Kount大于@max，我们就会跳出循环，继续进行处理。

The results of the loop processing may be seen below:

循环处理的结果如下所示：

Our last task is to join the first temporary table (#rawdata1) to the second temporary table (#rawdata2), performing an inner join on the “Row” column (see below).

我们的最后一个任务是将第一个临时表（＃rawdata1）联接到第二个临时表（＃rawdata2），在“行”列上执行内部联接（请参见下文）。

Enlarging a portion of the screen shot above, we note that the results for the rolling three month revenue and three month average are the same as in the original spreadsheet (see below).

放大上面的一部分屏幕截图，我们注意到滚动三个月的收入和三个月的平均值的结果与原始电子表格中的结果相同（请参见下文）。

This said, we have our final result and we have achieved our end goal.

也就是说，我们有最终结果，我们已经实现了最终目标。

结论 ( Conclusions )

Necessity is definitely the “mother of invention” and in this case what began as an interesting challenge, proved to be an easier than one would have expected. We have worked with the “Row_Number” function and through the usage of a while loop, we have been able to reach our end goal.

绝对必要性是“发明之母”，在这种情况下，起初是一个有趣的挑战，事实证明，这比人们原先所期望的要容易。 我们已经使用了“ Row_Number”功能，并且通过使用while循环，我们已经达到了最终目标。

My challenge to you is to try to utilize the same logic and to create a similar ‘routine’ using a cursor as I have utilized in many of my previous articles on SQL Shack.

我面临的挑战是尝试利用相同的逻辑并使用游标创建类似的“例程”，就像我在以前有关SQL Shack的许多文章中所使用的那样。

Should you have any queries or concerns, please do feel free to contact me.

如果您有任何疑问或疑虑，请随时与我联系。

In the interim, happy programming.

在此期间，编程愉快。

附录1：查询 ( Addenda 1: The query )

 
Use Rolling3
GO
 
IF OBJECT_ID(N'tempdb..#rawdata1') IS NOT NULL
BEGIN
     DROP TABLE #rawdata1
END
IF OBJECT_ID(N'tempdb..#rawdata2') IS NOT NULL
BEGIN
     DROP TABLE #rawdata2
END
 
go
 
declare @Max as int
declare @Kount as int
Set @Kount = 1
SELECT row_number() Over (order by YearMth) as row,[YearMth] 
      ,[Monthee]
      ,[ItemCount]
      , Revenue1 
      ,[Revenue2]
     into #rawdata1
  FROM [Rolling3].[dbo].[Rolling31]
  order by YearMth 
set @Max = (Select Max(row) from #rawdata1)
 
Create Table #Rawdata2
  (
  [Row] int,
  Rolling int,
  RollingAvg decimal(15,2)
  )
  while (@kount <= @max)
  Begin 
   Insert into #rawdata2
   select @Kount as Row , sum(Revenue1) as Rolling , Avg(Revenue1) as RollingAvg from #rawdata1
   where row between @Kount - 2  and @Kount  
   set @Kount = @Kount + 1
  end
  select rd1.row, rd1.yearMth,Rd1.Monthee,Rd1.Revenue1,Rd1.Revenue2,Rd2.rolling,rd2.RollingAvg from #rawdata2 rd2 
  inner join #rawdata1 rd1
  on rd1.row = rd2.row
 

附录2 ( Addenda 2 )

 
USE [Rolling3]
GO
 
/****** Object:  Table [dbo].[Rolling31]    Script Date: 05/21/2015 14:51:10 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
SET ANSI_PADDING ON
GO
 
CREATE TABLE [dbo].[Rolling31](
	[YearMth] [varchar](6) NULL,
	[Monthee] [varchar](20) NULL,
	[ItemCount] [int] NULL,
	[Revenue1] [int] NULL,
	[Revenue2] [int] NULL
) ON [PRIMARY]
 
GO
 
SET ANSI_PADDING OFF
GO
 

翻译自: https://www.sqlshack.com/using-row-number-function-and-while-loop-create-rolling-average-report/