mysql-按连续块分组

e3bfsja2  于 2021-06-20  发布在  Mysql
关注(0)|答案(2)|浏览(338)

我正在努力做一个决定 GROUP BY 连续块,我使用了以下两个作为引用:
-sql中连续行的分组依据
-如何在mysql中实现连续分组?

  • https://gcbenison.wordpress.com/2011/09/26/queries-that-group-tables-by-contiguous-blocks/
    我试图用给定状态的开始和结束日期来封装时段的主要思想。与其他示例不同的是,我使用每个房间的日期id作为索引字段(而不是顺序id)。
    我的table:
  1. room_id | calendar_date | state

样本数据:

  1. 1 | 2016-03-01 | 'a'
  2. 1 | 2016-03-02 | 'a'
  3. 1 | 2016-03-03 | 'a'
  4. 1 | 2016-03-04 | 'b'
  5. 1 | 2016-03-05 | 'b'
  6. 1 | 2016-03-06 | 'c'
  7. 1 | 2016-03-07 | 'c'
  8. 1 | 2016-03-08 | 'c'
  9. 1 | 2016-03-09 | 'c'
  10. 2 | 2016-04-01 | 'b'
  11. 2 | 2016-04-02 | 'a'
  12. 2 | 2016-04-03 | 'a'
  13. 2 | 2016-04-04 | 'a'

目标是:

  1. room_id | date_start | date_end | state
  2. 1 | 2016-03-01 | 2016-03-03 | a
  3. 1 | 2016-03-04 | 2016-03-05 | b
  4. 1 | 2016-03-06 | 2016-03-09 | c
  5. 2 | 2016-04-01 | 2016-04-01 | b
  6. 2 | 2016-04-02 | 2016-04-04 | c

我在这方面做了两次尝试:
1)

  1. SELECT
  2. rooms.row_new,
  3. rooms.state_new,
  4. MIN(rooms.room_id) AS room_id,
  5. MIN(rooms.state) AS state,
  6. MIN(rooms.date) AS date_start,
  7. MAX(rooms.date) AS date_end,
  8. FROM
  9. (
  10. SELECT @r := @r + (@state != state) AS row_new,
  11. @state := state AS state_new,
  12. rooms.*
  13. FROM (
  14. SELECT @r := 0,
  15. @state := ''
  16. ) AS vars,
  17. rooms_vw
  18. ORDER BY room_id, date
  19. ) AS rooms
  20. WHERE room_id = 1
  21. GROUP BY row_new
  22. ORDER BY room_id, date
  23. ;

这是非常接近工作,但当我打印出新的行时,它开始跳转(1,2,3,5,7,…)
2)

  1. SELECT
  2. MIN(rooms_final.calendar_date) AS date_start,
  3. MAX(rooms_final.calendar_date) AS date_end,
  4. rooms_final.state,
  5. rooms_final.room_id,
  6. COUNT(*)
  7. FROM (SELECT
  8. rooms.date,
  9. rooms.state,
  10. rooms.room_id,
  11. CASE
  12. WHEN rooms_merge.state IS NULL OR rooms_merge.state != rooms.state THEN
  13. @rownum := @rownum+1
  14. ELSE
  15. @rownum
  16. END AS row_num
  17. FROM rooms
  18. JOIN (SELECT @rownum := 0) AS row
  19. LEFT JOIN (SELECT rooms.date + INTERVAL 1 DAY AS date,
  20. rooms.state,
  21. rooms.room_id
  22. FROM rooms) AS rooms_merge ON rooms_merge.calendar_date = rooms.calendar_date AND rooms_merge.room_id = rooms.room_id
  23. ORDER BY rooms.room_id, rooms.calendar_date
  24. ) AS rooms_final
  25. GROUP BY rooms_final.state, rooms_final.row_num
  26. ORDER BY room_id, calendar_date;

由于某些原因,这会返回一些nullroom\u id的结果,而且通常是不准确的。

hgncfbus

hgncfbus1#

感谢@gordon linoff为我提供了获得这个答案的见解:

  1. SELECT
  2. MIN(room_id) AS room_id,
  3. MIN(state) AS state,
  4. MIN(date) AS date_start,
  5. MAX(date) AS date_end
  6. FROM
  7. (
  8. SELECT
  9. @r := @r + IF(@state <> state OR @room_id <> room_id, 1, 0) AS row_new,
  10. @state := state AS state_new,
  11. @room_id := room_id AS room_id_new,
  12. tmp_rooms.*
  13. FROM (
  14. SELECT @r := 0,
  15. @room_id := 0,
  16. @state := ''
  17. ) AS vars,
  18. (SELECT * FROM rooms WHERE room_id IS NOT NULL ORDER BY room_id, date) tmp_rooms
  19. ) AS rooms
  20. GROUP BY row_new
  21. order by room_id, date
  22. ;
展开查看全部
mzillmmw

mzillmmw2#

使用变量有点棘手。我会选择:

  1. SELECT r.state_new, MIN(r.room_id) AS room_id, MIN(r.state) AS state,
  2. MIN(r.date) AS date_start, MAX(r.date) AS date_end
  3. FROM (SELECT r.*,
  4. (@grp := if(@rs = concat_ws(':', room, state), @grp,
  5. if(@rs := concat_ws(':', room, state), @grp + 1, @grp + 1)
  6. )
  7. ) as grp
  8. FROM (SELECT r.* FROM rooms_vw r ORDER BY ORDER BY room_id, date
  9. ) r CROSS JOIN
  10. (SELECT @grp := 0, @rs := '') AS params
  11. ) AS rooms
  12. WHERE room_id = 1
  13. GROUP BY room_id, grp
  14. ORDER BY room_id, date;

笔记:
在一个表达式中分配变量并在另一个表达式中使用它是不安全的。mysql不保证表达式的求值顺序。
在较新版本的mysql中,您需要 ORDER BY 在子查询中。
在最新版本中,可以使用 row_number() ,大大简化了计算。

展开查看全部

相关问题