How to find irregular increasements on mySQL

Question

My Table:

id         value
1          25
2          96
5          47
6          41
9          78
10         23

How to find irregular increasements(or not existence rows) like following:

Result: 3, 4, 7, 8

I dont want to read each one because of having 50k rows. What do you suggest I do?

@Jens I need to check every day missing data and i have to find them and save into db. — Bora, Apr 09 '15 at 07:15
One thing I can suggest is to generate a sequence and do outer join against your table. The missing values will have a *null* which indicates your missing ID. — John Woo, Apr 09 '15 at 07:15
@JohnWoo Yes yes yes.. I can join another table with outer. Thanks, i'm trying now.. — Bora, Apr 09 '15 at 07:17

score 0 · Answer 1 · edited May 23 '17 at 12:06

Simple approach

Ingredients:

Generator table
outer join

SQL Fiddle

MySQL 5.6 Schema Setup:

create table t ( id int, value int );
insert into t values ( 1, 12 );
insert into t values ( 3, 12 );
insert into t values ( 7, 12 );
insert into t values ( 9, 12 );

Query 1:

select generator.row
from t right outer join
( 
  SELECT @row := @row + 1 as row FROM 
  (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t,
  (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t2, 
  (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t3, 
  (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t4, 
  (SELECT @row:=0) t5
) generator
on generator.row = t.id
where t.id is null and generator.row < ( select max( t.id) from t )

Results:

| row |
|-----|
|   2 |
|   4 |
|   5 |
|   6 |
|   8 |

Complex approach

If you need your gap limits, do you need some ingredients:

MySQL Variables
Generator table with at least your max id

Here your query

MySQL 5.6 Schema Setup:

create table t ( id int, value int );
insert into t values ( 1, 12 );
insert into t values ( 3, 12 );
insert into t values ( 7, 12 );
insert into t values ( 9, 12 );

The query:

select generator.row, rFrom, rTo
from (
  SELECT @r := @r + 1 as r, 
         case 
            when @r < t.id then @r
            else Null
         end as rFrom,
         case 
            when @r < t.id then t.id - 1
            else Null
         end as rTo,
         @r := t.id 
  FROM t , (SELECT @r:=0) r
) gaps inner join
( 
  SELECT @row := @row + 1 as row FROM 
  (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t,
  (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t2, 
  (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t3, 
  (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6 union all select 6 union all select 7 union all select 8 union all select 9) t4, 
  (SELECT @row:=0) t5
) generator
on generator.row between gaps.rFrom and gaps.rTo
where rFrom is not null

Results:

| row | rFrom | rTo |
|-----|-------|-----|
|   2 |     2 |   2 |
|   4 |     4 |   6 |
|   5 |     4 |   6 |
|   6 |     4 |   6 |
|   8 |     8 |   8 |

First subquery looks for gaps intervals, second one is a generator to generate missing ids. Be free to execute both queries one by one to understand it.

How to find irregular increasements on mySQL

1 Answers1

Simple approach

Complex approach