Hello,
i need to fill in the gaps in the sensor reading.
create table a(id number not null, typeid number not null, value varchar2(3) not null, ts timestamp not null);
Data:
Insert into A (ID,TYPEID,VAL,TS) values (1,1,10,to_timestamp('2017-08-17 14:20:38.346','DD-MON-RR HH.MI.SSXFF AM'));
Insert into A (ID,TYPEID,VAL,TS) values (2,2,1,to_timestamp('2017-08-17 14:20:19.36','DD-MON-RR HH.MI.SSXFF AM'));
Insert into A (ID,TYPEID,VAL,TS) values (3,1,15,to_timestamp('2017-08-17 14:21:56.554','DD-MON-RR HH.MI.SSXFF AM'));
Insert into A (ID,TYPEID,VAL,TS) values (4,1,20,to_timestamp('2017-08-17 14:24:08.113','DD-MON-RR HH.MI.SSXFF AM'));
Insert into A (ID,TYPEID,VAL,TS) values (5,2,10,to_timestamp('2017-08-17 14:24:29.538','DD-MON-RR HH.MI.SSXFF AM'));
Data result set is:
1 1 10 2017-08-17 14:20:38.346
2 2 1 2017-08-17 14:20:19.36
3 1 15 2017-08-17 14:21:56.554
4 1 20 2017-08-17 14:24:08.113
5 2 10 2017-08-17 14:24:29.538
The end result i need is - for each minute i want to have an entry for both typeids (1 and 2 ). If there is no data in the current minute - use values (val) from the previous minute.
1 1 10 2017-08-17 14:20:38.346
2 2 1 2017-08-17 14:20:19.36
3 1 15 2017-08-17 14:21:56.554
6 2 1 2017-08-17 14:21:56.554
7 1 15 2017-08-17 14:22:56.554
8 2 1 2017-08-17 14:22:56.554
9 1 15 2017-08-17 14:23:56.554
10 2 1 2017-08-17 14:23:56.554
4 1 20 2017-08-17 14:24:08.113
5 2 10 2017-08-17 14:24:29.538
the timestamps's milliseconds are not essential - can be anything.
Hope makes sense,
Thank you