Вычесть значения одного столбца для записей, поступающих из двух разных выборок, которые не обязательно совпадают

#mysql #sql

Вопрос:

Вот простая формулировка проблемы:

Предположим, что мы выдали запрос SELECT, который выдал что-то вроде этого:

 student_id |  course_id  | status_id | created_at
     1            1            1       some_date1
     2            1            1       some_date2
     6            2            1       some_date3

И другой запрос ВЫБОРА в той же таблице, который превзошел что-то вроде этого:

 student_id |  course_id  | status_id |     created_at
     1            1            3       some_other_date1
     2            1            2       some_other_date2
     4            3            2       some_other_date3

Таким образом, обе таблицы фактически являются временными таблицами.

Я хочу SQL-запрос (я использую MySQL), который принимает обе выборки, учитывает только то, что общего между обеими выборками на основе student_id и course_id и выводит student_id, course_id и разницу во времени между значениями created_at для каждой записи.

Любая помощь приветствуется. Приветствуется даже подсказка о некоторых возможностях SQL, о которых я не знаю.

1. Обеспечивает ли каждый ваш запрос (student_id, course_id) уникальность в наборе строк?

2. @Akina Да, это звучит как хорошее решение. Да, я считаю, что (student_id, course_id) это определяет строку однозначно в каждой таблице.

Ответ №1:

 SELECT student_id, course_id, DATEDIFF(t1.created_at, t2.created_at) difference
FROM (
       SELECT student_id, course_id, /* status_id, */ created_at
       FROM /* the rest of query 1 */ 
     ) AS t1
JOIN (
       SELECT student_id, course_id, /* status_id, */ created_at
       FROM /* the rest of query 2 */ 
     ) AS t2 USING (student_id, course_id)

1. Должно ли это быть «как разница» после DATEDIFF()? Возвращает ли DATEDIFF() разницу между двумя датами в секундах?

2. @stressedout Возвращает ли DATEDIFF() разницу между двумя датами в секундах? Нет, в днях. Если вам нужна разница в секундах, тогда используйте TIMESTAMPDIFF(SECOND, t1.created_at, t2.created_at) . Конечно, вы можете легко использовать другую единицу измерения периода времени. PS. Может ли иметь смысл разница в секундах между датами, а не datetime / timestamp?

3. Ваше решение кажется мне правильным, и в целом оно имеет смысл. Но я все еще обдумываю это в своей голове. Почему вы комментируете status_id, а затем используете USING в конце? Столкнусь ли я с неприятностями, если не прокомментирую это?

4. @stressedout Почему вы комментируете status_id Он не используется во внешнем запросе, поэтому его выбор не имеет смысла. использовать ИСПОЛЬЗОВАНИЕ в конце? Это вариант синтаксиса условия объединения, но вместо ON он выдает одну копию столбца в выходных данных / выражениях, когда не указан псевдоним таблицы, тогда как ON выдаст ошибку «неоднозначное имя». Возникнут ли у меня проблемы, если я не прокомментирую это? Нет. Вы только увеличите объем работы сервера и необходимый объем памяти — без какой-либо прибыли.