这种不断累加的计算,其实用sql来实现,效率还是比较低的。在sql server 2012中新增了一部分分析函数,其中对 sum函数做了扩展,可以通过 sum(v) over(partition by ... order by ...)对列v的值进行累计求和,效率 应该会比 单一的 rownum<= f.rownum 来的快一点。建议实在不行,就用游标来实现,可以做到 只对表扫描1遍,就可以达到累计求和的目的,速度应该会提高上百倍
你在实现这种查询时,是否可以考虑把中间结果 先存到 临时表中,后面直接引用临时表再进行计算。另外,建议你少用 子查询: (select sum([%Value]) from acc as e where rownum<= f.rownum) as accumulaSumVal而是多用 关联join,因为你是要先求和的,所以就算用了 join 也不会产生重复值,所以不需要再次去重,而join的性能好于子查询
非常感谢,我用sum over partition做的最后: select *, case when AccumulateValue between 0 and 0.8 then 'A' WHEN AccumulateValue BETWEEN 0.8 AND 0.9 THEN 'B' WHEN AccumulateValue BETWEEN 0.9 AND 0.99 THEN 'C' WHEN AccumulateValue BETWEEN 0.99 AND 1 THEN 'C-' ELSE NULL END ValueCLASS from ( SELECT *, sum([%Value]) over (partition by null order by [%Value]) AS AccumulateValuefrom( select *,D.TotalCA/sum(D.TotalCA) over (partition by null) as [%Value] FROM( select [Supply Chain],[Region],[Mat type name],[Plant],[Mat name],[MONTH], SUM([Val INV total CA]) AS TotalCA from [INVENTORY$]
GROUP BY [Supply Chain],[Region],[Mat type name],[Plant],[Mat name],[Month] )D )E)F 谢谢指导。不过您说的那个游标,不会用。
这种不断累加的计算,其实用sql来实现,效率还是比较低的。在sql server 2012中新增了一部分分析函数,其中对 sum函数做了扩展,可以通过 sum(v) over(partition by ... order by ...)对列v的值进行累计求和,效率 应该会比 单一的 rownum<= f.rownum 来的快一点。建议实在不行,就用游标来实现,可以做到 只对表扫描1遍,就可以达到累计求和的目的,速度应该会提高上百倍
你在实现这种查询时,是否可以考虑把中间结果 先存到 临时表中,后面直接引用临时表再进行计算。另外,建议你少用 子查询: (select sum([%Value]) from acc as e where rownum<= f.rownum) as accumulaSumVal而是多用 关联join,因为你是要先求和的,所以就算用了 join 也不会产生重复值,所以不需要再次去重,而join的性能好于子查询
select *, case when AccumulateValue between 0 and 0.8 then 'A'
WHEN AccumulateValue BETWEEN 0.8 AND 0.9 THEN 'B'
WHEN AccumulateValue BETWEEN 0.9 AND 0.99 THEN 'C'
WHEN AccumulateValue BETWEEN 0.99 AND 1 THEN 'C-'
ELSE NULL END ValueCLASS
from
(
SELECT *, sum([%Value]) over (partition by null order by [%Value]) AS AccumulateValuefrom( select *,D.TotalCA/sum(D.TotalCA) over (partition by null) as [%Value] FROM( select [Supply Chain],[Region],[Mat type name],[Plant],[Mat name],[MONTH],
SUM([Val INV total CA]) AS TotalCA from [INVENTORY$]
GROUP BY [Supply Chain],[Region],[Mat type name],[Plant],[Mat name],[Month] )D )E)F
谢谢指导。不过您说的那个游标,不会用。