Mysql выбирает только персонал с указанным количеством последовательных свободных временных интервалов

У каждого сотрудника уже есть таблица свободных временных интервалов в AvailSlots, например:

    Staff_ID  Avail_Slots_Datetime

    1         2015-1-1 09:00:00
    1         2015-1-1 10:00:00
    1         2015-1-1 11:00:00
    2         2015-1-1 09:00:00
    2         2015-1-1 10:00:00
    2         2015-1-1 11:00:00
    3         2015-1-1 09:00:00
    3         2015-1-1 12:00:00
    3         2015-1-1 15:00:00

Мне нужно выяснить, у кого из сотрудников есть, например, 2 (или 3, 4 и т. д.) ПОСЛЕДОВАТЕЛЬНЫХ свободных временных интервала в каждом временном интервале. Как новичок, приведенные ниже коды INNER JOIN - это все, что я знаю, чтобы написать, если запрос предназначен для 2 последовательных временных интервалов.

    SELECT a.start_time, a.person
    FROM a_free a, a_free b
    WHERE (b.start_time = addtime( a.start_time, '01:00:00' )) and (a.person = b.person)

Но, очевидно, делая это таким образом, мне пришлось бы добавлять больше кодов INNER JOIN - для каждого случая - в зависимости от того, предназначен ли запрос для 3, 4, или 5 и т. д. последовательных доступных временных интервалов в заданную дату/час. Поэтому я хочу узнать более эффективный и гибкий способ сделать то же самое. В частности, код запроса, который мне нужен (на естественном языке), будет таким:

  • Для каждого временного интервала в AvailSlots перечислите один персонал, у которого есть X (где X может быть любым числом, которое я укажу для запроса, от 1 до 24) последовательных интервалов даты и времени, начиная с этой даты и времени. В случае, если этому критерию соответствует несколько сотрудников, тай-брейк определяется их «рангом», который хранится в отдельной таблице ниже:

    Ranking Table (lower number = higher rank) 
Staff_ID  Rank
1          3
2          1
3          2

Если ответ заключается в использовании таких вещей, как «переменные mysql», «представления» и т. д., пожалуйста, объясните, как эти вещи работают. Опять же, как новичок в MySQL, "выбрать", "присоединиться", "где", "группировать по" - это все, что я знаю до сих пор. Я очень хочу узнать больше, но до сих пор не понимаю более продвинутых концепций mysql. Спасибо заранее.


mysql timeslots
person jnewbie    schedule 10.01.2015    source источник
comment
Пользовательские переменные, CASE WHEN start_time=@st+INTERVAL 1 HOUR THEN @somevartogroupby ELSE @somevartogroupby :=@somevartogroupby +1 END AS g   -  person Mihai    schedule 10.01.2015
comment
Это действительно для каждого временного интервала в AvailSlots или вы имеете в виду определенный интервал времени, выбранный для выполнения запроса?   -  person koriander    schedule 11.01.2015

Ответы (1)


arrow_upward
1
arrow_downward

Используя немного больше данных, чем вы опубликовали, я нашел запрос, который может сделать то, что вам нужно. Он использует переменные, как вы и предсказывали :), но я надеюсь, что это не требует пояснений. Начнем с таблицы:

CREATE TABLE a_free
    (`Staff_ID` int, `Avail_Slots_Datetime` datetime)
;

INSERT INTO a_free
    (`Staff_ID`, `Avail_Slots_Datetime`)
VALUES
    (1, '2015-01-01 09:00:00'),
    (1, '2015-01-01 10:00:00'),
    (1, '2015-01-01 11:00:00'),
    (1, '2015-01-01 13:00:00'),
    (2, '2015-01-01 09:00:00'),
    (2, '2015-01-01 10:00:00'),
    (2, '2015-01-01 11:00:00'),
    (3, '2015-01-01 09:00:00'),
    (3, '2015-01-01 12:00:00'),
    (3, '2015-01-01 15:00:00'),
    (3, '2015-01-01 16:00:00'),
    (3, '2015-01-01 17:00:00'),
    (3, '2015-01-01 18:00:00')
;

Затем следует запрос на поиск последовательных слотов. В нем указано время начала каждой пары, и каждая группа последовательных слотов отмечена уникальным номером. В выражении case происходит волшебство, см. комментарии:

select
Staff_ID,
Avail_Slots_Datetime as slot_start, 
case
  when @slot_group is null then @slot_group:=0 -- initalize the variable
  when @prev_end <> Avail_Slots_Datetime then @slot_group:=@slot_group+1  -- iterate if previous slot end does not match current one's start
  else @slot_group -- otherwise just just keep the value
end as slot_group,
@prev_end:= Avail_Slots_Datetime + interval 1 hour as slot_end -- store the current slot end to compare with next row
from a_free
order by Staff_ID, Avail_Slots_Datetime asc;

Имея список с идентифицированными группами слотов, мы можем обернуть приведенный выше запрос в другой, чтобы получить длины каждой группы слотов. Результаты первого запроса обрабатываются как любая другая таблица:

select
  Staff_ID,
  slot_group,
  min(slot_start) as group_start,
  max(slot_end) as group_end,
  count(*) as group_length
from (

  select
    Staff_ID,
    Avail_Slots_Datetime as slot_start, 
    case
      when @slot_group is null then @slot_group:=0
      when @prev_end <> Avail_Slots_Datetime then @slot_group:=@slot_group+1
      else @slot_group
    end as slot_group,
    @prev_end:= Avail_Slots_Datetime + interval 1 hour as slot_end
  from a_free
  order by Staff_ID, Avail_Slots_Datetime asc
) groups
group by Staff_ID, slot_group;

Примечание: если вы используете то же соединение с БД для повторного выполнения запроса, переменные не будут сброшены, поэтому нумерация slot_groups будет продолжать расти. Обычно это не должно быть проблемой, но на всякий случай вам нужно выполнить что-то вроде этого до или после:

select @prev_end:=null;

Поиграйте со скрипкой, если хотите: http://sqlfiddle.com/#!2/0446c8/15

person ptrk    schedule 10.01.2015
comment
Большое спасибо за отличный ответ. Я благодарен без слов. Я многому научился из него, и он дает мне как новичку очень ценный урок по использованию переменных для решения задач. Спасибо еще раз! - person jnewbie; 11.01.2015
comment
Пожалуйста, я был бы признателен, если бы вы приняли ответ взамен;) - person ptrk; 11.01.2015
comment
Очень жаль! Я так и не научился принимать ответ, щелкнув галочку, и не мог видеть кнопку «принять». Я только сейчас узнал, как это сделать. ваше здоровье. - person jnewbie; 05.02.2015