sql - 如何在纯(即 ANSI)SQL 中执行 'grading'

Question

我有一个看起来像这样的表:

CREATE TABLE student_results(id integer, name varchar(32), score float);

让我们做以下两个假设:

1. 假设分数从 0 到最大值 100。
2. 假设我想以 10 的“步长”给学生评分

所以我想应用以下评分:

Score      Grade Awarded
0-10       GRADE9
10-20      GRADE8
20-30      GRADE7
30-40      GRADE6
40-50      GRADE5
50-60      GRADE4
60-70      GRADE3
70-80      GRADE2
80-90      GRADE1
99-100     GENIUS

我想编写一个接受以下输入参数的 SQL 查询:

lowest score:  0 in this example
highest score: 100 in this example
'step' size:   10 in this example

与以往一样，如果可能的话，我想使用 ANSI SQL 编写这样的查询。如果我必须选择一个数据库，那么按照优先级递减的顺序，它必须是:

• PostgreSQL
• MySQL

有人可以使用上表作为示例来解释我如何能够编写执行这种分级的 SQL 查询吗？

[编辑]

Sample input data

1, 'homer', 10.5
2. 'santas little helper', 15.2
3, 'bart',  20.5
4, 'marge', 40.5
5. 'lisa', 100

我将有一个 SQL 函数 grade_rank() - 对学生进行排名:

函数 grade_rank() 的参数是:

1st argument: LOWEST possible score value
2nd argument: HIGHEST possible score value
3rd argument: step size, which determines the levels/divisions between the ranks

select id, name, grade_rank(0,100, 10) grade from student_scores;

输出(基于上面的输入)应该是:

1, homer,               GRADE9
2. santas liitle helper GRADE9
3, bart,                GRADE8
4, marge,               GRADE6
5. lisa,                GENIUS

Answer 1

通过这种方式你可以做的更一般但是等级会倒序，从1到N，即

• 0-10 年级
• 10-20 年级
• 20-30 三年级
• 30-40 年级
• ...

例如使用值第 10 步得分 43

这个算法

SELECT (((score-1)-((score-1) % step))/step)+1

将返回 5

您不必知道最高分。如果最高分是 100 分，则没有人能够执行高于 100 分的成绩，您只需要决定步数的大小即可。例如，如果您想要步长为 25。知道最高分数为 100，将有 4 个等级。因此，通过将步进级别设置为 25 而不是 10，结果将为 2，即等级 2。

SELECT (((43-1)-((43-1) % 25))/25)+1

也许不符合您的预期，但可能足够通用以提供有用的信息。下面是该函数在 SQL 中的样子。

CREATE OR REPLACE FUNCTION grade_rank(IN score integer, IN step integer, OUT rank integer)
AS 'SELECT ((($1-1)-(($1-1) % $2))/$2)+1'
LANGUAGE 'SQL';

现在调用这个函数

select * from grade_rank(43,10)

返回 5。

这是 plpgsql 的等价物:

CREATE OR REPLACE FUNCTION grade_rank(IN score integer, IN step integer)
  RETURNS integer AS
$BODY$ 
DECLARE rank integer;
BEGIN
    SELECT (((score-1)-((score-1) % step))/step)+1 INTO rank;
    RETURN rank;
END;
$BODY$
  LANGUAGE 'plpgsql';