EXPLAIN EXTENDED

MySQL: grouping continuous ranges

with 4 comments

CREATE TABLE filler (
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT
) ENGINE=Memory;

CREATE TABLE t_range (
id INT NOT NULL PRIMARY KEY,
state INT NOT NULL,
date DATETIME NOT NULL,
value VARCHAR(100) NOT NULL,
KEY ix_range_date_state (date, state)
) ENGINE=InnoDB DEFAULT CHARSET=UTF8;

DELIMITER $$

CREATE PROCEDURE prc_filler(cnt INT)
BEGIN
DECLARE _cnt INT;
SET _cnt = 1;
WHILE _cnt <= cnt DO
                INSERT
                INTO    filler
                SELECT  _cnt;
                SET _cnt = _cnt + 1;
        END WHILE;
END
$$

DELIMITER ;

START TRANSACTION;
CALL prc_filler(10000);
COMMIT;

INSERT
INTO    t_range
SELECT  id,
        (id - 1) DIV 1000 + 1,
        '2009-07-24' - INTERVAL id MINUTE,
        RPAD(CONCAT('Value ', id), 100, ' ')
FROM    filler;

SELECT  MIN(id) AS id, MIN(date) AS date, MIN(state) AS state, COUNT(*) cnt
FROM    (
SELECT  id, date, state,
(
SELECT  id
FROM    t_range ri
WHERE   (ri.date, ri.state, ri.id) < (ro.date, ro.state, ro.id)
                        AND ri.state <> ro.state
ORDER BY
date DESC, state DESC
LIMIT 1
) AS prev
FROM    t_range ro
) q
GROUP BY
prev

SELECT  MIN(id) AS id, MIN(date) AS date, MIN(state) AS state, COUNT(*) cnt
FROM    (
SELECT  @r := @r + (@state != state OR @state IS NULL) AS gn,
@state := state AS sn,
s.id, s.date, s.state
FROM    (
SELECT  @r := 0,
@state := NULL
) vars,
t_range s
ORDER BY
date, state
) q
GROUP BY
gn

4 Responses to 'MySQL: grouping continuous ranges'

Subscribe to comments with RSS

1. can you please tell me “how to fetch continuous date from a table in mysql with between dates”

   

   WK

   24 Jun 14 at 10:46
2. @WK: could you please be more specific?

   

   Quassnoi

   25 Jun 14 at 10:47
3. Yes sure.

   I have a booking table that contain the following columns:

   property_id
   date
   status

   and in this table I have the records of the a month for august like

   12(property_id) 01-08-2014(date) available (status)
   12(property_id) 02-08-2014(date) available (status)
   12(property_id) 03-08-2014(date) available (status)
   12(property_id) 04-08-2014(date) un-available (status)

   12(property_id) 05-08-2014(date) available (status)

   and so on with status available or un-available.

   Now I want to fetch continuous date from this table with the availability & three continuous date

   Please reply

   

   WK

   25 Jun 14 at 10:55
4. Thanks for this!! I modified it some to meet my needs. I was looking for contiguous membership periods with start and end dates. I also added an account type to the group by to show different types of accounts. Basically a state of covered ( 1 ) or not covered ( 0 ) by account type. I had to change the min(id) to just id and add it to the group by for multiple people.

   
create table contig_test
(
id int,
mon int,
state int,
account_type int
);

   insert into contig_test( id, mon, state, account_type ) values ( 4444 , 1 , 1 , 15 );
   insert into contig_test( id, mon, state, account_type ) values ( 4444 , 2 , 1 , 15 );
   insert into contig_test( id, mon, state, account_type ) values ( 4444 , 3 , 1 , 15 );
   insert into contig_test( id, mon, state, account_type ) values ( 4444 , 4 , 1 , 15 );
   insert into contig_test( id, mon, state, account_type ) values ( 4444 , 5 , 1 , 15 );
   insert into contig_test( id, mon, state, account_type ) values ( 4444 , 6 , 1 , 15 );
   insert into contig_test( id, mon, state, account_type ) values ( 4444 , 7 , 1 , 15 );
   insert into contig_test( id, mon, state, account_type ) values ( 4444 , 8 , 1 , 15 );
   insert into contig_test( id, mon, state, account_type ) values ( 4444 , 9 , 1 , 15 );
   insert into contig_test( id, mon, state, account_type ) values ( 4444 , 10 , 1 , 15 );
   insert into contig_test( id, mon, state, account_type ) values ( 4444 , 11 , 1 , 15 );
   insert into contig_test( id, mon, state, account_type ) values ( 4444 , 12 , 1 , 15 );

   insert into contig_test( id, mon, state, account_type ) values ( 4445 , 1 , 1 , 15 );
   insert into contig_test( id, mon, state, account_type ) values ( 4445 , 2 , 1 , 15 );
   insert into contig_test( id, mon, state, account_type ) values ( 4445 , 3 , 1 , 15 );
   insert into contig_test( id, mon, state, account_type ) values ( 4445 , 4 , 0 , 15 );
   insert into contig_test( id, mon, state, account_type ) values ( 4445 , 5 , 1 , 15 );
   insert into contig_test( id, mon, state, account_type ) values ( 4445 , 6 , 1 , 15 );
   insert into contig_test( id, mon, state, account_type ) values ( 4445 , 7 , 1 , 15 );
   insert into contig_test( id, mon, state, account_type ) values ( 4445 , 8 , 1 , 15 );
   insert into contig_test( id, mon, state, account_type ) values ( 4445 , 9 , 1 , 15 );
   insert into contig_test( id, mon, state, account_type ) values ( 4445 , 10 , 0 , 15 );
   insert into contig_test( id, mon, state, account_type ) values ( 4445 , 11 , 1 , 15 );
   insert into contig_test( id, mon, state, account_type ) values ( 4445 , 12 , 1 , 15 );

   insert into contig_test( id, mon, state, account_type ) values ( 4446 , 1 , 1 , 15 );
   insert into contig_test( id, mon, state, account_type ) values ( 4446 , 2 , 1 , 15 );
   insert into contig_test( id, mon, state, account_type ) values ( 4446 , 3 , 1 , 30 );
   insert into contig_test( id, mon, state, account_type ) values ( 4446 , 4 , 0 , 15 );
   insert into contig_test( id, mon, state, account_type ) values ( 4446 , 5 , 1 , 30 );
   insert into contig_test( id, mon, state, account_type ) values ( 4446 , 6 , 1 , 30 );
   insert into contig_test( id, mon, state, account_type ) values ( 4446 , 7 , 1 , 30 );
   insert into contig_test( id, mon, state, account_type ) values ( 4446 , 8 , 1 , 30 );
   insert into contig_test( id, mon, state, account_type ) values ( 4446 , 9 , 1 , 30 );
   insert into contig_test( id, mon, state, account_type ) values ( 4446 , 10 , 0 , 15 );
   insert into contig_test( id, mon, state, account_type ) values ( 4446 , 11 , 1 , 15 );
   insert into contig_test( id, mon, state, account_type ) values ( 4446 , 12 , 1 , 15 );

   SELECT id ,
   MIN(mon) AS startdate ,
   max(mon) as enddate,
   MIN(state) AS state,
   account_type,
   COUNT(*) cnt
   FROM
   (
   SELECT @r := @r + (@state != state OR @state IS NULL) AS gn,
   @state := state AS sn,
   s.id,
   s.mon,
   s.state,
   s.account_type
   FROM
   (
   SELECT @r := 0,
   @state := NULL
   ) vars, contig_test s
   ORDER BY id, mon, state
   ) q
   where state = 1
   GROUP BY id, account_type, gn
   order by id, startdate;

   

   Richard

   16 Jul 15 at 17:23

Leave a Reply