MySQL - группировка по непрерывным блокам

Question

Я изо всех сил пытаюсь GROUP BY смежные блоки, я использовал следующие два для ссылок:
- GROUP BY для непрерывных строк в SQL
Я могу сделать непрерывную группу в MySQL?
- https://gcbenison.wordpress.com/2011/09/26/queries-that-group-tables-by-contiguous-blocks/

Основная идея, что я пытаюсь инкапсулировать периоды с начальной и конечной датой данного состояния. Сложность, в отличие от других примеров, заключается в том, что в качестве поля индексации я использую дату на номер_места (а не последовательный идентификатор).

Мой стол:

room_id | calendar_date | state

Пример данных:

1 | 2016-03-01 | 'a'
1 | 2016-03-02 | 'a'
1 | 2016-03-03 | 'a'
1 | 2016-03-04 | 'b'
1 | 2016-03-05 | 'b'
1 | 2016-03-06 | 'c'
1 | 2016-03-07 | 'c'
1 | 2016-03-08 | 'c'
1 | 2016-03-09 | 'c'
2 | 2016-04-01 | 'b'
2 | 2016-04-02 | 'a'
2 | 2016-04-03 | 'a'
2 | 2016-04-04 | 'a'

Цель:

room_id | date_start | date_end   | state
1       | 2016-03-01 | 2016-03-03 | a
1       | 2016-03-04 | 2016-03-05 | b
1       | 2016-03-06 | 2016-03-09 | c
2       | 2016-04-01 | 2016-04-01 | b
2       | 2016-04-02 | 2016-04-04 | c

Две попытки, которые я сделал в этом:
1)

SELECT
  rooms.row_new,
  rooms.state_new,
  MIN(rooms.room_id) AS room_id,
  MIN(rooms.state) AS state,
  MIN(rooms.date) AS date_start,
  MAX(rooms.date) AS date_end,
FROM
  (
    SELECT @r := @r + (@state != state) AS row_new,
      @state := state AS state_new,
      rooms.*
      FROM (
        SELECT @r := 0,
          @state := ''
      ) AS vars,
        rooms_vw
    ORDER BY room_id, date
  ) AS rooms
  WHERE room_id = 1
GROUP BY row_new
ORDER BY room_id, date
;

Это очень близко к работе, но когда я печатаю row_new, он начинает прыгать (1, 2, 3, 5, 7,...)

2)

SELECT 
    MIN(rooms_final.calendar_date) AS date_start,
    MAX(rooms_final.calendar_date) AS date_end,
    rooms_final.state,
    rooms_final.room_id,
    COUNT(*)
 FROM (SELECT 
     rooms.date,
     rooms.state,
     rooms.room_id,
     CASE
         WHEN rooms_merge.state IS NULL OR rooms_merge.state != rooms.state THEN
                     @rownum := @rownum+1
         ELSE
                     @rownum
         END AS row_num
            FROM rooms
            JOIN (SELECT @rownum := 0) AS row
       LEFT JOIN (SELECT rooms.date + INTERVAL 1 DAY AS date,
                         rooms.state,
                          rooms.room_id
                    FROM rooms) AS rooms_merge ON rooms_merge.calendar_date = rooms.calendar_date AND rooms_merge.room_id = rooms.room_id
            ORDER BY rooms.room_id, rooms.calendar_date
          ) AS rooms_final
 GROUP BY rooms_final.state, rooms_final.row_num
 ORDER BY room_id, calendar_date;

По некоторым причинам это возвращает некоторые нулевые результаты room_id, а также, как правило, неточные.

Answer 1

Работать с переменными немного сложно. Я бы пошел на:

SELECT r.state_new, MIN(r.room_id) AS room_id, MIN(r.state) AS state,
       MIN(r.date) AS date_start, MAX(r.date) AS date_end
FROM (SELECT r.*,
             (@grp := if(@rs = concat_ws(':', room, state), @grp,
                         if(@rs := concat_ws(':', room, state), @grp + 1, @grp + 1)
                       )
             ) as grp
    FROM (SELECT r.* FROM rooms_vw r ORDER BY ORDER BY room_id, date
         ) r CROSS JOIN
         (SELECT @grp := 0, @rs := '') AS params    
   ) AS rooms
WHERE room_id = 1
GROUP BY room_id, grp
ORDER BY room_id, date;

Заметки:

• Назначать переменную в одном выражении и использовать ее в другом небезопасно. MySQL не гарантирует порядок вычисления выражений.
• В более поздних версиях MySQL вам нужно сделать ORDER BY в подзапросе.
• В самых последних версиях вы можете использовать row_number(), значительно упрощая расчет.

Answer 2

Спасибо @Gordon Linoff за предоставленную мне информацию, чтобы получить этот ответ:

SELECT
  MIN(room_id) AS room_id,
  MIN(state) AS state,
  MIN(date) AS date_start,
  MAX(date) AS date_end
FROM
  (
    SELECT
  @r := @r + IF(@state <> state OR @room_id <> room_id, 1, 0) AS row_new,
      @state := state AS state_new,
      @room_id := room_id AS room_id_new,
      tmp_rooms.*
      FROM (
        SELECT @r := 0,
          @room_id := 0,
          @state := ''
      ) AS vars,
        (SELECT * FROM rooms WHERE room_id IS NOT NULL ORDER BY room_id, date) tmp_rooms
  ) AS rooms
GROUP BY row_new
order by room_id, date
;