mysql分组后占比、累计占比和排序统计各年龄段人数

原文链接：https://blog.csdn.net/weixin_41233157/article/details/122962747

https://blog.51cto.com/u_15152248/2680714?abTest=51cto

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;
1
2
3
4
5
6
7
8
9

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;
1
2
3
4
5
6
7
8
9
10
11
12

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;
1
2
3
4
5
6
7
8
9
10
11
12
13

以表结构字段为id，birthday为例统计各年龄段人数：

SELECT
z.age AS NAME,
count( z.age ) AS 
VALUE
FROM
(
SELECT
CASE WHEN p.age < 12 THEN '0~12' 
WHEN p.age < 18 THEN '13~18' 
WHEN p.age <= 30 THEN '19~30' 
WHEN p.age <= 50 THEN'31~50' 
ELSE '50+' 
END AS age 
FROM
(SELECT extract( YEAR FROM CURDATE( ) ) - IFNULL( FROM_UNIXTIME( IFNULL( birthday, 0 ), '%Y-%m-%d' ), CURDATE( ) ) AS age 
FROM at_b ) p 
) z 
GROUP BY
z.age
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
执行结果如下：