排名趋势公式

--建表和Insert测试数据
use tempdb
go
if OBJECT_ID('PersonalSalesRank') Is not null drop Table PersonSaleRank
go
create table PersonalSalesRank(Name nvarchar(20),YM date,SalesQty int,RankNr int,RankTrend int)
go
set nocount on
insert into dbo.PersonalSalesRank(Name,YM,SalesQty) values
--2017-01
('Kent','20170101',400),
('John','20170101',758),
('Rob','20170101',365),
('Ruben','20170101',487),
('Andy','20170101',651),
--2017-02
('Andy','20170201',668),
('Christy','20170201',541),
('Kent','20170201',712),
('Ruben','20170201',729),
('Rob','20170201',365),
('John','20170201',465),
--2017-03
('Andy','20170301',651),
('Christy','20170301',588),
('Kent','20170301',769),
('Ruben','20170301',752),
('Rob','20170301',552),
('John','20170301',421)
go
if object_id('fn_RankTrend') Is not null drop function fn_RankTrend
go
create function fn_RankTrend
(
@CurrentRankNr int,
@LastRankNr int,
@LastRankTrend int
)
returns int
as
begin
return (isnull (sign(@LastRankNr -@CurrentRankNr)+@LastRankTrend*(1-abs(sign(sign(@LastRankNr-@CurrentRankNr)^sign(@LastRankTrend)))),0))
end
go

--排名
;with cte_Rank as
(
select RankNr,DENSE_RANK() over(PARTITION by YM order by SalesQty desc)as RankNr_1 from dbo.PersonalSalesRank
)
update a
set a.RankNr=a.RankNr_1
from cte_Rank a



--更新排名趋势
declare @YM date='20170101'
while(1=1)
begin
update a
set a.RankTrend=dbo.fn_RankTrend(a.RankNr,b.RankNr,b.RankTrend)
from dbo.PersonalSalesRank a
left join dbo.PersonalSalesRank b on b.Name=a.Name
and b.YM=DATEADD(month,-1,@YM)
where a.YM=@YM;
if @@ROWCOUNT=0 break;
print @YM
set @YM=DATEADD(month,1,@YM)
end

--测试
select name as[姓名],
CONVERT(char(7),a.YM,121) as[月份],
a.SalesQty as[销量],
CONVERT(nvarchar(20),N'第'+rtrim(a.RankNr)+N'名')as [排名],
case SIGN(a.RankTrend)
when -1 then N'↓'
when 1 then N'↑'
else N''
end as [排名升降],
case
when a.RankTrend>=2 then N'连续' +RTRIM(abs(a.RankTrend))+N'月'
else N''
end as[连续上升],
case
when a.RankTrend<=-2 then N'连续' +RTRIM(abs(a.RankTrend))+N'月'
else N''
end as [连续下降]
from dbo.PersonalSalesRank a
order by 2,3 desc
go