计算递归SQL服务器CTE中运行的平均值

I need a bit of code which calculates the current average row by row bases starting with receipt 1.

我需要一段代码，以收据1开始，按行计算当前的平均行数。

I have the below table which contains data for purchase quantity, price etc. I need to calculate the running average (current average) for each row starting from receipt 1. Receipt one is always easy because the previous price doesn't impact its average.

我有下面的表格，包含了购买数量、价格等数据。我需要计算从收到1开始的每一行的运行平均(当前平均)。收据一总是很容易，因为以前的价格不会影响它的平均价格。

Once you have receipt 1 average (£75) you can use this to caluclate the subsequent rows.

一旦你收到1平均(£75)您可以使用它来caluclate随后行。

I have manually calculated receipt 2 to be "£79.4858". I have been advised the best way it to use CTE recursive.

我已经手动计算收据2“£79.4858”。有人建议我使用CTE递归的最佳方式。

CREATE TABLE [dbo].[X]
(
    [Item No_] [nvarchar](20) NOT NULL,
    [ReceiptNo] [bigint] NULL,
    [Sold] [decimal](38, 20) NULL,
    [InventoryBalance] [decimal](38, 20) NOT NULL,
    [PurchaseQty] [decimal](38, 20) NULL,
    [IntakeSellingPrice] [decimal](38, 20) NULL,
    [NewBalance] [decimal](38, 20) NULL,
    [CurrentAverage] [numeric](2, 2) NOT NULL
) ON [PRIMARY]
GO

INSERT [dbo].[X] ([Item No_], [ReceiptNo], [Sold], [InventoryBalance], [PurchaseQty], [IntakeSellingPrice], [NewBalance], [CurrentAverage]) 
VALUES (N'2000045', 1, CAST(0.00000000000000000000 AS Decimal(38, 20)), CAST(0.00000000000000000000 AS Decimal(38, 20)), CAST(500.00000000000000000000 AS Decimal(38, 20)), CAST(75.00000000000000000000 AS Decimal(38, 20)), CAST(500.00000000000000000000 AS Decimal(38, 20)), CAST(0.00 AS Numeric(2, 2))),
       (N'2000045', 2, CAST(250.00000000000000000000 AS Decimal(38, 20)), CAST(250.00000000000000000000 AS Decimal(38, 20)), CAST(2181.00000000000000000000 AS Decimal(38, 20)), CAST(80.00000000000000000000 AS Decimal(38, 20)), CAST(2431.00000000000000000000 AS Decimal(38, 20)), CAST(0.00 AS Numeric(2, 2))),
       (N'2000045', 3, CAST(316.00000000000000000000 AS Decimal(38, 20)), CAST(2115.00000000000000000000 AS Decimal(38, 20)), CAST(10.00000000000000000000 AS Decimal(38, 20)), CAST(80.00000000000000000000 AS Decimal(38, 20)), CAST(2125.00000000000000000000 AS Decimal(38, 20)), CAST(0.00 AS Numeric(2, 2)))

3 个解决方案

#1

I am not sure but you are looking running avg like as below with the help of lag() function

我不确定，但是在lag()函数的帮助下，您正在寻找如下所示的运行avg

select [Item No_],ReceiptNo,((PurchaseQty * IntakeSellingPrice) +  
           (InventoryBalance * isnull(lag(IntakeSellingPrice) over (order by [Item No_]), IntakeSellingPrice)
           ))/NewBalance [RunningAVG]
from X

Result :

结果:

Item No_    ReceiptNo   RunningAVG
2000045     1           75.000000
2000045     2           79.485808
2000045     3           80.000000

#2

Just use the cumulative average:

用累积平均值:

select x.*,
       avg(?) over (order by [Item No_]) as running_avg
from dbo.x;

The ? is for the column you care about. I can't tell which you want the running average for.

的吗?是你关心的那一栏。我不知道你想要的平均运行时间是多少。

#3

It should be something like this:

应该是这样的:

SELECT *
      ,AVG([IntakeSellingPrice]) OVER (ORDER BY [Item No_] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM [dbo].[X]

Using a OVER clause and the ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW will perform the aggregate over the all columns from the start to current row, where rows are ordered by the ORDER BY clause.

使用OVER子句和*前行和当前行之间的行将对从开始到当前行的所有列执行聚合，其中的行由ORDER by子句排序。

#1