MySQL -按连续块分组
MySQL -按连续块分组
提问于 2018-06-19 06:50:52
我正在努力创建一个连续的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/
我试图用给定状态的开始和结束日期来封装periods的主要思想。与其他示例不同的一个复杂性是，我使用每个room_id的日期作为索引字段(而不是连续的id)。
My table:
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;由于某些原因，这会返回一些null、、_id的结果，而且通常不准确。
回答 2
Stack Overflow用户
发布于 2018-06-19 07:25:20
使用变量有点棘手。我会选择：
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()，从而大大简化了计算。
Stack Overflow用户
发布于 2018-06-19 08:27:20
感谢@戈登·林诺夫给了我洞察力，让我得到了这个答案：
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
;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50918451
复制相关文章
相似问题