浅谈MySQL进行二八分析

导语

众所周知，二八分析法已经在我们的日常工作中被广泛运用，简单来说就是80%的社会财富是集中在20%的人手中，移植到我们的实际工作中，例如本次讲如何利用SQL来完成每个员工销售量的累计占比，看看百分之80的销量是哪些员工完成的。

数据准备

如下分别是业务人员A-E的月销量

with temp as(
 select 'A' as name,18 as sale
 union all
 select 'B' as name,2 as sale
 union all
 select 'C' as name,20 as sale
 union all
 select 'D' as name,43 as sale
 union all
 select 'E' as name,9 as sale
)
select * from temp

Excel做法回顾

我们在excel中计算的时候，先是将数值升序，然后在进行累加，例如：C2 = C2, C3 = C2+C3,C4 = C2+ C3 + C4,...

方法一：自连接法（不限版本）

• 表的自连接法在SQL语句查询中非常实用，因为表与表连接以后会创造有利的计算字段，减小了我们解决问题难度。而常常使用的是等值连接（符合为"="），这里我们要巧妙的借用不等值连接法

第一步：连接表

with temp as(
 select 'A' as name,18 as sale
 union all
 select 'B' as name,2 as sale
 union all
 select 'C' as name,20 as sale
 union all
 select 'D' as name,43 as sale
 union all
 select 'E' as name,9 as sale
)
select * from temp t1,temp t2 where t1.sale>=t2.sale
order by t1.sale,t2.sale

第二步：分组求和

-- 方法一
with temp as(
 select 'A' as name,18 as sale
 union all
 select 'B' as name,2 as sale
 union all
 select 'C' as name,20 as sale
 union all
 select 'D' as name,43 as sale
 union all
 select 'E' as name,9 as sale
)
select t1.name,
   sum(t2.sale) as cumsum, -- 累计求和
   concat(round((sum(t2.sale)/(select sum(sale) from temp))*100,2),'%') as cumper -- 累计百分比
from temp t1,temp t2 
where t1.sale>=t2.sale
group by t1.sale
order by t1.sale,t2.sale

方法二：变量法（不限版本）

• 我们巧妙的借用变量，将销量赋值给变量，进行累计

with temp as(
 select 'A' as name,18 as sale
 union all
 select 'B' as name,2 as sale
 union all
 select 'C' as name,20 as sale
 union all
 select 'D' as name,43 as sale
 union all
 select 'E' as name,9 as sale
)
select name,
       sale,
    @stepsum:= @stepsum + sale as cumsum, -- 累计求和
    concat(round((@stepsum/(select sum(sale) from temp))*100,2),'%') as cumper -- 累计百分比
from temp t,(select @stepsum:=0)a
order by sale

方法三：窗口函数（要求8以上版本）

• 利用窗口函数sum进行累计求和

with temp as(
 select 'A' as name,18 as sale
 union all
 select 'B' as name,2 as sale
 union all
 select 'C' as name,20 as sale
 union all
 select 'D' as name,43 as sale
 union all
 select 'E' as name,9 as sale
)
select name,
    sum(sale)over(order by sale) cumsum,
    concat(round((sum(sale)over(order by sale)/(select sum(sale) from temp))*100,2),'%') as cumper
from temp t

总结

表的连接法，变量法，窗口函数的使用，我已在历史文章之中介绍，不止局限与本片的累计问题，还可以应用top问题，连续性问题，熟练掌握他们的技法，让复杂问题不在难！！！