ABC分类法的有关问题

ABC分类法的问题
大家好，比如我有一张表：

SQL code

declare @importantLead table    (        custID int,        amount decimal(18,0),        ownerID int,        deptID int    )

然后，这里面的东西就是说，按照amount降序排列，每个owner都有好几个custID，现在是需要从上往下查找，比如找到前4行的amount占到总的60%，则认为这些是重要的客户，这时候统计下有多少个，只需要得到重要客户的个数。

请教下如何实现。


[解决办法]

SQL code

declare @importantLead table    (        custID int,        amount decimal(18,0),        ownerID int,        deptID int    )insert @importantLeadselect 1,20,1,1 union allselect 1,24,2,1 union allselect 2,52,2,2 union allselect 3,96,1,3 union allselect 3,45,3,3 union allselect 3,96,3,3 union allselect 3,34,4,3 union allselect 3,18,2,3declare @total floatset @total=(select SUM(amount) from @importantLead)declare @sum floatset @sum=(select SUM(amount) from(select top 4 amount from @importantLead order by amount desc)a)if (@sum/@total)>=0.6 select count(distinct custID) as 重要客户数 from(select top 4 custID from @importantLead order by amount desc)b/*重要客户数2*/
[解决办法]
SQL code
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'tba')BEGIN    DROP TABLE tbaENDGOCREATE table tba    (        custID int,        amount decimal(18,0),        ownerID int,        deptID int    )           INSERT INTO tbaSELECT 1,1000,1,1 UNIONSELECT 2,2000,1,1 UNIONSELECT 3,5000,1,1 UNIONSELECT 4,1000,1,1 UNIONSELECT 5,500,1,1 UNIONSELECT 6,100,1,1 UNIONSELECT 7,100,1,1 UNIONSELECT 8,100,1,1 UNIONSELECT 9,0,1,1 UNIONSELECT 10,0,1,1 UNIONSELECT 11,0,1,1GOWITH tbb AS( SELECT custID,amount,ownerID,deptID,RN=ROW_NUMBER() OVER (ORDER BY amount Desc) FROM tba)SELECT custID,(SELECT SUM(amount) FROM tbb AS B WHERE A.RN >= B.RN) AS amount,(SELECT SUM(amount) FROM tbb AS B WHERE A.RN >= B.RN)/(SELECT SUM(amount) FROM tbb) AS ProportionFROM tbb AS AcustID    amount    Proportion3    5000    0.5102042    7000    0.7142851    8000    0.8163264    9000    0.9183675    9500    0.9693876    9600    0.9795917    9700    0.9897958    9800    1.0000009    9800    1.00000010    9800    1.00000011    9800    1.000000 

 

查看更多 下一篇