mysql计算正态分布、差异系数、离均差、离均差率

1、数据准备　　建表　

drop table if exists score;
create table score(
    id int not null primary key auto_increment,
    score decimal(10,2) not null
);

　　插入数据

insert into score (score) values (89.4);
insert into score (score) values (82.5);
insert into score (score) values (65);
insert into score (score) values (98);
insert into score (score) values (92);
insert into score (score) values (34);
insert into score (score) values (59);
insert into score (score) values (83);
insert into score (score) values (88);
insert into score (score) values (63);
insert into score (score) values (100);
insert into score (score) values (96);
insert into score (score) values (68);
insert into score (score) values (56);

注意：计算标准差最少需要两个值

2、计算正态分布

计算方法是：平均分-标准差（四个值：μ-2σ，μ-σ，μ+σ，μ+2σ）

select round(score_avg - score_bzc * 2, 2) zt1,
       round(score_avg - score_bzc, 2)     zt2,
       round(score_avg + score_bzc, 2)     zt3,
       round(score_avg + score_bzc * 2, 2) zt4
from (select avg(score) score_avg, stddev_samp(score) score_bzc
      from score
      where id in (1, 2)) as a;

3、计算差异系数

 计算方法是：标准差/平均分（σ/μ）

select round(score_bzc/score_avg,2) cyxs
from (select avg(score) score_avg, stddev_samp(score) score_bzc
      from score
      where id in (1,2)) as a;

4、计算离均差

计算方法是：单个值-平均值（x-μ）

select round(score - (avg(score) over ()), 2) ljc
from score
where id in (1, 2);

5、计算离均差率

计算方法是：离均差/平均值（（x-μ）/μ）

select round((score - (avg(score) over ())) / (avg(score) over ()), 2) ljcl
from score
where id in (1, 2);