Разобрали ответы и решения урока 3.4(База данных «Абитуриент», запросы корректировки) под курс «Интерактивный тренажер по SQL».
Предыдущий разбор модуля 3.4 следующий разбор модуль 4.1
Отобрать все шаги, в которых рассматриваются вложенные запросы (то есть в названии шага упоминаются вложенные запросы). Указать к какому уроку и модулю они относятся. Для этого вывести 3 поля:
— в поле Модуль указать номер модуля и его название через пробел;
— в поле Урок указать номер модуля, порядковый номер урока (lesson_position) через точку и название урока через пробел;
— в поле Шаг указать номер модуля, порядковый номер урока (lesson_position) через точку, порядковый номер шага (step_position) через точку и название шага через пробел.Длину полей Модуль и Урок ограничить 19 символами, при этом слишком длинные надписи обозначить многоточием в конце (16 символов — это номер модуля или урока, пробел и название Урока или Модуля к ним присоединить «…»). Информацию отсортировать по возрастанию номеров модулей, порядковых номеров уроков и порядковых номеров шагов.
SELECT CONCAT(LEFT(CONCAT(module_id, ' ', module_name), 16), '...') Модуль,
CONCAT(LEFT(CONCAT(module_id, '.', lesson_position, ' ', lesson_name), 16), '...') Урок,
CONCAT(module_id, '.', lesson_position, '.', step_position, ' ', step_name) Шаг
FROM module
INNER JOIN lesson USING(module_id)
INNER JOIN step USING(lesson_id)
WHERE step_name LIKE '%ложенн% запрос%'
ORDER BY module_id, lesson_id, step_id;Заполнить таблицу
step_keywordследующим образом: если ключевое слово есть в названии шага, то включить вstep_keywordстроку сidшага иidключевого слова.
INSERT INTO step_keyword
SELECT step.step_id, keyword.keyword_id
FROM
keyword
CROSS JOIN step
WHERE step.step_name REGEXP CONCAT(' ', CONCAT(keyword.keyword_name, '\\b'))
GROUP BY step.step_id, keyword.keyword_id
ORDER BY keyword.keyword_id;Реализовать поиск по ключевым словам. Вывести шаги, с которыми связаны ключевые слова
MAXиAVGодновременно. Для шагов указать id модуля, позицию урока в модуле, позицию шага в уроке через точку, после позиции шага перед заголовком — пробел. Позицию шага в уроке вывести в виде двух цифр (если позиция шага меньше 10, то перед цифрой поставить 0). Столбец назватьШаг. Информацию отсортировать по первому столбцу в алфавитном порядке.
SELECT
concat(module_id,'.',lesson_position,
IF(step_position < 10, ".0","."),
step_position," ",step_name) AS Шаг
FROM
step
JOIN lesson USING(lesson_id)
JOIN module USING(module_id)
JOIN step_keyword USING (step_id)
JOIN keyword USING(keyword_id)
WHERE keyword_name = 'MAX' OR keyword_name ='AVG'
GROUP BY ШАГ
HAVING COUNT(*) = 2
ORDER BY 1;Посчитать, сколько студентов относится к каждой группе. Столбцы назвать
Группа,Интервал,Количество. Указать границы интервала.
SELECT
rate_group Группа,
CASE rate_group
WHEN 'I' THEN 'от 0 до 10'
WHEN 'II' THEN 'от 11 до 15'
WHEN 'III' THEN 'от 16 до 27'
ELSE 'больше 27'
END Интервал,
COUNT(*) Количество
FROM
(
SELECT
CASE
WHEN COUNT(DISTINCT step_id) <= 10 THEN 'I'
WHEN COUNT(DISTINCT step_id) <= 15 THEN 'II'
WHEN COUNT(DISTINCT step_id) <= 27 THEN 'III'
ELSE 'IV'
END rate_group
FROM step_student
WHERE result = 'correct'
GROUP BY student_id
) query_in
GROUP BY rate_group
ORDER BY 1;Исправить запрос примера так: для шагов, которые не имеют неверных ответов, указать 100 как процент успешных попыток, если же шаг не имеет верных ответов, указать 0. Информацию отсортировать сначала по возрастанию успешности, а затем по названию шага в алфавитном порядке.
WITH table1 (step_name, correct, count) AS (
SELECT
step_name,
SUM( IF (result = 'correct' , 1 , 0)) AS s,
COUNT(result) AS c
FROM step
JOIN step_student USING (step_id)
GROUP BY step_name
)
SELECT step_name AS Шаг, ROUND((correct/count)*100) AS Успешность
FROM table1
ORDER BY 2, 1Вычислить прогресс пользователей по курсу. Прогресс вычисляется как отношение верно пройденных шагов к общему количеству шагов в процентах, округленное до целого. В нашей базе данные о решениях занесены не для всех шагов, поэтому общее количество шагов определить как количество различных шагов в таблице step_student.
Тем пользователям, которые прошли все шаги (прогресс = 100%) выдать «Сертификат с отличием». Тем, у кого прогресс больше или равен 80% — «Сертификат». Для остальных записей в столбце Результат задать пустую строку («»).
Информацию отсортировать по убыванию прогресса, затем по имени пользователя в алфавитном порядке.
WITH get_passed (student_name, pssd)
AS
(
SELECT student_name, COUNT(DISTINCT step_id) AS passed
FROM student JOIN step_student USING(student_id)
WHERE result = "correct"
GROUP BY student_id
ORDER BY passed
)
SELECT student_name AS Студент, ROUND(100*pssd/(SELECT COUNT(DISTINCT step_id) FROM step_student)) AS Прогресс,
CASE
WHEN ROUND(100*pssd/(SELECT COUNT(DISTINCT step_id) FROM step_student)) = 100 THEN "Сертификат с отличием"
WHEN ROUND(100*pssd/(SELECT COUNT(DISTINCT step_id) FROM step_student)) >= 80 THEN "Сертификат"
ELSE ""
END AS Результат
FROM get_passed
ORDER BY Прогресс DESC, Студент
Для студента с именем student_61 вывести все его попытки: название шага, результат и дату отправки попытки (
submission_time). Информацию отсортировать по дате отправки попытки и указать, сколько минут прошло между отправкой соседних попыток. Название шага ограничить 20 символами и добавить «…». Столбцы назватьСтудент, Шаг, Результат, Дата_отправки, Разница.
SELECT student_name AS Студент,
CONCAT(LEFT(step_name, 20), '...') AS Шаг,
result AS Результат,
FROM_UNIXTIME(submission_time) AS Дата_отправки,
SEC_TO_TIME(submission_time - LAG(submission_time, 1, submission_time) OVER (ORDER BY submission_time)) AS Разница
FROM student
INNER JOIN step_student USING(student_id)
INNER JOIN step USING(step_id)
WHERE student_name = 'student_61'
ORDER BY Дата_отправки;
Посчитать среднее время, за которое пользователи проходят урок по следующему алгоритму:
— для каждого пользователя вычислить время прохождения шага как сумму времени, потраченного на каждую попытку (время попытки — это разница между временем отправки задания и временем начала попытки), при этом попытки, которые длились больше 4 часов не учитывать, так как пользователь мог просто оставить задание открытым в браузере, а вернуться к нему на следующий день;
— для каждого студента посчитать общее время, которое он затратил на каждый урок;
— вычислить среднее время выполнения урока в часах, результат округлить до 2-х знаков после запятой;
— вывести информацию по возрастанию времени, пронумеровав строки, для каждого урока указать номер модуля и его позицию в нем.Столбцы результата назвать Номер, Урок, Среднее_время.
SELECT ROW_NUMBER() OVER (ORDER BY Среднее_время) AS Номер,
Урок, Среднее_время
FROM(
SELECT
Урок, ROUND(AVG(difference), 2) AS Среднее_время
FROM
(SELECT student_id,
CONCAT(module_id, '.', lesson_position, ' ', lesson_name) AS Урок,
SUM((submission_time - attempt_time) / 3600) AS difference
FROM module INNER JOIN lesson USING (module_id)
INNER JOIN step USING (lesson_id)
INNER JOIN step_student USING (step_id)
WHERE submission_time - attempt_time <= 4 * 3600
GROUP BY 1, 2) AS query_1
GROUP BY 1) AS TA
order by 3;Вычислить рейтинг каждого студента относительно студента, прошедшего наибольшее количество шагов в модуле (вычисляется как отношение количества пройденных студентом шагов к максимальному количеству пройденных шагов, умноженное на 100). Вывести номер модуля, имя студента, количество пройденных им шагов и относительный рейтинг. Относительный рейтинг округлить до одного знака после запятой. Столбцы назвать
Модуль,Студент,Пройдено_шаговиОтносительный_рейтингсоответственно. Информацию отсортировать сначала по возрастанию номера модуля, потом по убыванию относительного рейтинга и, наконец, по имени студента в алфавитном порядке.
SELECT module_id AS Модуль, student_name AS Студент, COUNT(DISTINCT step_id) AS Пройдено_шагов ,
ROUND(COUNT(DISTINCT step_id) /
MAX(COUNT(DISTINCT step_id)) OVER(PARTITION BY module_id) *100, 1) AS Относительный_рейтинг
FROM lesson
JOIN step USING (lesson_id)
JOIN step_student USING (step_id)
JOIN student USING (student_id)
WHERE result = 'correct'
GROUP BY module_id, student_name
ORDER BY 1, 4 DESC, 2Проанализировать, в каком порядке и с каким интервалом пользователь отправлял последнее верно выполненное задание каждого урока. В базе занесены попытки студентов для трех уроков курса, поэтому анализ проводить только для этих уроков.
WITH get_time_lesson(student_name, lesson, max_submission_time)
AS(
SELECT student_name, CONCAT(module_id, '.', lesson_position), MAX(submission_time)
FROM step_student INNER JOIN step USING (step_id)
INNER JOIN lesson USING (lesson_id)
INNER JOIN student USING(student_id)
WHERE result = 'correct'
GROUP BY 1,2
ORDER BY 1),
get_students(student_name)
AS(
SELECT student_name
FROM get_time_lesson
GROUP BY student_name
HAVING COUNT(lesson) = 3)
SELECT student_name as Студент,
lesson as Урок,
FROM_UNIXTIME(max_submission_time) as Макс_время_отправки,
IFNULL(CEIL((max_submission_time - LAG(max_submission_time) OVER (PARTITION BY student_name ORDER BY max_submission_time )) / 86400),'-') as Интервал
FROM get_time_lesson
WHERE student_name in (SELECT * FROM get_students)
ORDER BY 1,3;
Для студента с именем student_59 вывести следующую информацию по всем его попыткам:
— информация о шаге: номер модуля, символ ‘.’, позиция урока в модуле, символ ‘.’, позиция шага в модуле;
— порядковый номер попытки для каждого шага — определяется по возрастанию времени отправки попытки;
— результат попытки;
— время попытки (преобразованное к формату времени) — определяется как разность между временем отправки попытки и времени ее начала, в случае если попытка длилась более 1 часа, то время попытки заменить на среднее время всех попыток пользователя по всем шагам без учета тех, которые длились больше 1 часа;
— относительное время попытки — определяется как отношение времени попытки (с учетом замены времени попытки) к суммарному времени всех попыток шага, округленное до двух знаков после запятой.Столбцы назвать Студент, Шаг, Номерпопытки, Результат, Времяпопытки и Относительное_время. Информацию отсортировать сначала по возрастанию id шага, а затем по возрастанию номера попытки (определяется по времени отправки попытки).
SET @avg_time := (SELECT CEIL(AVG(submission_time - attempt_time))
FROM step_student INNER JOIN student USING(student_id)
WHERE student_name = "student_59" AND (submission_time - attempt_time) < 3600);
WITH get_stat
AS
(
SELECT student_name, CONCAT(module_id, ".", lesson_position, ".", step_position) AS less, step_id, RANK() OVER (PARTITION BY CONCAT(module_id, ".", lesson_position, ".", step_position) ORDER BY submission_time) AS rang, result,
CASE
WHEN (submission_time - attempt_time) > 3600 THEN @avg_time
ELSE (submission_time - attempt_time)
END AS qr
FROM student
INNER JOIN step_student USING(student_id)
INNER JOIN step USING(step_id)
INNER JOIN lesson USING(lesson_id)
WHERE student_name = "student_59"
)
SELECT student_name AS Студент, less AS Шаг, rang AS Номер_попытки, result AS Результат, SEC_TO_TIME(CEIL(qr)) AS Время_попытки, ROUND((qr / (SUM(qr) OVER (PARTITION BY less ORDER BY less)) * 100), 2) AS Относительное_время
FROM get_stat
ORDER BY step_id, 3;Вывести группу (
I, II, III), имя пользователя, количество шагов, которые пользователь выполнил по соответствующему способу. Столбцы назватьГруппа, Студент, Количество_шагов. Отсортировать информацию по возрастанию номеров групп, потом по убыванию количества шагов и, наконец, по имени студента в алфавитном порядке.
WITH qr
AS
(
SELECT student_name, step_id, count(result) AS sm
FROM step_student INNER JOIN student USING(student_id)
WHERE result = "correct"
GROUP BY 1, 2
HAVING count(result) > 1
)
SELECT "I" AS Группа, student_name AS Студент, count(step_id) AS Количество_шагов
FROM (
SELECT student_name, step_id, IF(result = "correct" AND submission_time < MAX(submission_time) OVER (PARTITION BY student_name, step_id), IF(LEAD(result) OVER (PARTITION BY student_id, step_id ORDER BY submission_time) = "wrong", 1, 0), 0) AS change_res
FROM step_student INNER JOIN student USING(student_id))qr1
WHERE change_res = 1
GROUP BY 1, 2
UNION
SELECT "II" AS Группа, student_name AS Студент, count(step_id) AS Количество_шагов
FROM qr
GROUP BY 1, 2
UNION
SELECT "III" AS Группа, student_name AS Студент, count(DISTINCT step_id) AS Количество_шагов
FROM (
SELECT student_id, step_id, SUM(new_result) OVER (PARTITION BY student_id, step_id) AS sum_result
FROM (
SELECT student_id, step_id, IF(result = "wrong", 0, 1) AS new_result
FROM step_student)qr_1)qr_2 INNER JOIN student USING(student_id)
WHERE sum_result = 0
GROUP BY student_id
ORDER BY Группа, Количество_шагов DESC, Студент;