mysql - quartile 和 StDev 在 SQL 中怎么写？

Question

我有这样的表:

表名:pelamarmagisterrangkuman

然后我做了这样的 sql 来查找平均值、最小值、最大值

select `pelamarmagisterrangkuman`.`major` AS `ProgramStudi`,
count(usia) as N,
AVG(usia) as Mean,
MIN(usia) as Minimum,
MAX(usia) as Maximum
from `pelamarmagisterrangkuman` group by `pelamarmagisterrangkuman`.`major`

结果是这样的:

现在想求quartile和stdev值，但是不知道sql怎么写。有人可以帮我解决这个问题吗？提前致谢

Answer 1

标准偏差很容易:

select p.major AS ProgramStudi,
       count(usia) as N, AVG(usia) as Mean,
       MIN(usia) as Minimum, MAX(usia) as Maximum,
       STDDEV(usia)
from `pelamarmagisterrangkuman` p
group by p.major;

四分位数要难得多。我认为最可靠的方法可能是使用变量:

select p.major AS ProgramStudi,
       count(usia) as N, AVG(usia) as Mean,
       MIN(usia) as Minimum, MAX(usia) as Maximum,
       STDDEV(usia) as stddev,
       max(case when floor(0.25*pp.cnt) = rn then usia end) as quartile_1,
       max(case when floor(0.50*pp.cnt) = rn then usia end) as quartile_2,
       max(case when floor(0.75*pp.cnt) = rn then usia end) as quartile_3
from (select p.*,
             (@rn := if(@m = p.major, @rn + 1,
                        if(@m := p.major, 1, 1)
                       )
             ) as rn
      from pelamarmagisterrangkuman p cross join
           (select @m := '', @rn := 0) params
      order by p.major, p.usia
     ) p join
     (select p.major, count(*) as cnt
      from pelamarmagisterrangkuman p
      group by p.major
     ) pp
     on p.major = pp.major
group by p.major;