mysql如何分组统计并求出百分比_Mysql

mysql如何分组统计并求出百分比

2022-11-24 15:55木木-木 Mysql

这篇文章主要介绍了mysql如何分组统计并求出百分比，具有很好的参考价值，希望对大家有所帮助。如有错误或未考虑完全的地方，望不吝赐教

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;