mysql分组统计并求出百分比
1、mysql 分组统计并列出百分比
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
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; |
--查出符合条件并且分组， 统计出每组数量
|
1 2 3 4 5 6 |
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 |
--查出符合条件，总数量
|
1 2 3 4 5 |
(SELECT sum( `play` ) AS total FROM `dt_collect_antique` WHERE`add_time` BETWEEN '2020-07-02' AND '2020-07-05' ) t2 |
2、按年龄段分组，并求个年龄段占比
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
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
