Нужна помощь в использовании локальных переменных для вычитания предыдущего значения строки, имеющего несколько групп

Question

У меня есть 3 таблицы следующим образом:

+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| id        | int(11)      | NO   | PRI | 0       |       |
| full_name | varchar(200) | YES  |     | NULL    |       |
| gender    | varchar(1)   | YES  |     | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
+----------+---------+------+-----+---------+-------+
| Field    | Type    | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| actor_id | int(11) | YES  | MUL | NULL    |       |
| movie_id | int(11) | YES  | MUL | NULL    |       |
| salary   | int(11) | YES  |     | NULL    |       |
+----------+---------+------+-----+---------+-------+
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | NO   | PRI | 0       |       |
| title | varchar(100) | YES  |     | NULL    |       |
| year  | int(11)      | YES  |     | NULL    |       |
| genre | varchar(100) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+

Я пытаюсь выяснить, у какого актера больше всего времени между фильмами.

Я пытаюсь использовать локальные переменные для ссылки на предыдущее значение строки в случае, когда актер был таким же (или по умолчанию он равен 0). Однако по какой-то причине переменная @previousName возвращает случайный список имен.

Вот код:

SELECT 
    a.full_name, 
    m.year,
    m.title,
    @PreviousName,
    @PreviousYear,
    if(@PreviousName = a.full_name, m.year - @PreviousYear, 0) AS Delta,
    @PreviousName := a.full_name,
    @PreviousYear := m.year 
FROM
    actors AS a
        INNER JOIN
    cast AS c ON a.id = c.actor_id
        INNER JOIN
    movies AS m ON c.movie_id = m.id,
    (SELECT @PreviousName := null, @PreviousYear := 999) as SQLVars
ORDER BY full_name;

И вот что я получаю: Изображение таблицы результатов

Обратите внимание, я использую MySQL V5.7, поэтому оконные функции не вариант.

Answer 1

Вы можете сделать это с помощью коррелированного подзапроса:

select mc.*, (year - prev_year) as diff
from (select c.*, m.year,
             (select m2.year
              from movies m2 join
                   cast c2
                   on c2.movie_id = m2.id
              where c2.actor_id = c.actor_id and
                    m2.year < m.year
              order by m2.year desc
              limit 1
             ) prev_year
      from movies m join
           cast c
           on c.movie_id = m.id
     ) mc
order by diff desc;

Это предполагает, что актер снимается не в двух фильмах одного года. Если бы у вас была дата релиза или что-то еще, это было бы более эффективно для заказа времени.