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

hlj321 2012-11-19 02:49:32
按单品销售额分类
取前三个月的销售
按单品销售额从高到低排序
占销售额前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
...全文
466 11 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
11 条回复
切换为时间正序
请发表友善的回复…
发表回复
-晴天 2012-11-19
  • 打赏
  • 举报
回复
... 以为一行一项的.
开启时代 2012-11-19
  • 打赏
  • 举报
回复
引用 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秒 难道我机器高配吗?
-晴天 2012-11-19
  • 打赏
  • 举报
回复
如果嫌慢的话,可以这样:
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
不过也差不多得四分钟(在我的机器上)才能执行完.
-晴天 2012-11-19
  • 打赏
  • 举报
回复
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)
hlj321 2012-11-19
  • 打赏
  • 举报
回复
引用 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……
谢谢,正解
hlj321 2012-11-19
  • 打赏
  • 举报
回复
引用 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%
开启时代 2012-11-19
  • 打赏
  • 举报
回复
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
开启时代 2012-11-19
  • 打赏
  • 举报
回复
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
playwarcraft 2012-11-19
  • 打赏
  • 举报
回复

--是否这个意思?
;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

hlj321 2012-11-19
  • 打赏
  • 举报
回复
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
开启时代 2012-11-19
  • 打赏
  • 举报
回复
语句别这么贴 ,出来乱七八糟的 还得重新排版 。

34,838

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧