SQL-计算预测平均值 [英] SQL - calculate forecast average

问题描述

我正在尝试根据前三个月的实际情况或预测来计算销售预测.

I'm trying to calculate a forecast of sales based on the 3 previous months which either can be actuals or forecast.

company_id    Year    Month    Actuals    Forecast 
  123456      2014      1         10     
  123456      2014      2         15     
  123456      2014      3         17     
  123456      2014      4                 14.00
  123456      2014      5                 15.33 
  123456      2014      6                 15.44
  123456      2014      7                 14.93

Month 4 = (10+15+17)/3
Month 5 = (15+17+14)/3
Month 6 = (17+14+15.33)/3
Month 7 = (14+15.33+15.44)/3

假设我要计算每个公司的未来18个月的预测.

Let's say I want to calculate the forecast for the next 18 months for each company.

我正在查看去年的数据.一些公司有2个月的数据，其他12个月，依此类推.

I'm looking at the data for last year. Some companies has e.g. 2 months of data and other 12 months and so on.

我已经搜索并找到了许多不同的解决方案，但是所有这些解决方案都只考虑实际情况.

I have searched and found many different solutions but all of them only takes the actuals into account.

我认为我必须进行递归CTE，但我无法弄清楚.

I think I have to make a recursive CTE, but I can't figure it out.

请帮助:)

推荐答案

所以您希望基于先前的移动平均线来移动平均线:) 我认为，在SQL Server上进行编程时，必须始终意识到哪些任务更适合基于集合的方法，哪些任务更通用.如果您问我，您的任务非常适合简单的逐行处理.这是一个例子

So you want moving average based on previous moving average :) I think that when programming on SQL Server one always have to realize which tasks are more suited to set-based approach and which are more general row-by-row approach. Your task, if you ask me, is perfect for simple row-by-row processing. Here's an example

declare @temp table ([Month] int, Actual decimal(29, 2), Forecast decimal(29, 2))
declare @month int

insert into @temp (
    [Month], Actual
)
select 1, 10 union all
select 2, 15 union all
select 3, 17

select @month = isnull(max([Month]) + 1, 1) from @temp

while @month <= 18
begin
    insert into @temp (
        [Month], Forecast
    )
    select
        @month, avg(a.value) as Forecast
    from (
        select top 3 isnull(Actual, Forecast) as value
        from @temp
        order by [Month] desc
    ) as a

    select @month = @month + 1
end

select * from @temp

这篇关于SQL-计算预测平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！