滚动12/24个月平均价格

Question

我已经在这个逻辑上坚持了一段时间，最终向社区寻求帮助。我正在制作一个价格差异图，它使用的逻辑是：

前一年的总销量-如果该商品在过去12个月的销量为NULL，则表示该商品从未售出，否则为0

(最近12个月该项目所有交易的平均价格-最近13-24个月该项目所有交易的平均价格)X(本年度总成交量)

我尝试执行一个WITH高级SQL查询，以细分当前日期之前12个月和13-24个月的平均价格，但我不认为我的WHERE语句可以做到这一点：

SELECT

inv_item_mst.item,
AVG(inv_item_mst.price*(1-(inv_item_mst.disc/100))) AS [AvgPreviousPrice],
SUM(inv_item_mst.qty_invoiced) AS [PreviousVolume],
inv_hdr_mst.cust_num


FROM inv_item_mst

LEFT JOIN inv_hdr_mst
ON inv_item_mst.inv_num=inv_hdr_mst.inv_num

WHERE YEAR(inv_hdr_mst.inv_date) = YEAR(DATEADD(YEAR,-1,GETDATE()))

GROUP BY inv_item_mst.item,
inv_hdr_mst.cust_num

我使用WHERE YEAR(inv_hdr_mst.inv_date) = YEAR(DATEADD(YEAR,0，GETDATE()复制了相同的代码，以获得当前成交量和当前平均价格。

你知道我可以在哪里更改，以便跟踪0-11个月(当前)和12-23个月(以前)范围内的所有交易的平均价格和成交量吗？

注意:我还需要添加逻辑，如果项目在过去12个月内不存在，那么我需要它是0。我使用主项目表连接这两个查询，以确保我获得了他们尝试跟踪的所有项目，而不考虑年份。

Answer 1

因为您没有指定所需的输出格式，所以您的问题有多种解决方案。

此外，您的问题包含一些关于如何计算“滚动”平均值的冲突信息。与使用实际年份值相比，像0-11和12、13个月这样的日期范围将产生不同的结果。下面的例子证明了我的观点。

today              = 2020-07-22
current 12 months  = [2019-07-22, 2020-07-22] --> defined with dateadd(year, -1, ...)
current year       = 2020                     --> defined with year(...)-1
previous 12 months = [2018-07-22, 2019-07-21] --> defined with dateadd(year, -2, ...)
previous year      = 2019                     --> defined with year(...)-2

示例数据

定义表结构

declare @item table
(
    ItemId int,
    ItemName nvarchar(20)
);

declare @trans table
(
    ItemId nvarchar(20),
    TransDate date,
    TransVolume int,
    TransPrice money
);

确定样本数据的日期范围

select  convert(date,getdate()) as 'today',
        dateadd(year, -1, convert(date,getdate())) as 'start last year',
        dateadd(year, -2, convert(date,getdate())) as 'start last 2 years';


today       start last year  start last 2 years
----------  ---------------  ------------------
2020-07-22  2019-07-22       2018-07-22

插入一些样本数据

insert into @item (ItemId, ItemName) values
(1, 'Awesome product'),
(2, 'Mega item'),
(3, 'Super stuff'),
(4, 'Weird thing'); -- will not be sold

insert into @trans (ItemId, TransDate, TransVolume, TransPrice) values
(1, '2017-01-01', 99, 9.99), -- before last 2 years, to be excluded
(1, '2019-01-01', 10, 1.20), -- in     last 2 years
(1, '2019-05-01',  7, 1.50), -- in     last 2 years
(1, '2019-06-01',  1, 1.00), -- in     last 2 years
(1, '2020-03-01',  8, 2.00), -- in     last 1 year
(2, '2019-03-01', 12, 0.75), -- in     last 2 years
(2, '2019-08-01',  3, 1.20), -- in     last 1 year
(2, '2019-10-01', 10, 1.20), -- in     last 1 year
(2, '2020-07-01',  9, 0.80), -- in     last 1 year
(3, '2019-04-01',  7, 1.20), -- in     last 2 years
(3, '2019-05-01', 11, 1.50), -- in     last 2 years
(3, '2020-04-01',  3, 0.80), -- in     last 1 year
(3, '2020-05-01',  2, 1.25); -- in     last 1 year

解决方案1

此解决方案并排显示各个周期的值(在同一行上)。这两个阶段有两个outer apply子查询。本期和上期按日期范围计算。

select  i.ItemName,
        isnull(calc1.SumVol, 0) as 'SumVolume1', -- volume in last 2 years
        isnull(calc1.AvgPri, 0) as 'AvgPrice1',  -- average price in last 2 years
        isnull(calc2.SumVol, 0) as 'Volume2',    -- volume in last 1 year
        isnull(calc2.AvgPri, 0) as 'AvgPrice2'   -- average price in last 1 year
from @item i
outer apply (   select  sum(t2.TransVolume) as 'SumVol',
                        avg(t2.TransPrice) as 'AvgPri'
                from @trans t2
                where t2.ItemId = i.ItemId
                  and t2.TransDate >= dateadd(year, -2, convert(date,getdate()))
                  and t2.TransDate <  dateadd(year, -1, convert(date,getdate())) ) calc1
outer apply (   select  sum(t1.TransVolume) as 'SumVol',
                        avg(t1.TransPrice) as 'AvgPri'
                from @trans t1
                where t1.ItemId = i.ItemId
                  and t1.TransDate >= dateadd(year, -1, convert(date,getdate())) ) calc2;

结果如下所示：

ItemName             SumVolume1  AvgPrice1             Volume2     AvgPrice2
-------------------- ----------- --------------------- ----------- ---------------------
Awesome product      18          1,2333                8           2,00
Mega item            12          0,75                  22          1,0666
Super stuff          18          1,35                  5           1,025
Weird thing          0           0,00                  0           0,00

解决方案2

这个解决方案将不同周期的值显示在彼此的下面(不同的行)。它使用由with关键字定义的Common Table Expression (CTE)。对于没有事务的项目，您会得到一行null (可以用isnull()替换)。当前期间和上一期间再次使用日期范围进行计算。

with cte_TransRange as
(
    select  case
                when t.TransDate >= dateadd(year, -1, convert(date,getdate())) then -1
                when t.TransDate >= dateadd(year, -2, convert(date,getdate())) then -2
                else 0
            end as 'YearRange',
            t.ItemId,
            t.TransPrice,
            t.TransVolume
    from @trans t
)
select  i.ItemName,
        tr.YearRange,
        isnull(sum(tr.TransVolume),0) as 'SumVolume',
        isnull(avg(tr.TransPrice),0) as 'AvgPrice'
from @item i
left join cte_TransRange tr
    on  tr.ItemId = i.ItemId
    and tr.YearRange <> 0
group by    i.ItemName,
            tr.YearRange
order by i.ItemName;

这将以另一种格式提供相同的数字：

ItemName             YearRange   SumVolume   AvgPrice
-------------------- ----------- ----------- ---------------------
Awesome product      -2          18          1,2333
Awesome product      -1          8           2,00
Mega item            -2          12          0,75
Mega item            -1          22          1,0666
Super stuff          -2          18          1,35
Super stuff          -1          5           1,025
Weird thing          NULL        0           0,00

解决方案3

解决方案2的一个变体，但我现在使用的不是日期范围，而是实际的年份。正因为如此，这个版本要短得多(也更简单)。

select  i.ItemName,
        year(t.TransDate) as 'Year',
        isnull(sum(t.TransVolume),0) as 'SumVolume',
        isnull(avg(t.TransPrice),0) as 'AvgPrice'
from @item i
left join @trans t
    on  t.ItemId = i.ItemId
    and year(t.TransDate) > year(getdate())-2
group by    i.ItemName,
            year(t.TransDate)
order by i.ItemName;

但与之前的数字相比，这会产生不同的结果！

ItemName             Year        SumVolume   AvgPrice
-------------------- ----------- ----------- ---------------------
Awesome product      2019        18          1,2333
Awesome product      2020        8           2,00
Mega item            2019        25          1,05
Mega item            2020        9           0,80
Super stuff          2019        18          1,35
Super stuff          2020        5           1,025
Weird thing          NULL        0           0,00

希望这能解决你的问题。