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

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

 

 

 

 

posted @   左叔  阅读(1104)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· 清华大学推出第四讲使用 DeepSeek + DeepResearch 让科研像聊天一样简单!
· 实操Deepseek接入个人知识库
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
点击右上角即可分享
微信分享提示