如何取占销售额前80%内的品种？

[hlj321]

按单品销售额分类
取前三个月的销售
按单品销售额从高到低排序
占销售额前80%的算a类
80%—95%的算b类
95%之后的算c类
下面是生成数据的语句

CREATE TABLE [dbo].[xs](
	[rq] [date] NULL,
	[SPID] [int] NOT NULL,
	[je] [decimal](38, 2) NULL
) ON [PRIMARY] 
GO
   
DECLARE @loop INT 
DECLARE @rq DATETIME
DECLARE @je DECIMAL(20,2)
SET @rq = CONVERT(VARCHAR(10),DATEADD(MONTH,-3,GETDATE()),121) 
WHILE @rq < GETDATE()
BEGIN
	SELECT @loop = 1 
	WHILE @loop <= 200
	BEGIN		
		SELECT @je = RAND() * 1000.00,@loop = @loop + 1 
		INSERT INTO XS (rq,SPID,je) VALUES(@rq,@loop,@je)
	END
	SET @rq = DATEADD(DAY,1,@rq)
END

[开启时代]

语句别这么贴 ，出来乱七八糟的 还得重新排版 。

[hlj321]

CREATE TABLE [dbo].[xs]( [rq] [date] NULL, [SPID] [int] NOT NULL, [je] [decimal](38, 2) NULL ) ON [PRIMARY] GO DECLARE @loop INT DECLARE @rq DATETIME DECLARE @je DECIMAL(20,2) SET @rq = CONVERT(VARCHAR(10),DATEADD(MONTH,-3,GETDATE()),121) WHILE @rq < GETDATE() BEGIN SELECT @loop = 1 WHILE @loop <= 200 BEGIN SELECT @je = RAND() * 1000.00,@loop = @loop + 1 INSERT INTO XS (rq,SPID,je) VALUES(@rq,@loop,@je) END SET @rq = DATEADD(DAY,1,@rq) END

[playwarcraft]

--是否这个意思?
;with cte_test
as
(
 select *, rowid= row_number() over (partition by spid order by je desc) 
 ,cnt = (select count(*) from xs where spid=a.spid and rq between dateadd(month,-3,getdate()) and getdate())
 from xs as a
 where rq between dateadd(month,-3,getdate()) and getdate()
)
select 
rq,spid,je,
type= case when rowid <= 0.8 * cnt then 'A'
           when rowid>0.8*cnt and rowid<= 0.95*cnt then 'B'
           else 'C' end
 from cte_test

[开启时代]

with TB as ( select SPID,SUM(je) as je,ROW_NUMBER() over(order by sum(je) desc) as rowid from XS where rq>DATEADD(month,-3,getdate()) group by spid ) ---80%的商品 select SPID,je from TB as a where (select SUM(je) from TB where rowid<=a.rowid)/(select SUM(je) from XS where rq>DATEADD(month,-3,getdate()))*100<80

[开启时代]

with TB as ( select SPID,SUM(je) as je,ROW_NUMBER() over(order by sum(je) desc) as rowid from XS where rq>DATEADD(month,-3,getdate()) group by spid ) ----全部商品分类 select SPID,je,case when (select SUM(je) from TB where rowid<=a.rowid)/(select SUM(je) from XS where rq>DATEADD(month,-3,getdate()))*100<80 then 'A' when (select SUM(je) from TB where rowid<=a.rowid)/(select SUM(je) from XS where rq>DATEADD(month,-3,getdate()))*100 between 80 and 95 then 'B' else 'C' end 类别 from TB as a

[hlj321]

引用 3 楼 playwarcraft 的回复:

SQL code? 1234567891011121314151617 --是否这个意思? ;with cte_test as( select *, rowid= row_number() over (partition by spid order by je desc) ,cnt = (select count(*) from xs where spid=a.spid ……

不是记录数，应当是je金额比,占销售金额的80%

[hlj321]

引用 5 楼 lixzhong 的回复:

with TB as ( select SPID,SUM(je) as je,ROW_NUMBER() over(order by sum(je) desc) as rowid from XS where rq>DATEADD(month,-3,getdate()) group by spid ) ----全部商品分类 select SPID,je,case when (select SU……

谢谢，正解

[-晴天]

declare @c decimal(38,2)
select @c=sum(je) from xs
;with c1 as(
select *,rn=row_number()over(order by je desc),0 as s from xs
),c2 as(
select rq,spid,je,rn,je as s,'a' as flg from c1 where rn=1
union all
select a.rq,a.spid,a.je,a.rn,a.je+b.s as s,
(case when a.je+b.s<@c*0.8 then 'a' when a.je+b.s<0.95 then 'b' else 'c' end) as flg
from c1 a inner join c2 b on a.rn=b.rn+1 where a.s=0 and b.s>0
)
select rq,spid,je,flg from c2 OPTION (MAXRECURSION 0)

[-晴天]

如果嫌慢的话,可以这样:

declare @c decimal(38,2)
select @c=sum(je) from xs
select *,identity(int,1,1) as rn,convert(decimal(38,2),0.00) as s into #c1 from xs order by je desc
update #c1 set s=je where rn=1
while exists(select 1 from #c1 where s=0)
update a set a.s=a.je+b.s from #c1 a inner join #c1 b on a.rn=b.rn+1 where a.s=0 and b.s>0
select rq,spid,je,(case when s<@c*0.8 then 'a' when s<@c*0.95 then 'b' else 'c' end)as flg from #c1 order by spid
go
drop table #c1

不过也差不多得四分钟(在我的机器上)才能执行完.

[开启时代]

引用 5 楼 lixzhong 的回复:

with TB as ( select SPID,SUM(je) as je,ROW_NUMBER() over(order by sum(je) desc) as rowid from XS where rq>DATEADD(month,-3,getdate()) group by spid ) ----全部商品分类 select SPID,je,case wh……

这个是4秒 难道我机器高配吗?

[-晴天]

... 以为一行一项的.