mysql如何分组统计并求出百分比_数据库_开发者

开发者 https://www.devze.com 2022-12-12 09:59 出处：网络作者：木木-木

目录mysql分组统计并求出百分比1、mysql 分组统计并列出百分比2、按年龄段分组，并求个年龄段占比mysql求百分比的几种方法函数介绍利用round,format,left与concat求百分比mysql分组统计并求出百分比

mysql分组统计并求出百分比

1、mysql 分组统计并列出百分比

SELECT
 point_id,
 pname_cn,
 play_num,
 round( play_num / totaandroidl * 100, 2php ) 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开发者_DB2` ) 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` WHER编程客栈E `add_time` BETWEEN ".$time[0]." AND ".$time[1]."
      ) t2 ON 1 = 1
   ) t
  LIMIT 0, 6;