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
|
