MySQL 2023-02-25

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;