Как сделать «оценку» в чистом (т.е. ANSI) SQL

0

У меня есть таблица, которая выглядит примерно так:

CREATE TABLE student_results (id integer, имя varchar (32), оценка float);

Давайте сделаем следующие два предположения:

  • предположим, что оценка от 0 до 100.
  • Предположим, что я хочу, чтобы ученики оценивали "размер шага" 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. Если мне нужно выбрать базу данных, то в порядке предпочтения DECREASING это должно быть:

  • 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
  • 1
    Можете ли вы опубликовать пример ожидаемого результата от такого запроса?
  • 0
    Вы спрашиваете, как сделать объединение, используя BETWEEN вместо = ?
Показать ещё 3 комментария
Теги:

4 ответа

1
Лучший ответ

Таким образом, вы можете сделать это более общее, но оценки будут в обратном порядке, начиная с 1 до N, т.е.

  • 0-10 Grade1
  • 10-20 Grade2
  • 20-30 Класс 3
  • 30-40 Класс4
  • ...

Например, используя значения этап 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';
Поделиться
1

Как насчет этого? (Оказывается, я использовал @steps, как и количество шагов, вместо @step. Если вы скорее укажете @step, @steps можно рассчитать как @steps = (@highest-@lowest)/@step

SET @lowest = 0;
SET @highest = 100;
SET @steps = 10;

SELECT
    name,
    CASE
        WHEN score >= (@highest-@steps) THEN 'GENIUS'
        ELSE 
            CONCAT(
                'GRADE',
                @steps-FLOOR((score-@lowest)/((@highest-@lowest)/@steps))-1)
    END
FROM
    student_results

Это даст вам новый класс, когда вы пройдете следующий шаг.

0-9.999   => GRADE1
10-19.999 => GRADE2
etc.
Поделиться
  • 0
    Я думаю, что вам нужно вычесть @step из @highest при тестировании на 'GENIUS'.
  • 0
    Я думаю, вы можете немного подправить его. Оценка 90 не дает GRADE1 (или я неправильно рассчитываю, что обычно бывает :-))
1

что-то вроде этого?

SELECT 
     [name],
     score,
     CASE 
          WHEN score > @max - @stepsize THEN 'GENIUS'
          ELSE CONCAT('GRADE',
                    CAST(
                         FLOOR((@max - score)/@stepsize - 
                              CASE score 
                                   WHEN @min THEN 1
                                   ELSE 0
                              END CASE
                         ) as char(3)
                    )
               )
     END CASE
FROM 
     student_results

вам, возможно, придется немного подкорректировать его - я не совсем понял минимальную часть (используется ли она только потому, что последний диапазон 1 размер больше других диапазонов?)

Edit

Переименован @step в @stepsize для ясности на Ivar (@step может быть неверно истолкован как количество шагов)

Поделиться
  • 0
    +1 - я считаю, что ваш SQL более стандартный, чем мой, тоже хороший случай
  • 0
    -1 Будет работать только для текущей шкалы оценок. Сбой, если @min! = 0, @step! = 10 или @max! = 100 ...
Показать ещё 3 комментария
1

Есть несколько вариантов:

1) создать таблицу с оценками (мин., макс.) и присоединиться к этой таблице

SELECT score, grades.grade
FROM table 
     INNER JOIN grades ON table.score >= grades.min AND table.score <= grades.max

2) создать временную таблицу (или даже выбрать из DUAL) и присоединиться к ней, например, в приведенном выше примере вместо вы можете написать подзапрос

(SELECT 0 as MIN, 10 as max, 'GRADE9' as grade FROM DUAL
 UNION ALL
 SELECT 11 as MIN, 20 as max, 'GRADE8' as grade FROM DUAL
 UNION ALL
 ...
 SELECT 91 as min, 100 as max, 'GENIUS' as grade FROM DUAL
 ) AS grades

3) использовать случай

SELECT score,
   CASE WHEN score = 0 THEN 'GRADE9'
        WHEN score >= 1 AND score <= 90 THEN 'GRADE' || (9 - (score-1) / 10)
        WHEN score >= 91 THEN 'GENIUS'
        ELSE 'ERROR'
   END grade
FROM table

(обратите внимание, что в приведенном выше запросе вы можете подставить 0, 100 и 10 с самым низким, самым высоким и шаг за шагом получить динамический sql)

4) создать пользовательскую функцию (но это будет связано с RDBMS)

Поделиться

Ещё вопросы

Сообщество Overcoder
Наверх
Меню