像计算累加占比前80%的产品 应该怎么添加语句

Question

表结构如上：

我写的代码如下：
select (@num:=@num+1) '排名',t1.产品 as 产品名称,t1.销售金额 as 销售金额, (t1.销售金额/t2.总和) as 占比
from (select @num:=0) num,
(select cp.pname 产品,sum(priceSALE_AMOUNT) as 销售金额,1 as 关联
from 销量明细 xl
left join 客户明细 kh on xl.customId = kh.customId
left join 产品明细 cp on xl.pid = cp.pid
group by cp.pid
)t1
left join
(select sum(priceSALE_AMOUNT) as 总和,1 as 关联
from 销量明细 xl
left join 客户明细 kh on xl.customId = kh.customId

数据库是MySQL的
left join 产品明细 cp on xl.pid = cp.pid)t2
on t1.关联 = t2.关联
order by (t1.销售金额/t2.总和) desc

相求累计占比前80%的产品 该怎么添加语句呢

Answer 1

MYSQL8.0以上版本,使用开窗函数中的CUME_DIST,返回累计占比

select * from (
select cp.pid,
cume_dist() over( order by sum(cp.price*xl.sale_amount) desc)  累计占比
from 产品明细 cp left join 销量明细 xl
on  xl.pid=cp.pid
group by cp.pid) 
where 累计占比<=0.8

而且你这题和上一题都犯了个毛病,明明查询结果中不需要客户信息,产品明细和销量明细也不需要通过客户信息关联,为什么非得把客户明细带上去啊

---

为什么要加到你那个语句里去啊？你不是要查累计占比前80%的产品么,这个sql查出来就是了,如果你要同时显示单个占比,多查一个字段就好了

select * from (
select cp.pid,
sum(cp.price*xl.sale_amount) 销售金额,
sum(cp.price*xl.sale_amount) /(sum(sum(cp.price*xl.sale_amount)) over() ) 占比,
cume_dist() over( order by sum(cp.price*xl.sale_amount) desc)  累计占比
from 产品明细 cp left join 销量明细 xl
on  xl.pid=cp.pid
group by cp.pid) 
where 累计占比<=0.8