mysql从一个连续时间段的表中读取缺少数据

数据库有一个表 想从这个表中某一天内连续时间段比如recevietime字段下查询缺少哪分钟的数据

img

1.先建个表用于存放 某天的所有分钟

-- Create table
create table T_TEST
(
  receivetime DATE,
  id          INTEGER
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 80
    next 1
    minextents 1
    maxextents unlimited
  );
-- 插入某一天   以2018-03-23为例
insert into  T_TEST (receivetime,id)
SELECT TO_DATE('2018-03-23', 'YYYY-MM-DD') + (ROWNUM - 1) / (24 * 60)   as time_mm,ROWNUM
FROM DUAL CONNECT BY ROWNUM <= 24*60

img

2.用表直接联查比对

---测试   删除t_demo中的几个时间   用t_demo(就是相当于你发出的那个业务表 MHAPP.ELE01_AW0S)

 SELECT * from t_test t left join t_demo d 
 
 on  to_char(t.receivetime, 'YYYY-MM-DD hh24:mi') =  to_char(d.receivetime, 'YYYY-MM-DD hh24:mi')
 
 where  to_char(d.receivetime, 'YYYY-MM-DD hh24:mi') is null

img

row_number排序后，错位关联，判断时间差

其实就是做出相邻的时间差，分钟相差>=1的就是缺少的分钟了呀
首先
#取出所有数据从2开始
select a.*,(@i := @i + 1) as row_num from table a,(select @i := 1) d order by recevietime

#取出所有数据从1开始
select a.*,(@j := @j + 1) as row_num from table a,(select @j := 0) c order by recevietime
第三部
就是将上面的2个做邻近的时间差了

select A.recevietime,TIMESTAMPDIFF(MINUTE,A.recevietime,B.recevietime) sub_minute
from(
    select a.*,(@i := @i + 1) as row_num from table a,(select @i := 1) d order by recevietime
) as A LEFT JOIN (
    select a.*,(@j := @j + 1) as row_num from table a,(select @j := 0) c order by recevietime
)as B on A.row_num =B.row_num

应该搞定了把，窗口函数 rank() 对时间进行操作 ，比较时间