Redshift SQL计算高校学生通过率 混合类型成绩字段处理问题

问题根因 #

• 原查询逻辑错误：将规则要求的或关系写成了且，要求同时满足分数大于50和等级不在不合格列表，和需求不符
• 字段类型不兼容：grade是混合存储的字符串类型，直接和数字50比较会触发隐式类型转换，要么执行报错，要么得到非预期结果

修复后查询 #

select 
  uni,
  count(case when 
    (isnumeric(grade) = 1 and grade::numeric > 50) 
    or 
    (isnumeric(grade) = 0 and grade not in ('D+', 'D', 'D-', 'F'))
  then 1 end) as passed,
  count(*) as total,
  round((passed::float / total) * 100, 2) as pass_ratio
from analytics.students_fact
where 
  uni in ('uni1', 'uni2', 'uni3')
  and grade <> 'Unknown'
group by uni
order by uni;

关键说明 #

• 用Redshift内置的isnumeric()函数判断grade是否为有效数字：是则转为数值类型判断是否大于50，不是则直接判断是否属于不合格的字母等级
• 用group by uni替代原有的三次union写法，只需扫描一次表，执行效率更高
• 计算通过率时先将passed转为float类型，避免Redshift整数除法导致结果取整的问题，round函数可自定义保留的小数位数
• 如果你使用的Redshift版本不支持isnumeric，可以替换为正则匹配逻辑：regexp_like(grade, '^\\d+(\\.\\d+)?$')，兼容整数和小数形式的数字成绩

内容的提问来源于stack exchange，提问作者Estrobelai