Как включить строку в запрос GROUP BY в MySQL?
У меня есть таблица для записи оценки, полученной учеником по предмету в конкретную дату. В таблице perf есть столбцы «id», «дата», «студент», «тема», «отметка».
Цель: я хочу сравнить оценку, полученную учеником, с самой высокой, самой низкой и средней оценкой класса по конкретному предмету за каждый день, используя один запрос, например:
SELECT date,mark,highest,lowest,avg,student FROM ....
.... и получите такой результат:
Как мне достичь своей цели в одном заявлении?
Я открыт для промежуточных шагов, например, создания представлений.
Я использую MySQL 8.0.33.
Вот данные:
Я могу получить высшую, низшую и среднюю оценку класса, используя GROUP BY следующим образом:
SELECT date,subject,MAX(mark) AS highest, MIN(mark) AS lowest, FORMAT(AVG(mark),0) AS avg FROM perf GROUP BY date,subject
Попытка включить оценку и ученика в приведенный выше запрос дает бессмысленные результаты:
SELECT date,subject,mark,MAX(mark) AS highest, MIN(mark) AS lowest, FORMAT(AVG(mark),0) AS avg,student FROM perf GROUP BY date,subject,mark,student
Я пробовал несколько вещей, но мне удалось объединить оценку, высшую, самую низкую, среднюю, студентку в один результат, когда я указываю студента, предмет и дату следующим образом, но это слишком специфично и не то, что я хочу:
SELECT (SELECT date FROM perf WHERE student='2c3' AND subject='Art' AND date='2021-09-07') AS date,
(SELECT mark FROM perf WHERE student='2c3' AND subject='Art' AND date='2021-09-07') AS mark,
(SELECT MAX(mark) FROM perf WHERE subject='Art' AND date='2021-09-07') AS highest,
(SELECT MIN(mark) FROM perf WHERE subject='Art' AND date='2021-09-07') AS lowest,
(SELECT FORMAT(AVG(mark),0) FROM perf WHERE subject='Art' AND date='2021-09-07') AS avg
Ответы 4
Доказательство работы Ввод и вывод GitHub из компилятора SQL Online: https://gist.github.com/xentzenith/ccdcc3e11f377195645cf2db8a4dccf0
Для достижения цели сравнения оценки, полученной учащимся, с самой высокой, самой низкой и средней оценкой класса по конкретному предмету за каждый день, вы можете использовать комбинацию оконных функций и подзапросов.
WITH stats AS (
SELECT
date,
subject,
MAX(mark) OVER (PARTITION BY date, subject) AS highest,
MIN(mark) OVER (PARTITION BY date, subject) AS lowest,
AVG(mark) OVER (PARTITION BY date, subject) AS avg
FROM perf
)
SELECT
p.date,
p.mark,
s.highest,
s.lowest,
ROUND(s.avg, 0) AS avg,
p.student
FROM perf p
JOIN stats s ON p.date = s.date AND p.subject = s.subject
WHERE p.mark = s.mark;
CTE статистики вычисляет самые высокие, самые низкие и средние оценки для каждого предмета на каждую дату, используя оконные функции (MAX(), MIN(), AVG()). Предложение PARTITION BY указывает группу строк, к которым применяется агрегатная функция. Это должно работать для вас, если вы используете соответствующую версию SQL :)
Выдает ошибку 1054 — Неизвестный столбец «s.mark» в пункте «where». Удаление строки «ГДЕ p.mark = s.mark» убирает ошибку. Однако полученное решение возвращает повторяющиеся строки. Изменение «ВЫБРАТЬ дату, тему, ...» на «ВЫБРАТЬ ОТЛИЧНЫЕ дату, тему, ...» удаляет дубликаты. Судя по всему, решение на вашем GitHub не выдает ошибку 1054, хотя все равно возвращает дубликаты.
Используйте CTE, чтобы вычислить общие значения и присоединиться к ним:
with cte as (
SELECT
date,
subject,
MAX(mark) AS highest,
MIN(mark) AS lowest,
FORMAT(AVG(c.mark), 0) AS avg
FROM perf
GROUP BY date, subject
)
SELECT
c.date,
c.subject,
p.mark,
c.highest,
c.lowest,
c.avg,
p.student
FROM perf p
JOIN cte c ON c.date = p.date
AND c.subject = p.subject
Спасибо. Выдает ошибку 1054 — Неизвестный столбец «c.mark» в «списке полей». Отлично работает при изменении «c.mark» на «mark» в списке полей и удалении «WHERE c.mark = p.mark»
Возможно, вы можете использовать оконную функцию mysql8, она может собирать данные из нескольких измерений в одной строке.
SELECT
p.*,
MAX(p.mark) over(PARTITION BY p.date, p.subject) AS highest,
MIN(p.mark) over(PARTITION BY p.date, p.subject) AS lowest,
format(AVG(p.mark) over(PARTITION BY p.date, p.subject), 0) AS AVG
FROM perf p
результат:
Это простое использование оконных функций. может помочь тебе.
Аккуратный. Работает идеально. Однако реализация, похоже, занимает вдвое больше времени, чем решения, представленные здесь до сих пор.
SELECT
a.DATE,
b.lowest,
b.highest,
b.avg,
a.student,
a.mark
FROM perf a
LEFT JOIN (
SELECT
DATE,
SUBJECT,
MIN(mark) AS lowest,
MAX(mark) AS highest,
AVG(mark) AS AVG
FROM perf
GROUP BY DATE,SUBJECT ) b ON a.date = b.date AND a.subject = b.subject
Работает идеально. Спасибо.
Пожалуйста, отредактируйте свой вопрос и постарайтесь отформатировать его так, чтобы его можно было прочитать. Ваши операторы SQL должны быть отформатированы как код, а данные должны быть в таблицах. Вы можете получить помощь по форматированию, нажав
?на панели инструментов во время редактирования.