1.求group by之后和的占比
先做group by分组汇总,再单独inner join连接到和,最后分组结果除以连接到的和
select
ifnull(hour_diff,'--合计--') hour_diff,count(1) cnt,count(1)/tot pct
from
(select timestampdiff(hour,createtime,activate_time) hour_diff from t_consumer) w
inner join
(select count(1) tot from t_consumer) c
group by
hour_diff
with rollup;
![](https://i-blog.csdnimg.cn/blog_migrate/99b3292482c9e2f0d30d6d13b90fc6b9.png)
2.求group by之后和的累计占比
先求group by之后和的占比,再利用@s参数对占比进行累加
set @s=0;
select *,@s:=@s+pct cul_pct
from
(select
ifnull(hour_diff,'--合计--') hour_diff,count(1) cnt,count(1)/tot pct
from
(select timestampdiff(hour,createtime,activate_time) hour_diff from t_consumer) w
inner join
(select count(1) tot from t_consumer) c
group by hour_diff
with rollup
) wc;
![](https://i-blog.csdnimg.cn/blog_migrate/8a3b2dbbf2bb11826ddd994ed1643f57.png)
3.对每行数据进行排序
对每行数据利用@rank参数从1开始累加即可排序
set @s=0;
set @rank=0;
select *,@s:=@s+pct cul_pct,@rank:=@rank+1 rank
from
(select
ifnull(hour_diff,'--合计--') hour_diff,count(1) cnt,count(1)/tot pct
from
(select timestampdiff(hour,createtime,activate_time) hour_diff from t_consumer) w
inner join
(select count(1) tot from t_consumer) c
group by
hour_diff
with rollup) wc;
![](https://i-blog.csdnimg.cn/blog_migrate/ceaaaf195e0b8d395c206892ef66ef3d.png)