mysql分组统计并求出百分比
1、mysql 分组统计并列出百分比
select point_id, pname_cn, play_num, round( play_num / total * 100, 2 ) as `ratio` from ( select * from ( select any_value ( `point_id` ) as point_id, any_value ( `pname_cn` ) as pname_cn, sum( `play` ) as play_num from `dt_collect_antique` where`add_time` between '2020-07-02' and '2020-07-05' group by `point_id` ) t1 inner join ( select sum( `play` ) as total from `dt_collect_antique` where`add_time` between '2020-07-02' and '2020-07-05' ) t2 on 1 = 1 ) t order by `play_num` desc limit 0, 10;
--查出符合条件并且分组, 统计出每组数量
select any_value ( `point_id` ) as point_id, any_value ( `pname_cn` ) as pname_cn, sum(`like`) as like_num from `dt_collect_antique` where`add_time` between '2020-07-02' and '2020-07-05' group by `point_id` ) t1
--查出符合条件,总数量
(select sum( `play` ) as total from `dt_collect_antique` where`add_time` between '2020-07-02' and '2020-07-05' ) t2
2、按年龄段分组,并求个年龄段占比
select age_group, age_total, round( age_total / total * 100, 2 ) as `ratio` from ( select * from ( select sum(total) as age_total, case when age >= 0 and age < 18 then '18岁以下' when age >= 18 and age <= 25 then '18岁到25岁' when age >= 26 and age <= 35 then '26岁到35岁' when age >= 36 and age <= 45 then '36岁到45岁' when age >= 46 and age <= 60 then '46岁到60岁' else '60岁以上' end as age_group from dt_collect_age where `add_time` between ".$time[0]." and ".$time[1]." group by age_group ) t1 inner join ( select sum( `total` ) as total from `dt_collect_age` where `add_time` between ".$time[0]." and ".$time[1]." ) t2 on 1 = 1 ) t limit 0, 6;
mysql求百分比的几种方法
函数介绍
1、round(x,d)和round(x)
round函数用于数据的四舍五入,x指要处理的数,d是指保留几位小数
round(x) ,其实就是 round(x,0)
d可以是负数,代表指定小数点左边的d位整数位为0,同时小数位均为0
2、format(x,d)
提供数据内容格式化功能,可以格式化数据为整数或者浮点数,能四舍五入
d为负数时,按0处理
3、left(str,len)
left函数是一个字符串函数,它返回具有指定长度的字符串的左边部分,str为要处理的字符串,len为长度
left函数为字符串截取,不能四舍五入
4、concat(str1,str2,...)
concat函数用于将多个字符串连接成一个字符串
利用round,format,left与concat求百分比
注意:使用left按位截取百分比时,位数要根据需要合理设置,否则容易出现意外bug
以上为个人经验,希望能给大家一个参考,也希望大家多多支持。