滚动12/24个月平均价格

我被这个逻辑束缚了一段时间，终于向社区寻求帮助。我正在制作一个价格差异图表，使用以下逻辑：
上一年的总销量-如果过去12个月内该商品的销量为空，表示该商品从未售出，则为0
（过去12个月内该项目所有交易的平均价格-过去13-24个月内该项目所有交易的平均价格）x（本年总交易量）
我试着用高级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\u hdr\u mst.inv\u date）=year（dateadd（year，0，getdate（）））复制了相同的代码，以获得当前的数量和当前的平均价格。
你知道我可以把where改成什么吗？这样就可以跟踪0-11个月（当前）和12-23个月（以前）范围内所有交易项目的平均价格和交易量？
注意：我还需要添加逻辑，说明如果该项在前12个月不存在，那么我需要它为0。我用一个主项表来连接这两个查询，以确保我得到他们试图跟踪的所有项，而不管是哪一年。

因为您没有指定所需的输出格式，所以有多种解决方案。
此外，你的问题包含一些关于你如何计算“滚动”平均数的矛盾信息。与使用实际年份值相比，像[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
此解决方案并排（在同一行上）显示各个时段的值。这两个句点有两个外部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 关键字。你和我吵架了 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

希望这能解决你的问题。

滚动12/24个月平均价格

1条答案

相关问题

热门标签

最新问答