mysql分组后占比、累计占比和排序计算方法

1.求group by之后和的占比先做group by分组汇总，再单独inner join连接到和，最后分组结果除以连接到的和selectifnull(hour_diff,'--合计--') hour_diff,count(1) cnt,count(1)/tot pctfrom(select timestampdiff(hour,createtime,activate_time) hour_dif

gao_vip

9074人浏览 · 2022-02-16 15:35:21

gao_vip · 2022-02-16 15:35:21 发布

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;

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;

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;