Групповые киносеансы по датам

Question

Моя база данных фильмов позволяет пользователям вводить базу данных в таблицу сеансов, которая выглядит следующим образом:

id | time | movie_id

Где время - это дата и время, когда будет воспроизводиться фильм, а movie_id - это ключ, относящийся к таблице фильмов.

Теперь я пытаюсь найти дни подряд, в которых воспроизводимые фильмы совпадают. Например, я хочу иметь возможность отображать.

3/6/2013 - 7/6/2013
Iron Man 3   7pm
Second Movie 10pm
ETC...

8/6/2013
Iron Man 3   8pm

9/6/2013 - 11/6/2013
Iron Man 3   7pm
Second Movie 10pm

Есть ли способ группировать, используя как время, так и находя дни подряд? Я мог бы изменить таблицу на отдельное значение даты и времени, если это необходимо. Любая помощь приветствуется. Если это возможно только в PHP, любая идея о том, как начать работу, также будет оценена.

Спасибо. Если у вас есть какие-либо вопросы или этот вопрос недостаточно ясен, пожалуйста, задавайте.

Answer 1

Ну, не так элегантно, но с использованием рабочего решения GROUP_CONCAT агрегатная функция и переменные внутри SELECT группам, подобным последовательным дням.

Ваша схема и пример набора данных:

CREATE TABLE movies (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  title CHAR(64) NOT NULL
);
CREATE TABLE schedule (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  time TIMESTAMP NOT NULL,
  movie_id INT NOT NULL
);
INSERT INTO movies (title) VALUES
  ('Iron Man 3'),
  ('Second Movie')
;
INSERT INTO schedule (time, movie_id) VALUES
  ('2013-06-03 19:00:00', 1),
  ('2013-06-03 22:00:00', 2),
  ('2013-06-04 19:00:00', 1),
  ('2013-06-04 22:00:00', 2),
  ('2013-06-05 19:00:00', 1),
  ('2013-06-05 22:00:00', 2),
  ('2013-06-06 19:00:00', 1),
  ('2013-06-06 22:00:00', 2),
  ('2013-06-07 19:00:00', 1),
  ('2013-06-07 22:00:00', 2),
  ('2013-06-08 20:00:00', 1),
  ('2013-06-09 19:00:00', 1),
  ('2013-06-09 22:00:00', 2),
  ('2013-06-10 19:00:00', 1),
  ('2013-06-10 22:00:00', 2),
  ('2013-06-11 19:00:00', 1),
  ('2013-06-11 22:00:00', 2),
  ('2013-06-13 19:00:00', 1),
  ('2013-06-13 22:00:00', 2)
;

Запрос:

SELECT
    DATE_FORMAT(min_date, '%e/%c/%Y') AS beg_date,
    DATE_FORMAT(max_date, '%e/%c/%Y') AS end_date,
    title,
    LOWER(TIME_FORMAT(time, '%l%p')) AS `movie_time`
  FROM
    (SELECT
        MIN(min_date) AS min_date,
        MAX(max_date) AS max_date,
        range_schedule
      FROM
        (SELECT
            @min_date :=
              IF(@range_schedule <=> day_schedule
                 AND days.date <=> ADDDATE(@max_date, 1),
                @min_date,
                days.date
              ) AS min_date,
            @max_date := days.date AS max_date,
            @range_schedule := days.day_schedule AS range_schedule
          FROM
            (
              SELECT DATE(time) AS `date`, GROUP_CONCAT(CONCAT(TIME(time), '-', movie_id) ORDER BY time) AS day_schedule
              FROM schedule
              GROUP BY DATE(time)
              ORDER BY DATE(time)
            ) AS days,
            (SELECT
              @min_date := '0000-00-00',
              @max_date := '0000-00-00',
              @range_schedule := NULL
            ) r
        ) days_of_ranges
      GROUP BY min_date, range_schedule
    ) ranges
    JOIN schedule ON DATE(schedule.time) = ranges.min_date
    JOIN movies ON movies.id = movie_id
  ORDER BY min_date, time
;

И результат:

|  BEG_DATE |  END_DATE |        TITLE | MOVIE_TIME |
-----------------------------------------------------
|  3/6/2013 |  7/6/2013 |   Iron Man 3 |        7pm |
|  3/6/2013 |  7/6/2013 | Second Movie |       10pm |
|  8/6/2013 |  8/6/2013 |   Iron Man 3 |        8pm |
|  9/6/2013 | 11/6/2013 |   Iron Man 3 |        7pm |
|  9/6/2013 | 11/6/2013 | Second Movie |       10pm |
| 13/6/2013 | 13/6/2013 |   Iron Man 3 |        7pm |
| 13/6/2013 | 13/6/2013 | Second Movie |       10pm |

Все, что вам нужно сделать в PHP, это хранить последние BEG_DATE а также END_DATE значения, чтобы сравнить их с текущими, чтобы решить, когда выводить заголовок диапазона.