Oracle转MySQL存储函数percentile_cont(比例) WITHIN GROUP( ORDER BY to_number(分数) )用法

在Oracle中会用到
percentile_cont(比例) WITHIN GROUP( ORDER BY to_number(分数) )
的写法,但在MySQL中却没有percentile_cont函数用法,所以只能根据函数思想使用排序LIMIT改编算法(亲测,基本无误差)

Oracle

create function fn_get_grade(score float)
/**
** 五段分为法
** 大于84% 是A等级
** 小于等于84% 且 大于 63% 是B等级
** 小于等于63% 且 大于 37% 是C等级
** 小于等于37% 且 大于 16% 是D等级
** 小于等于16%             是E等级
**/
RETURN VARCHAR2 IS
  RESULT VARCHAR2(10);
  grade_a FLOAT;
  grade_b FLOAT;
  grade_c FLOAT;
  grade_d FLOAT;
begin
  SELECT percentile_cont(0.84) WITHIN GROUP( ORDER BY to_number(total_score) ) p  INTO grade_a
         FROM fb_unit_assessment_report r
  ;
  SELECT percentile_cont(0.63) WITHIN GROUP( ORDER BY to_number(total_score) ) p  INTO grade_b
         FROM fb_unit_assessment_report r
  ;
  SELECT percentile_cont(0.37) WITHIN GROUP( ORDER BY to_number(total_score) ) p  INTO grade_c
         FROM fb_unit_assessment_report r
  ;
  SELECT percentile_cont(0.16) WITHIN GROUP( ORDER BY to_number(total_score) ) p  INTO grade_d
         FROM fb_unit_assessment_report r
  ;
  IF score > grade_a THEN
    RESULT := 'A';
  ELSIF score <= grade_a AND score > grade_b THEN
    RESULT := 'B';
  ELSIF score <= grade_b AND score > grade_c THEN
    RESULT := 'C';
  ELSIF score <= grade_c AND score > grade_d THEN
    RESULT := 'D';
  ELSE
    RESULT := 'E';
  END IF;
  return(Result);
end fn_get_grade;
/

MySQL

create function fn_get_grade(score float) returns VARCHAR(10)
/**
** 五段分为法
** 大于84% 是A等级
** 小于等于84% 且 大于 63% 是B等级
** 小于等于63% 且 大于 37% 是C等级
** 小于等于37% 且 大于 16% 是D等级
** 小于等于16%             是E等级
**/
  
begin
 DECLARE RESULT VARCHAR(10);
	DECLARE a INT ;
	DECLARE b INT ;
	DECLARE c INT ;
	DECLARE d INT ;
	
	DECLARE grade_a FLOAT;
    DECLARE grade_b FLOAT;
    DECLARE grade_c FLOAT;
    DECLARE grade_d FLOAT;
  

 SELECT CAST(ROUND(COUNT(total_score)*(1-0.84)+1)AS signed) INTO a FROM fb_unit_assessment_report r ;
 SELECT total_score  INTO grade_a FROM fb_unit_assessment_report r ORDER BY total_score DESC LIMIT a,1;

 SELECT CAST(ROUND(COUNT(total_score)*(1-0.63)+1)AS signed) INTO b FROM fb_unit_assessment_report r ;
 SELECT total_score  INTO grade_b FROM fb_unit_assessment_report r ORDER BY total_score DESC LIMIT b,1;

 SELECT CAST(ROUND(COUNT(total_score)*(1-0.37)+1)AS signed) INTO c FROM fb_unit_assessment_report r ;
 SELECT total_score  INTO grade_c FROM fb_unit_assessment_report r ORDER BY total_score DESC LIMIT c,1;

 SELECT CAST(ROUND(COUNT(total_score)*(1-0.16)+1)AS signed) INTO d FROM fb_unit_assessment_report r ;
 SELECT total_score  INTO grade_d FROM fb_unit_assessment_report r ORDER BY total_score DESC LIMIT d,1;
 
	IF score > grade_a THEN
    set RESULT = 'A';
  ELSEIF score <= grade_a AND score > grade_b THEN
    set RESULT = 'B';
  ELSEIF score <= grade_b AND score > grade_c THEN
    set RESULT = 'C';
  ELSEIF score <= grade_c AND score > grade_d THEN
    set RESULT = 'D';
  ELSE
    set RESULT = 'E';
  END IF;
  return(RESULT);
end 


  • 4
    点赞
  • 6
    收藏
    觉得还不错? 一键收藏
  • 2
    评论
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值