t-sql比较尾随平均值与当前数据

我有一个任务来获取接收到的记录数的尾随平均值 过去10个月以及本月的记录数。例如,在集合中 在以下记录中,最近三个月每月平均记录为4.33。 该月的记录数是3。 因此,预期结果是

Result

declare @recordsReceived table (id int, dob date)

INSERT @recordsReceived VALUES (1,'2020-01-01')
INSERT @recordsReceived VALUES (2,'2020-01-08')
INSERT @recordsReceived VALUES (3,'2020-01-17')
INSERT @recordsReceived VALUES (5,'2020-02-03')
INSERT @recordsReceived VALUES (6,'2020-02-09')
INSERT @recordsReceived VALUES (7,'2020-02-16')
INSERT @recordsReceived VALUES (8,'2020-02-21')
INSERT @recordsReceived VALUES (9,'2020-02-28')
INSERT @recordsReceived VALUES (10,'2020-03-01')
INSERT @recordsReceived VALUES (11,'2020-03-07')
INSERT @recordsReceived VALUES (12,'2020-03-21')
INSERT @recordsReceived VALUES (13,'2020-03-28')
INSERT @recordsReceived VALUES (14,'2020-04-03')
INSERT @recordsReceived VALUES (15,'2020-04-09')
INSERT @recordsReceived VALUES (16,'2020-04-30')
INSERT @recordsReceived VALUES (5,'2020-02-03')
INSERT @recordsReceived VALUES (6,'2020-02-09')
INSERT @recordsReceived VALUES (7,'2020-02-16')
INSERT @recordsReceived VALUES (8,'2020-02-21')
INSERT @recordsReceived VALUES (9,'2020-02-28')
INSERT @recordsReceived VALUES (10,'2020-03-01')
INSERT @recordsReceived VALUES (12,'2020-03-21')
INSERT @recordsReceived VALUES (12,'2020-03-21')
INSERT @recordsReceived VALUES (13,'2020-03-28')
INSERT @recordsReceived VALUES (14,'2020-04-03')
INSERT @recordsReceived VALUES (15,'2020-04-09')
INSERT @recordsReceived VALUES (16,'2020-04-30')
评论
  • ret
    ret 回复

    你可以试试看

    Select * From (
      Select count(month(dob))*1.00/count(distinct(month(dob))) As [TAVG] 
      from @recordsReceived 
      Where month(dob)  < (Select max(month(dob)) from @recordsReceived)) A
    
     ,
     (Select count(month(dob)) [Current]
     from @recordsReceived 
     Where month(dob)  = (Select max(month(dob)) from @recordsReceived)) B