Найти для каждого ученика предмет с лучшим средним баллом за задания
Есть таблица TASKS.
STUDENT_ID COURSE_ID DATE TASK_NR FILE_NAME POINTS TASK_ID
---------- --------- -------- ---------- -------------------- ---------- ----------
10020011 I319 17.06.03 1 t3.txt 8 3
10020014 I319 01.06.03 1 t7.txt 10 4
10020015 I319 11.07.03 1 t8.txt 10 5
10020011 I319 11.06.04 2 t2.txt 7 6
10020015 I319 11.06.04 3 t2.txt 6 7
10020013 I319 11.06.04 3 t3.txt 8 8
10020011 Iad307 11.06.03 1 t1.txt 5 9
10020011 Id310 12.06.03 1 t2.txt 7 10
10020011 ID218 17.06.03 1 t3.txt 8 11
10020014 Iad307 01.06.03 1 t7.txt 10 12
10020015 Iad307 11.07.03 1 t8.txt 10 13
Суть задачи - из таблицы TASKS получить для каждого ученика, к какому предмету его среднее количество баллов за задания лучшее.
Выглядеть должно так
STUDENT_ID COURSE_ID AVG
---------- --------- ----------
10020011 ID218 8
10020013 ID218 9,5
10020014 I319 10
10020015 Iad307 10
Для начала я нахожу средний результат по баллам каждого предмета для каждого ученика
SELECT student_id, course_id, AVG(points) AS avg FROM tasks GROUP BY student_id, course_id
ORDER BY student_id;
STUDENT_ID COURSE_ID AVG
---------- --------- ----------
10020011 Id310 7
10020011 Iad307 5,75
10020011 I319 5
10020011 ID218 8
10020013 ID218 9,5
10020013 I319 8
10020014 I319 10
10020014 Iad307 6,25
10020014 ID218 6
10020015 Id309 5,5
10020015 I319 8
Далее я хочу получить ид ученика, ид курса и максимальное значение баллов по этому курсу для каждого ученика
SELECT student_id, course_id, MAX(avg) AS max FROM (
SELECT student_id, course_id, AVG(points) AS avg FROM tasks GROUP BY student_id, course_id
) GROUP BY student_id ORDER BY student_id;
Получаю ошибку
ORA-00979: выражение не является выражением GROUP BY 00979. 00000 - "not a GROUP BY expression"
Пробовал вариант
SELECT student_id, ANY_VALUE(course_id), MAX(avg) AS max FROM (
SELECT student_id, course_id, AVG(points) AS avg FROM tasks GROUP BY student_id, course_id
) GROUP BY student_id ORDER BY student_id;
Получаю ошибку
ORA-00904: "ANU_VALUE": недопустимый идентификатор 00904. 00000 - "%s: invalid identifier"
Есть ли альтернатива ANY_VALUE или возможно другой способ решения задачи?
Ответы (1 шт):
Попробовал воспроизвести на http://sqlfiddle.com/.
Суть проблемы - в выборе значения из поля course_id, которое соответствует максимальному баллу из средних. Решить удалось двумя способами трехэтажными SELECT с помощью 1) RANK() и 2) row_number(). На мой взгляд, вариант № 1 более правильный, чем № 2, поскольку выводит все результаты для одного и того же студента, если максимальные баллы равны.
Исходные данные (Build Schema):
CREATE TABLE tasks
("STUDENT_ID" int, "COURSE_ID" varchar2(6), "DATE" varchar2(8), "TASK_NR" int, "FILE_NAME" varchar2(6), "POINTS" int, "TASK_ID" int)
;
INSERT ALL
INTO tasks ("STUDENT_ID", "COURSE_ID", "DATE", "TASK_NR", "FILE_NAME", "POINTS", "TASK_ID")
VALUES (10020011, 'I319', '17.06.03', 1, 't3.txt', 8, 3)
INTO tasks ("STUDENT_ID", "COURSE_ID", "DATE", "TASK_NR", "FILE_NAME", "POINTS", "TASK_ID")
VALUES (10020014, 'I319', '01.06.03', 1, 't7.txt', 10, 4)
INTO tasks ("STUDENT_ID", "COURSE_ID", "DATE", "TASK_NR", "FILE_NAME", "POINTS", "TASK_ID")
VALUES (10020015, 'I319', '11.07.03', 1, 't8.txt', 10, 5)
INTO tasks ("STUDENT_ID", "COURSE_ID", "DATE", "TASK_NR", "FILE_NAME", "POINTS", "TASK_ID")
VALUES (10020011, 'I319', '11.06.04', 2, 't2.txt', 7, 6)
INTO tasks ("STUDENT_ID", "COURSE_ID", "DATE", "TASK_NR", "FILE_NAME", "POINTS", "TASK_ID")
VALUES (10020015, 'I319', '11.06.04', 3, 't2.txt', 6, 7)
INTO tasks ("STUDENT_ID", "COURSE_ID", "DATE", "TASK_NR", "FILE_NAME", "POINTS", "TASK_ID")
VALUES (10020013, 'I319', '11.06.04', 3, 't3.txt', 8, 8)
INTO tasks ("STUDENT_ID", "COURSE_ID", "DATE", "TASK_NR", "FILE_NAME", "POINTS", "TASK_ID")
VALUES (10020011, 'Iad307', '11.06.03', 1, 't1.txt', 5, 9)
INTO tasks ("STUDENT_ID", "COURSE_ID", "DATE", "TASK_NR", "FILE_NAME", "POINTS", "TASK_ID")
VALUES (10020011, 'Id310', '12.06.03', 1, 't2.txt', 7, 10)
INTO tasks ("STUDENT_ID", "COURSE_ID", "DATE", "TASK_NR", "FILE_NAME", "POINTS", "TASK_ID")
VALUES (10020011, 'ID218', '17.06.03', 1, 't3.txt', 8, 11)
INTO tasks ("STUDENT_ID", "COURSE_ID", "DATE", "TASK_NR", "FILE_NAME", "POINTS", "TASK_ID")
VALUES (10020014, 'Iad307', '01.06.03', 1, 't7.txt', 10, 12)
INTO tasks ("STUDENT_ID", "COURSE_ID", "DATE", "TASK_NR", "FILE_NAME", "POINTS", "TASK_ID")
VALUES (10020015, 'Iad307', '11.07.03', 1, 't8.txt', 10, 13)
SELECT * FROM dual
;
SQL-запрос №1 - сначала усредняем баллы как в оригинальном коде, потом их ранжируем в обратном порядке с помощью RANK() (с рангом 1 самые большие значения), затем отбираем записи со значением ранга = 1. При этом могут появиться множественные значения для одного и того же student_id, если максимальные средние баллы по разным курсам равны:
SELECT student_id, course_id, sred FROM (
SELECT student_id, course_id, sred, RANK() OVER (PARTITION BY student_id ORDER BY sred DESC) as rank_sred FROM (
SELECT student_id, course_id, AVG(points) AS sred FROM tasks GROUP BY student_id, course_id
) GROUP BY student_id,course_id, sred ORDER BY student_id) WHERE RANK_SRED=1;
Результат 1 (студент № 14 в двух экземплярах с баллами 10 по двум курсам):
STUDENT_ID COURSE_ID SRED
10020011 ID218 8
10020013 I319 8
10020014 I319 10
10020014 Iad307 10
10020015 Iad307 10
Второй способ - через row_number() - выбирает только первый результат из группы:
SELECT student_id, course_id, sred FROM (
SELECT student_id, course_id, sred, row_number() over (partition by student_id order by sred DESC) as row_n FROM (
SELECT student_id, course_id, AVG(points) AS sred FROM tasks GROUP BY student_id, course_id
) GROUP BY student_id,course_id, sred ORDER BY student_id) WHERE row_n=1;
Результат № 2 (студент № 14 в одном экземпляре)
STUDENT_ID COURSE_ID SRED
10020011 ID218 8
10020013 I319 8
10020014 I319 10
10020015 Iad307 10
Для понимания правильного результата - разобранные данные в Excel
