Вопрос:
Сложный SQL-запрос нужен здесь, но не уверен, что это возможно.
У меня есть таблица вроде этого:
———————— |uid |lesson_id |score | ———————— |1 |0 |20 | |1 |0 |25 | |1 |0 |15 | |1 |0 |40 | |1 |1 |70 | |1 |0 |10 | |1 |1 |20 | |1 |1 |55 | |1 |1 |55 | |1 |0 |5 | |1 |2 |65 | ————————
Для конкретного пользователя мне нужно получить максимальный балл для каждого урока, суммировать их, а затем получить их среднее… все в одном SQL-запросе.
Макс. Оценки:
———————— |lesson_id |max_score | ———————— |0 |40 | |1 |70 | |2 |65 | ————————
Сумма:
—— |sum | —— |175 | ——
В среднем:
——- |avg | ——- |58.3 | ——-
Как это?
SELECT uid AS id, AVG(max_score) AS avg_score FROM ( SELECT uid, lesson_id, max(score) AS max_score FROM cdu_user_progress WHERE session_id = $session_id GROUP BY uid, lesson_id ) AS tt WHERE uid IN ($ids) GROUP BY uid ORDER BY avg_score Лучший ответ:
Вы можете просто использовать подзапросы:
SELECT uid, SUM(MaxScore), Avg(MaxScore) FROM ( SELECT uid, lesson_id,max(score) as MaxScore FROM scores GROUP BY userid, lesson_id ) AS m GROUP BY uid
или только для одного пользователя:
SELECT SUM(MaxScore), Avg(MaxScore) FROM ( SELECT lesson_id,max(score) as MaxScore FROM scores WHERE uid = $uid GROUP BY lesson_id ) AS m
или для “группы конкретных пользователей”:
$uidlist = (comma-delimited list of user ids) SELECT uid, SUM(MaxScore), Avg(MaxScore) FROM ( SELECT uid, lesson_id,max(score) AS MaxScore FROM scores WHERE FIND_IN_SET(uid, $uidlist) GROUP BY uid, lesson_id ) AS m GROUP BY uid Ответ №1
Вот простой пример SQLFIDDLE
select Avg(tt.maxscores) from ( select lesson_id,max(score) as maxscores from t group by lesson_id ) as tt Ответ №2SELECT AVG( s.scores ) AS avarage, SUM( s.scores ) AS total FROM ( SELECT lesson_id, MAX( score ) AS scores FROM ‘tblScores’ GROUP BY lesson_id ) AS s Ответ №3
Вы можете легко сделать это в одном SQL-запросе, хотя вам понадобится использовать более одного оператора.
Наиболее легко, вы можете сделать это через подзапрос, хотя тот же результат можно сделать с помощью временной таблицы или Common-Table-Expression.
(Также обратите внимание, что функция SQL AVG уже суммирует и делит результаты по их счету. Вы не хотите делать AVG(SUM(X)), так как это приведет к бессмысленной фигуре.) В любом случае, на пример:
Сначала напишите выражение SQL, чтобы получить элементы, которые вы будете усреднять, например максимальный балл для каждого теста для каждого пользователя.
SELECT MAX(Score) As MaxScore, uid, lesson_id FROM table GROUP BY uid, lesson_id
Затем вы обрабатываете вышеуказанный SELECT как таблицу и делаете второй выбор вокруг него, чтобы получить средний балл за uid.
SELECT AVG(MaxScore) as Final, uid FROM ( SELECT MAX(Score) As MaxScore, uid, lesson_id FROM table GROUP BY uid, lesson_id ) AS subQuery GROUP BY uid
Вы можете легко включить дополнительные функции агрегации с помощью этого метода, и, поскольку он довольно стандартный, он будет работать на большинстве SQL-серверов. Однако, в зависимости от поставщика, у вас может быть собственный синтаксис, созданный специально для этого, например, OVER и Parition на SQL Server
Ответ №4
Все возможно, это может быть очень очень уродливо. Вот пример уродливого кода, который может получить то, что вы хотите в одном запросе…
Запрос кода PSEUDO:
SELECT lesson_id, max_score WHERE uid=X UNION SELECT -1, sum //done via nested query UNION SELECT -2, average //done via nested query
