mysql如何分組統(tǒng)計并求出百分比

更新時間：2022年10月11日 16:59:11 作者：木木-木

這篇文章主要介紹了mysql如何分組統(tǒng)計并求出百分比，具有很好的參考價值，希望對大家有所幫助。如有錯誤或未考慮完全的地方，望不吝賜教

mysql分組統(tǒng)計并求出百分比

1、mysql 分組統(tǒng)計并列出百分比

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;

--查出符合條件并且分組，統(tǒng)計出每組數(shù)量

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

--查出符合條件，總數(shù)量

(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;