sqlserver 计算同比,环比增长

/****** Script for SelectTopNRows command from SSMS  ******/
SELECT  [fdSequenceID]
      ,[fdInnerTime]
      ,[fdTime]
      ,[fdData]
      ,[fdState]
      ,[fdUpdateTime]
      ,[fdCreateTime]
  FROM [NewDBTest].[dbo].[tbDataMonthHG]
  


WITH f AS 
(
  SELECT ROW_NUMBER()OVER(   partition by fdSequenceID ORDER BY fdTime) AS id, * FROM [NewDBTest].[dbo].[tbDataMonthHG]
)

select t.*,
       fdRate=(cast(cast(100*((t.fdData/NULLif(d.fdData,0))-1) as numeric(25,10)) as  varchar(50))+'%' )   --小数变百分数
       from f t
left join f d on
       t.fdInnerTime = DateAdd(year,1,d.fdInnerTime) and t.fdSequenceID = d.fdSequenceID  --同比增长
       order by fdSequenceID,id asc 
       

 

环比增长：

WITH f AS 
(
  SELECT ROW_NUMBER()OVER(   partition by fdSequenceID ORDER BY fdTime) AS id, * FROM [NewDBTest].[dbo].[tbDataMonthHG] where fdSequenceID <100
)
 
select t.*,
       fdRate=(cast(cast(100*((t.fdData/NULLif(d.fdData,0))-1) as numeric(25,10)) as  varchar(50))+'%' )   --将分数变为百分数
       from f t
       
left join f d on
       t.fdInnerTime = DateAdd(month,1,d.fdInnerTime) and t.fdSequenceID = d.fdSequenceID   --获取根据时间获取上月时间
       order by fdSequenceID,id asc 

 某一时间点与相邻的上一个时间的增长

 

WITH f AS 
(
  SELECT ROW_NUMBER()OVER(   partition by fdSequenceID ORDER BY fdTime) AS id, * FROM [NewDBTest].[dbo].[tbData]
)
SELECT  
  t.fdSequenceID,t.fdInnerTime,t.fdTime,t.fdData,t.fdState,t.fdUpdateTime,t.fdCreateTime,t.id,
  (select cast(cast(100*((t.fdData/NULLif(d.fdData,0))-1) as numeric(35,10)) as  varchar(50))+'%') AS fdRate
  from f t 
  LEFT JOIN f d on t.id=d.id+1 where t.fdSequenceID = d.fdSequenceID  ORDER by t.fdSequenceID ASC 
  

-- 不会返回null



WITH f AS 
(
  SELECT ROW_NUMBER()OVER(   partition by fdSequenceID ORDER BY fdTime) AS id, * FROM [NewDBTest].[dbo].[tbData] where fdSequenceID<188
)

select t.*,
       cast(cast(100*((t.fdData/NULLif(d.fdData,0))-1) as numeric(25,10)) as  varchar(50))+'%' 
       from f t
left join f d
       on datediff(day, 
                   DateAdd(day,d.id-1,DateAdd(month,d.fdSequenceID-1,'1900-1-1')),
                   DateAdd(day,t.id-1,DateAdd(month,t.fdSequenceID-1,'1900-1-1'))
                   ) > 0

-- 会返回null