How to find the missing data from table using Sql in Mysql?

Question

Every five minutes, my system will produce a file and I will store the file name in my table.

I name the file name like:

studentA202106011940-202106011945.zip
studentA202106011945-202106011950.zip

So in one day, I will store 288 files in my table.

Now I can search the file in some day like:

select * 
from studentData 
where LEFT(SUBSTRING_INDEX(fileName, '-',-1),8)='29210601'

How can I quickly find the missing data from table?

Please define what "missing data" means. Sample data and desired results would really help. — Gordon Linoff
– Gordon Linoff, Commented Jun 9, 2021 at 12:00
Write a recursive query to generate all file names, then use NOT EXISTS to see which ones are missing. — Thorsten Kettner
– Thorsten Kettner, Commented Jun 9, 2021 at 12:02
How can I quickly find the missing data from table? "Missing data" is the filename which is absent for specified data? — Akina
– Akina, Commented Jun 9, 2021 at 12:04
Write a recursive query to generate all file names ,you means to create a temp table?@ThorstenKettner — flower
– flower, Commented Jun 9, 2021 at 12:16
No, MySQL 5.7 is old. It was MySQL 8 when they introduced this and several other features. It is worth upgrading :-) — Thorsten Kettner
– Thorsten Kettner, Commented Jun 9, 2021 at 14:30

Gordon Linoff · Accepted Answer · 2021-06-09 12:58:01Z

3

You can check if the next starttime is not the same the a given rows end time. This identifies the first missing row in a sequence of adjacent missing rows:

with f as (
      select f.*,
             right(substring_index(filename, '-', 1), 12) as starttime,
             substring_index(substring_index(filename, '-', -1), '.', 1) as endtime
      from files f
     ) 
select f.*, f.endtime as first_missing_starttime
from (select f.*,
             lead(starttime) over (order by starttime) as next_startttime
      from f
     ) f
where f.next_starttime <> f.endtime;

edited Jun 9, 2021 at 12:58

answered Jun 9, 2021 at 12:06

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Gordon Linoff Over a year ago

@flower . . . This requires MySQL 8+.

flower Over a year ago

I try to rewrite your sql using view.

Gordon Linoff Over a year ago

@flower . . . This logic would be very tricky -- and expensive -- to put into a view in pre-8.0 versions of MySQL. You might consider upgrading MySQL.

Strawberry · Accepted Answer · 2021-06-09 15:39:55Z

0

I'm sure this can be written more succinctly, but anyway...

For 5.7, I have a table of integers (i) from 0 to 9...

DROP TABLE IF EXISTS my_table;
CREATE TABLE my_table
(my_string CHAR(12) NOT NULL PRIMARY KEY);

INSERT INTO my_table VALUES
('202106011945'),('202106011950');

SELECT SEC_TO_TIME((i4.i*1000+i3.i*100+i2.i*10+i1.i) *300) t
   FROM ints i1
   JOIN ints i2
   JOIN ints i3
   JOIN ints i4 
   LEFT
   JOIN my_table x
     ON TIME(STR_TO_DATE(CONCAT(x.my_string,00),'%Y%m%d%H%i%s')) = SEC_TO_TIME((i4.i*1000+i3.i*100+i2.i*10+i1.i) *300)
  WHERE i4.i*1000+i3.i*100+i2.i*10+i1.i <288
    AND x.my_string IS NULL;

+----------+
| t        |
+----------+
| 00:00:00 |
...
| 18:45:00 |
| 18:50:00 |
| 18:55:00 |
| 19:00:00 |
| 19:05:00 |
| 19:10:00 |
| 19:15:00 |
| 19:20:00 |
| 19:25:00 |
| 19:30:00 |
| 19:35:00 |
| 19:40:00 | -- we can see that
| 19:55:00 | -- it's working
| 20:00:00 |
| 20:05:00 |
...
+----------+

answered Jun 9, 2021 at 15:39

Strawberry

34k14 gold badges43 silver badges57 bronze badges

2 Comments

flower Over a year ago

CREATE TABLE ints (i int(12) NOT NULL PRIMARY KEY); INSERT INTO ints VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9); I run the sql and the result is null

Strawberry Over a year ago

I don't think INT(12) is a thing. Anyway, you forgot 0

Collectives™ on Stack Overflow

How to find the missing data from table using Sql in Mysql?

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related