CODEWITHSUNDEEP
mysqlsqlgaps-and-islands
Barzo
Barzo

Reputation: 1049

Find holes in mysql table where ids are not unique

a MySql table is defined as:

CREATE TABLE tbl_misure_30m (
  m_rcd_id          INT NOT NULL AUTO_INCREMENT ,
  m_fon_id          INT UNSIGNED,
  m_timestamp       TIMESTAMP,
  m_fon_rcd_id      INT UNSIGNED,
  m_fon_Leq         FLOAT(4,2),
  m_fon_LsMax       FLOAT(4,2),
  m_Leq_state       INT,
  m_LsMax_state     INT,
  m_fon_mem_block   INT,

  INDEX fon_key (m_fon_id),
  FOREIGN KEY (m_fon_id) REFERENCES tbl_users(fon_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,

  PRIMARY KEY ( m_rcd_id )

) ENGINE = InnoDB;

where:

Unfortunately the database design cannot be changed.

Now, I need to verify that the column m_fon_rcd_id (for each m_fon_id) does not contains holes (in which case I need to know the range).

What I would like to have is a result-set like: | gap_starts_at | gap_ends_at | m_fon_id |

I found this answer but it takes for granted that the various id are unique :

SELECT (t1.m_fon_rcd_id + 1) as gap_starts_at, 
       (SELECT MIN(t3.m_fon_rcd_id) -1 FROM tbl_misure_30m t3 WHERE t3.m_fon_rcd_id > t1.m_fon_rcd_id) as gap_ends_at
FROM tbl_misure_30m t1
WHERE NOT EXISTS (SELECT t2.m_fon_rcd_id FROM tbl_misure_30m t2 WHERE t2.m_fon_rcd_id = t1.m_fon_rcd_id + 1 )
HAVING gap_ends_at IS NOT NULL

Any suggestions?

Thanks in advance!

Upvotes: 1

Views: 189

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

I would approach this by getting the next value. Then do some arithmetic on that:

select m_fon_rcd_id + 1 as gapstart, next_m_fon_rcd_id - 1 as gap_ends,
       (next_m_fon_rcd_id - m_fon_rcd_id - 1) as gap_length
from (select m_fon_rcd_id,
             (select m2.m_fon_rcd_id
              from tbl_misure_30m m2
              where m2.m_fon_rcd_id > m.m_fon_rcd_id
              order by m_fon_rcd_id
              limit 1
             ) as next_m_fon_rcd_id
      from tbl_misure_30m m
     ) m
where next_m_fon_rcd_id > m_fon_rcd_id + 1;

EDIT:

If you want to do this gaps within m_fon_id, you can just add it in to various parts of the query:

select m_fon_id, m_fon_rcd_id + 1 as gapstart, next_m_fon_rcd_id - 1 as gap_ends,
       (next_m_fon_rcd_id - m_fon_rcd_id - 1) as gap_length
from (select m_fon_rcd_id,
             (select m2.m_fon_rcd_id
              from tbl_misure_30m m2
              where m2.m_fon_id = m.m_fon_id and
                    m2.m_fon_rcd_id > m.m_fon_rcd_id
              order by m_fon_rcd_id
              limit 1
             ) as next_m_fon_rcd_id
      from tbl_misure_30m m
     ) m
where next_m_fon_rcd_id > m_fon_rcd_id + 1;

Upvotes: 1

Related Questions