如何优化一下改代码

zhangzhikai397 2015-07-22 03:57:24
1、问题:数据库中有个表(其中Price表示单价,Material_Count表示数量)
Price Material_Count
50.00 100
10.00 100
1.00 10000
500.00 100
1.00 500
10.00 500
5.00 500
5.00 500
5.00 500
2.00 200
200.00 100
200.00 400
20.00 400
2、希望通过SQL语句得到如下结果:
单价 数量 金额 占比 累计占比
200.00 400 80000.00 0.4268 0.4268
500.00 100 50000.00 0.2668 0.6936
200.00 100 20000.00 0.1067 0.8003
1.00 10000 10000.00 0.0533 0.8536
20.00 400 8000.00 0.0426 0.8962
50.00 100 5000.00 0.0266 0.9228
10.00 500 5000.00 0.0266 0.9494
5.00 500 2500.00 0.0133 0.9627
5.00 500 2500.00 0.0133 0.976
5.00 500 2500.00 0.0133 0.9893
10.00 100 1000.00 0.0053 0.9946
1.00 500 500.00 0.0026 0.9972
2.00 200 400.00 0.0021 0.9993
(金额=单价*数量; 占比=金额/SUM(金额); 累计占比=占比的累计求和)

3、本人写的SQL语句 大家觉得有没有改进之处:
with T as
(
SELECT Price,
Material_Count,
Price*Material_Count AS TotalPrice,
SUM(Price*Material_Count) OVER() AS Total,
Price*Material_Count/SUM(Price*Material_Count) OVER() AS Share,
ROW_NUMBER() OVER(ORDER BY Price*Material_Count DESC) AS ROW
FROM [Order]
)
SELECT Price,
Material_Count,
TotalPrice,
Share,
(select SUM(Share) from T b where b.ROW<=a.ROW) as Cum
FROM T a

...全文
133 1 收藏 4
写回复
4 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
gw6328 2015-07-30
楼主的已经很简化了。
回复
zhangzhikai397 2015-07-29
不错 ,谢谢大家
回复
Tiger_Zhao 2015-07-22
不能直接累加Share,要累加TotalPrice再做除非,否则最后不为1。
WITH T AS (
SELECT Price,
Material_Count,
Price*Material_Count AS TotalPrice,
SUM(Price*Material_Count) OVER() AS Total,
Price*Material_Count/SUM(Price*Material_Count) OVER() AS Share,
ROW_NUMBER() OVER(ORDER BY Price*Material_Count DESC) AS ROW
FROM [Order]
)
,R AS (
SELECT *,
CONVERT(money,TotalPrice) RowTotal,
Share Cum0,
CONVERT(float,TotalPrice / Total) Cum
FROM T
WHERE ROW = 1
UNION ALL
SELECT T.*,
CONVERT(money,R.RowTotal + T.TotalPrice),
R.Cum0 + T.Share,
CONVERT(float,(R.RowTotal + T.TotalPrice) / T.Total)
FROM R
JOIN T
ON R.ROW + 1 = T.ROW
)
SELECT Price,
Material_Count,
TotalPrice,
Share,
Cum0,
Cum
FROM R

    Price Material_Count  TotalPrice                      Share                       Cum0                    Cum
--------- -------------- ----------- -------------------------- -------------------------- ----------------------
200.00 400 80000.00 0.4268943436499466382070 0.4268943436499466382070 0.426894343649947
500.00 100 50000.00 0.2668089647812166488794 0.6937033084311632870864 0.693703308431163
200.00 100 20000.00 0.1067235859124866595517 0.8004268943436499466381 0.80042689434365
1.00 10000 10000.00 0.0533617929562433297758 0.8537886872998932764139 0.853788687299893
20.00 400 8000.00 0.0426894343649946638207 0.8964781216648879402346 0.896478121664888
50.00 100 5000.00 0.0266808964781216648879 0.9231590181430096051225 0.92315901814301
10.00 500 5000.00 0.0266808964781216648879 0.9498399146211312700104 0.949839914621131
5.00 500 2500.00 0.0133404482390608324439 0.9631803628601921024543 0.963180362860192
5.00 500 2500.00 0.0133404482390608324439 0.9765208110992529348982 0.976520811099253
5.00 500 2500.00 0.0133404482390608324439 0.9898612593383137673421 0.989861259338314
10.00 100 1000.00 0.0053361792956243329775 0.9951974386339381003196 0.995197438633938
1.00 500 500.00 0.0026680896478121664887 0.9978655282817502668083 0.99786552828175
2.00 200 400.00 0.0021344717182497331910 0.9999999999999999999993 1
回复
Pact_Alice 2015-07-22
DECLARE @sql NUMERIC(18,8),@sql2 NUMERIC(18,8) SET @sql=(SELECT SUM(Price*Material_Count) FROM tabname) SET @sql2=(select SUM((Price*Material_Count)/@sql) AS Share FROM tabname) SELECT Price, Material_Count, Price*Material_Count AS TotalPrice, Price*Material_Count/@sql AS Share, ((Price*Material_Count)/@sql)/@sql2 FROM tabname
回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2015-07-22 03:57
社区公告
暂无公告