早教吧百科知识频道 --> IT百科 -->编程开发 -->

mysql求P30、P50、P70

场景/问题/来源

mysql数据库

mysql如何求不同的百分位数

知识/回答

话不多说，直接上代码，示例如下：

select

round(avg(case

when ((kk*0.3 -floor(kk*0.3))>0 and r= floor(kk*0.3)+1) then d.pp

when ((kk*0.3 -floor(kk*0.3))=0 and r between floor(kk*0.3) and floor(kk*0.3)+1) then d.pp end),2) as p30,

round(avg(case

when ((kk*0.5 -floor(kk*0.5))>0 and r= floor(kk*0.5)+1) then d.pp

when ((kk*0.5 -floor(kk*0.5))=0 and r between floor(kk*0.5) and floor(kk*0.5)+1) then d.pp end),2) as p50,

round(avg(case

when ((kk*0.7 -floor(kk*0.7))>0 and r= floor(kk*0.7)+1) then d.pp

when ((kk*0.7 -floor(kk*0.7))=0 and r between floor(kk*0.7) and floor(kk*0.7)+1) then d.pp end),2) as p70

from (

select a.pp,

row_number() over (order by a.pp) as r,

sum(1) over()

from test_table a

) d

与mysql求P30、P50、P...相似的小知识：

模糊搜索： P50 P70 mysql求P30