Mysql Select Only Staff with Specified Number of Consecutive Free Time Slots

Each staff already has a table of avail time slots in AvailSlots like this:

    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

I need to find out which staff has, for example, 2 (or 3, 4, etc) CONSECUTIVE avail time slots at each time slot. As a novice, the INNER JOIN codes below is all I know to write if the query is for 2 consecutive time slots.

    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)

But, obviously, doing it that way, I would have to add more INNER JOIN codes - for each case - depending on whether the query is for 3, or 4, or 5 , etc consecutive available time slots at a given date/hour. Therefore, I want to learn a more efficient and flexible way to do the same. Specifically, the query code I need (in natural language) would be this:

For each time slot in AvailSlots, list one staff that has X (where X can be any number I specify per query, from 1 to 24) consecutive datetime slot starting from that datetime. In case more than one staff can meet that criteria, the tie break is their "rank" which is kept in a separate table below:
```
Ranking Table (lower number = higher rank) 
Staff_ID  Rank
1          3
2          1
3          2
```

If the answer is to use things like "mysql variables", "views", etc, please kindly explain how those things work. Again, as a total mysql novice, "select", "join", "where", "group by" are all I know so far. I am eager to learn more but have trouble understanding more advanced mysql concepts so far. Many thanks in advance.

Solution

Using a bit more data than you posted, I found a query that might do what you need. It does use the variables as you predicted :) but I hope it's pretty self-explanatory. Let's start with the table:

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')
;

Then there's a query to find the consecutive slots. It lists start times of each pair, and marks each group of consecutive slots with a unique number. The case expression is where the magic happens, see the comments:

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;

Having the list with slot groups identified, we can wrap the query above in another one to get the lengths of each slot group. The results of the first query are treated as any other table:

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;

Note: if you use the same DB connection to execute the query again, the variables would not be reset, so the slot_groups numbering will continue to grow. This normally should not be a problem, but to be on the safe side, you need to execute something like this before or after:

select @prev_end:=null;

Play with the fiddle if you like: http://sqlfiddle.com/#!2/0446c8/15