常见统计分析—按条件统计绝对数量及相对占比

在常见的业务场景中经常会遇到分类统计的问题，如下表所示学生分数，需求：

1.取得不同分数段学生的绝对数量（优良中差数量）

2.取得不同分数段学生的相对情况（优良中差占比）

表名：score

sid cid score

01  01  80

01  02  90

01  03  99

02  01  70

02  02  60

02  03  80

03  01  80

03  02  80

03  03  80

04  01  50

04  02  30

04  03  20

05  01  76

05  02  87

06  01  31

06  03  34

07  02  89

07  03  98

(1)考察点： case when 函数使用

select
    *,
    (
    case
        when score < 60 then 'fail' # 注意此处不需要加‘else’，推测‘else’应该是在最后一个条件前加，否则报错
        when score >= 60 and score < 85 then 'pass' else # 此处需要加‘else’或者下面继续用‘when...then'
        'good'
    end
    )as 'rank'
from
    score;

查询结果如下：

sid cid score rank
01  01  80  pass
01  02  90  good
01  03  99  good
02  01  70  pass
02  02  60  pass
02  03  80  pass
03  01  80  pass
03  02  80  pass
03  03  80  pass
04  01  50  fail
04  02  30  fail
04  03  20  fail
05  01  76  pass
05  02  87  good
06  01  31  fail
06  03  34  fail
07  02  89  good
07  03  98  good

（2）考察点：复杂查询—多表非连接查询
简单原表：

sid cid score
01  01  80
01  02  90
01  03  99
02  01  70
02  02  60

求各score占比：
a)该表中分数（score）各不相同，因此不需用到分组函数

SELECT
    a.score,
    concat( round( a.score / b.total * 100, 2 ), ' %' ) AS percent # 连接-四舍五入-计算占比
FROM
    ( SELECT * FROM score LIMIT 5 ) a, # a表为原始的待计算数据表
    ( SELECT sum( score ) total FROM ( SELECT * FROM score LIMIT 5 ) a ) b # b表为计算占比的总和参数
ORDER BY
    score DESC;

score percent
99  24.81 %
90  22.56 %
80  20.05 %
70  17.54 %
60  15.04 %

b)该表中分数（score）有相同，因此需用到分组求和函数

SELECT
    tt.rank,
    concat(round(tt.rank_num / b.total * 100,2),' %') as percent 
FROM
    (
SELECT
    t.rank,
    count( * ) rank_num 
FROM
    (
SELECT
    *,
    ( CASE WHEN score < 60 THEN 'fail' # 注意此处不需要加‘else’，推测‘else’应该是在最后一个条件前加，否则报错
    WHEN score >= 60 AND score < 85 THEN 'pass' ELSE # 此处需要加‘else’或者下面继续用‘when...then'
    'good' END ) AS 'rank' 
FROM
    score 
    ) t 
GROUP BY
    t.rank 
    ) tt,
    (
SELECT
    sum( rank_num ) total 
FROM
    (
SELECT
    t.rank,
    count( * ) rank_num 
FROM
    (
SELECT
    *,
    ( CASE WHEN score < 60 THEN 'fail' # 注意此处不需要加‘else’，推测‘else’应该是在最后一个条件前加，否则报错
    WHEN score >= 60 AND score < 85 THEN 'pass' ELSE # 此处需要加‘else’或者下面继续用‘when...then'
    'good' END ) AS 'rank' 
FROM
    score 
    ) t 
GROUP BY
    t.rank 
    ) tt 
    ) b;

rank percent
fail    27.78 %
good    27.78 %
pass    44.44 %

该方法可行，但过程繁琐，调用了两次原始表。
c)简单方式

set @sum = (select sum(score) from score); # 定义一个参数即可
select score,concat(round(score / @sum * 100,2),'%') as percent from score;

常见统计分析—按条件统计绝对数量及相对占比

推荐阅读更多精彩内容