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;