时间戳

首页 > 解决方案 > 比较当月销售额与上月销售额

sql - 比较当月销售额与上月销售额

问题描述

获取测试数据的代码：

create table SalesCalls
(
    EmpId INT NOT NULL,
    EmpName nvarchar(20),
    month INT,
    Year INT,
    CallsMade INT
)
GO

Insert into SalesCalls values
(1,'ABC',12,2018,10),
(1,'ABC',1,2019,15),
(1,'ABC',2,2019,20),
(2,'DEF',12,2018,12),
(2,'DEF',1,2019,14),
(2,'DEF',2,2019,26)
GO

目标是比较员工当前月份的销售额与该员工上个月的销售额，并找出其中的百分比变化。使用以下查询实现了这一点：

With SalesCTE as 
(
    select EmpId,EmpName,
        Month As CurrentMonth,
        Year as CurrentMonthYear,
        Case When month = 1 then 12 Else (Month-1) End AS PrevMonth,
        Case when month = 1 then (Year - 1) Else Year End As PrevMonthYear,
        CallsMade 
    from SalesCalls
)
select 
    S1.EmpId, S1.EmpName, S1.CurrentMonth, S1.CurrentMonthYear, S1.CallsMade as CurrentMonthCalls,
    S2.CurrentMonth as PrevMont,
    S2.CurrentMonthYear as PrevMonthYear,
    S2.CallsMade as PrevMonthCalls,
    ( CONVERT(numeric(5,2),S1.CallsMade) / S2.CallsMade) * 100 As PercentageChange
from SalesCTE S1
JOIN SalesCTE S2 ON S1.EmpId = S2.EmpId
    AND S1.PrevMonth = S2.CurrentMonth   
    AND S1.PrevMonthYear = S2.CurrentMonthYear
ORDER BY S1.EmpId, S1.CurrentMonth, S1.CurrentMonthYear

上述查询一直有效，直到同月没有员工的冗余记录。

但是后来有多个来源的数据进来了，一个 Employee 表可以有同一个月的多条记录，它仍然有效。因为员工可能会以不同的方式拨打电话。例如，下面的记录被插入到表中：

Insert into SalesCalls values
(1,'ABC',1,2019,1)

现在，上面的查询在上面用于比较当前月份的 SalesCalls 与上个月时运行良好的查询不再有效。

用例的第 2 阶段：为了解决这个问题，我构建了一个包含聚合数据的中间临时表。使用的查询是：

Select EmpId, EmpName, month, Year, SUM(CallsMade) as CallsMade 
into #SalesCalls
from SalesCalls
group by EmpId, EmpName, month, Year

现在SalesCallsCTE 中的表被替换为#SalesCalls，然后上面的查询工作正常。

但是#SalesCalls每次都需要删除并重新创建此表才能看到最新的比较数据。

问题是，是否可以仅使用单个查询而不使用中间临时表或视图来获取比较数据。

标签： sqlsql-servertsqlsql-server-2019

解决方案

只需使用窗口函数：

select EmpId, EmpName, month, Year,
       sum(CallsMade) as CallsMade,
       (case when lag(year * 12 + month) over (partition by empId order by year, month) = year * 12 + month - 1
             then lag(sum(callsMade)) over (partition by empId order by year, month)
        end) as prevMonthCalls,
       (case when lag(year * 12 + month) over (partition by empId order by year, month) = year * 12 + month - 1
             then callsMade * 100.0 / lag(sum(callsMade) over (partition by empId order by year, month)
        end) as as perentageChange
from SalesCalls
group by EmpId, EmpName, month, Year;

join根本不需要s、CTE、子查询或临时表。

推荐阅读