sql to fill in missing data?

Question

Giving the following query"

select ping_date,packet_loss,ping_avg,ping_source,ping_destination
from router_ping 
where ping_date > sysdate - NUMTODSINTERVAL (24, 'HOUR') AND (ping_source = 'zja68f-wr2' AND ping_destination = 'zja68f-wr2'  OR ping_source = 'zja68f-wr2' AND ping_destination = 'zja68f-wr2' )
order by TRUNC(ping_date), ping_date ASC

Will output:

 PACKET_LOSS    PING_AVG    PING_DATE   PING_DESTINATION    PING_SOURCE
0                 273   2015-05-07 17:40:16.0   zja68f-wr2      zfr11f-wr2
0                 273   2015-05-07 17:45:27.0   zja68f-wr2      zfr11f-wr2
0                 273   2015-05-07 17:50:15.0   zja68f-wr2      zfr11f-wr2

0                 273   2015-05-07 18:00:19.0   zja68f-wr2      zfr11f-wr2
0                 273   2015-05-07 18:05:18.0   zja68f-wr2      zfr11f-wr2
0                 273   2015-05-07 18:10:15.0   zja68f-wr2      zfr11f-wr2
0                 273   2015-05-07 18:15:12.0   zja68f-wr2      zfr11f-wr2
0                 273   2015-05-07 18:20:13.0   zja68f-wr2      zfr11f-wr2
0                 273   2015-05-07 18:25:14.0   zja68f-wr2      zfr11f-wr2

This result there is a missing row for 2015-05-07 17:55:xx.x . Can this be dynamically added for any missing rows? adding a null value for PACKET_LOSS and PING_AVG column.

You asked, "Can this be dynamically added for any missing rows?" yes. You have to generate a table with all the potential date/time intervals for the range in question,and then LEFT join to your router_ping table. One way to do this is with recursion (Recursive CTE) that would give you dates/times from teh min of router_ping to the max of router ping generating the a value for each 5 minute interval. then Join back to ping_date based on YYYY-MM-DD HH:MM drop the rest. Here's an existing example for just dates. http://stackoverflow.com/questions/9592930/oracle-select-missing-dates — xQbert, May 08 '15 at 20:56
Here's another example using a more dynamic approach http://stackoverflow.com/questions/23300303/mysql-single-table-select-last-7-days-and-include-empty-rows/23301236#23301236 — xQbert, May 08 '15 at 20:59

Nick · Accepted Answer · 2015-05-09T02:50:03.567

You might want to consider if my rather arduous solution is worth the complexity or not. However, by using a connect by "trick" in Oracle, we can simulate this behavior by incrementing in 5 minute intervals to dynamically create the time table. Then joining it to he router_ping table and by noting the row before and after me to determine if I'm in the middle of "router ping sequence" and show data as appropriate. (Note: I had to change your where clause as I wasn't getting any data returned based on your output.)

select ping_date, packet_loss, ping_avg, coalesce(ping_source, previous_ping_source), coalesce(ping_destination, previous_ping_destination)
from
(
select coalesce(ping_date, ping_interval) as ping_date, 
coalesce(pings.packet_loss, dates.packet_loss) as packet_loss,
coalesce(pings.ping_avg, dates.ping_avg) as ping_avg,
pings.ping_source, 
pings.ping_destination,
coalesce(pings.ping_source, lag(pings.ping_source, 1, 0) over (order by ping_interval ASC)) previous_ping_source,
coalesce(pings.ping_destination, lag(pings.ping_destination, 1, 0) over (order by ping_interval ASC)) previous_ping_destination,
coalesce(pings.ping_source, lead(pings.ping_source, 1, 0) over (order by ping_interval ASC)) next_ping_source
from
(select ping_date,packet_loss,ping_avg,ping_source,ping_destination
from router_ping 
where ping_date > sysdate - NUMTODSINTERVAL (48, 'HOUR') AND (ping_source = 'zfr11f-wr2' AND ping_destination = 'zja68f-wr2'  OR ping_source = 'zja68f-wr2' AND ping_destination = 'zja68f-wr2' )
) pings
right outer join
(select (select min(ping_date) from router_ping) + (interval '5' minute) * level as ping_interval,
0 as packet_loss, 0 as ping_avg, 'NO DATA ROW' as data_indicator
from dual
connect by level <= 20) dates
on (pings.ping_date = dates.ping_interval))
where (previous_ping_source is not null and next_ping_source is not null)
order by ping_date
;

Sample data and table used for example:

create table router_ping
(ping_date timestamp,
ping_destination varchar2(50),
ping_source varchar2(50),
ping_avg    number,
packet_loss number);

insert into router_ping 
(ping_date, ping_destination, ping_source, ping_avg, packet_loss)
values
(to_date('2015-05-08 17:40:16','YYYY-MM-DD HH24:MI:SS'), 'zja68f-wr2', 'zfr11f-wr2', 273, 0);

insert into router_ping 
(ping_date, ping_destination, ping_source, ping_avg, packet_loss)
values
(to_date('2015-05-08 17:45:16','YYYY-MM-DD HH24:MI:SS'), 'zja68f-wr2', 'zfr11f-wr2', 273, 0);

insert into router_ping 
(ping_date, ping_destination, ping_source, ping_avg, packet_loss)
values
(to_date('2015-05-08 17:50:16','YYYY-MM-DD HH24:MI:SS'), 'zja68f-wr2', 'zfr11f-wr2', 273, 0);

insert into router_ping 
(ping_date, ping_destination, ping_source, ping_avg, packet_loss)
values
(to_date('2015-05-08 18:00:16','YYYY-MM-DD HH24:MI:SS'), 'zja68f-wr2', 'zfr11f-wr2', 273, 0);

sql to fill in missing data?

1 Answers1