代码之家 › 专栏 › 技术社区 › Kerry Jones

MySQL-按Contigous块分组

contiguous sql mysql

Kerry Jones · 技术社区 · 6 年前

我正在努力做一个 GROUP BY
- GROUP BY for continuous rows in SQL
How can I do a contiguous group by in MySQL?
- https://gcbenison.wordpress.com/2011/09/26/queries-that-group-tables-by-contiguous-blocks/

我试图用给定的开始和结束日期来封装句点的主要思想 . 与其他示例不同的复杂性是,我使用 每个房间的日期 作为索引字段(而不是顺序id)。

我的桌子:

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

我在这方面的两次尝试:
(一)

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
;

新建行 它开始跳跃(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;

无效的 房间编号 结果以及一般不准确。

2 回复 | 直到 6 年前

Gordon Linoff 6 年前

处理变量有点棘手。我会选择:

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不保证表达式的求值顺序。
ORDER BY 在子查询中。
在最新版本中,可以使用 row_number()

Kerry Jones 6 年前

感谢@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
;