SQL 同比、环比计算公式及实例演示

同比数据计算公式：

同比：一般情况下是今年第n月与去年第n月比
计算方式：同比增长率=（本期数－同期数）÷同期数×100%
所以要有个本期数据与同期数据一一对应的表，那就要本期数据与同期数据分离

环比数据计算公式：
环比：一般是指报告期水平与前一时期水平之比，此处指本月数据与上月数据（或者本日数据与上日数据）
计算方式：环比增长速度=（本期数－上期数）÷上期数×100%
 

CREATE TABLE SalesDetail(
ID int,   --序号
DepartName varchar(50), --部门
Sales decimal(10,2), --销售量
SalesDate Datetime --销售日期
)
 
insert into SalesDetail(ID, DepartName, Sales, SalesDate)
select 1, '营销一部', 300, '2006-7-1'
UNION ALL select 2, '营销二部', 500, '2006-7-1'
UNION ALL select 3, '营销三部', 800, '2006-8-1'
UNION ALL select 4, '营销一部', 600, '2006-8-1'
UNION ALL select 5, '营销二部', 800, '2006-8-1'
UNION ALL select 6, '营销一部', 400, '2007-7-1'
UNION ALL select 7, '营销二部', 800, '2007-7-1'
UNION ALL select 8, '营销三部', 700, '2007-8-1'
UNION ALL select 9, '营销一部', 600, '2008-7-1'
UNION ALL select 10, '营销二部', 700, '2008-7-1'
 
select * from SalesDetail
 
-- 计算环比
select c.DepartName as 部门, convert(varchar(10), c.SalesDate, 23) as 对比年月, c.Sales as 本月销售总量, d.Sales as 上月销售总量, case when d.Sales is null or d.Sales=0 then '无穷大' else cast(cast((isnull(c.Sales, 0)-isnull(d.Sales,0))*100/isnull(d.Sales, 0) as decimal(10,2)) as varchar(50))+'%' end as 环比 from SalesDetail c left join
(select a.DepartName as DepartName, a.Sales as Sales, a.SalesDate as lsSalesDate, b.SalesDate as SalesDate from SalesDetail a join SalesDetail b on a.SalesDate=DateAdd(month, -1, b.SalesDate) and a.DepartName=b.DepartName) d
on c.SalesDate=d.SalesDate and c.DepartName=d.DepartName
 
-- 计算同比
select c.DepartName as 部门, convert(varchar(10), c.SalesDate, 23) as 对比年月, c.Sales as 本月销售总量, d.Sales as 去年同期销售总量, case when d.Sales is null or d.Sales=0 then '无穷大' else cast(cast((isnull(c.Sales, 0)-isnull(d.Sales,0))*100/isnull(d.Sales, 0) as decimal(10,2)) as varchar(50))+'%' end as 同比 from SalesDetail c left join
(select a.DepartName as DepartName, a.Sales as Sales, a.SalesDate as lsSalesDate, b.SalesDate as SalesDate from SalesDetail a join SalesDetail b on a.SalesDate=DateAdd(year, -1, b.SalesDate) and a.DepartName=b.DepartName) d
on c.SalesDate=d.SalesDate and c.DepartName=d.DepartName

同比效果:

环比效果: