SQL Server获得最近X个月的平均值，包括丢失的月份 [英] SQL Server getting last X month average including missing months

问题描述

我的月度销售数据如下(希望我可以画表，但不能让我放进去，或者我不知道怎么做)

My monthly sales data look like this.(Wish I can draw table but it's not letting me put one in or I don't know how)

表有两列日期和销售单位.日期只有一个月的第一天.但并非所有月份都可以进入.因此，可能是3/1/2017有20个单位.那么在2017年4月1日没有行.并且5/1/2017有100个单位.

Table has two columns date and units sold. Date just have first day of month. But not all months have entry. So it might be 3/1/2017 has 20 units. then no row for 4/1/2017. And 5/1/2017 has 100 units.

例如，我需要计算最近3个月的平均值.但是几个月都没有排.我如何将缺失的月份计入平均计算中?我可以进行分组和求平均值，但是这样可以省去计算中缺少的月份.

I need to calculate last 3 months average for example. But there is no row for some months. How would I include the missing month into the average calculation? I can do group and average but that is leaving out the missing month in calculation.

谢谢.

推荐答案

-- reference date to start data aggregation
declare @refDate date = '2017-04-27'
-- number of months to go back    
declare @LastMonthsN int = 3

如果您需要显示过去n个月的所有行:

If you need to show all rows for last n months:

;with
m as (
    select DATEADD(mm, DATEDIFF(mm, 0, @refdate)-_month+1, 0) _month
    from ( values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)) x (_month)
    where _month<=@LastMonthsN
),
f as (
    select *
    from SalesData d
    where ref_month between dateadd(mm, -@LastMonthsN, @refDate) and dateadd(mm, 0, @refDate)
)
select _month, Isnull(qty, 0) qty
from m
left join f on _month = ref_month

如果您只需要期平均数:

If you only need the period average:

;with
f as (
    select *
    from SalesData d
    where ref_month between dateadd(mm, -@LastMonthsN, @refDate) and dateadd(mm, 0, @refDate)
)
select avg(isnull(qty, 0)) periodAVG
from f 

这篇关于SQL Server获得最近X个月的平均值，包括丢失的月份的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！