sql to fill in missing data?

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.

--------------Solutions-------------

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);

Category:sql Time:2018-05-02 Views:0
Tags: sql oracle

Related post

  • Methodologies or algorithms for filling in missing data 2011-02-25

    I am dealing with datasets with missing data and need to be able to fill forward, backward, and gaps. So, for example, if I have data from Jan 1, 2000 to Dec 31, 2010, and some days are missing, when a user requests a timespan that begins before, end

  • SQL to CodeIgniter Array Missing Data Issue 2010-05-30

    $query = $this->db->query("SELECT t1.numberofbets, t1.profit, t2.seven_profit, t3.28profit, user.user_id, username, password, email, balance, user.date_added, activation_code, activated FROM user LEFT JOIN (SELECT user_id, SUM(amount_won) AS pr

  • SQL Server Sum of missing data 2014-06-25

    The below query displays sites against the total orders within last week. But if there is no order for a given site in last week, i should still see the site with a sum of zero. At the moment its only giving me four sites, thats because no order has

  • PHP - Is there a simple way to loop between two dates and fill in missing values? 2012-01-06

    I have 2 dates. Lets say they look like this. $start = 2010/12/24; $end = 2012/01/05; I query the database to look for visits between these two dates. I find some. I then populate an array called stats. $stats['2010/12/25'] = 50; $stats['2010/12/31']

  • How to make highcharts default to 0 for missing data 2012-05-01

    I have a time series at a 1 minute interval. I would like to display that in a chart with missing points as 0. I've found xAxis.ordinal and turned that off which displays the time series properly spaced out. The issue is that it draws lines between t

  • How to find missing data rows using SQL? 2009-12-05

    My problem: I got a MySQL database that stores a great amount of meteorological data in chronological order (New data are inserted every 10 min). Unfortunately there have been several blackouts and hence certain rows are missing. I recently managed t

  • Fill DataGridView with data from SQL 2011-08-14

    I want to fill a DataGridView with data returned from a SQL. So here is my code [I provided cause some people may think I'm asking for help before trying myself] I want the DataGridView to be filled by a data from SQL not to show all the records. The

  • SQL: Get all missing date records from database 2011-10-17

    I have a DB table with the following structure id | dateCreated | numOfUsers Where a typical row is 1, '2011-10-13 12:00:00', 4 My row contains data for the last 4 months, however quite a few days are missing and i'd like to find out using SQL the mi

  • MySQL how to fill missing dates in range? 2010-08-21

    I have a table with 2 columns, date and score. It has at most 30 entries, for each of the last 30 days one. date score ----------------- 1.8.2010 19 2.8.2010 21 4.8.2010 14 7.8.2010 10 10.8.2010 14 My problem is that some dates are missing - I want t

  • R: filling missing data in xts stock price object 2011-05-20

    I have 1 minute intraday price data which has missing data points. As such I want to fill them. I read through the suggestions in the following post and tried a similar procedure: R: Filling missing dates in a time series ??? In my case the missing d

  • SQL Query for adding missing values for a field between two dates? 2010-08-20

    Ok, So I have these two tables - BioUser- UserId,Weight,DateAdded DimDate-Date // It has basically all the dates for any period..its basically a table with all dates till 2050 Now the BioUser table has entries for weight of a user but not for everyda

  • How to find missing data either in array or in mySQL table? 2008-12-13

    I have an array filled with values (twitter ids) and I would like to find the missing data between the lowest id and the highest id? Any care to share a simple function or idea on how to do this? Also, I was wondering if I can do the same with mySQL?

  • Predicting missing data values in a database 2009-07-23

    I have a database, consisting of a whole bunch of records (around 600,000) where some of the records have certain fields missing. My goal is to find a way to predict what the missing data values should be (so I can fill them in) based on the existing

  • Delphi: Clientdataset: EDatabaseError: Missing Data-Package using Synapse 2009-08-14

    From the client I am sending a string to the server what he should send me back. This time its a stream created by a ClientDataSet. Unfortunately receiving (or sending??) does not work at the moment. Note: I am using Synapse with blocking sockets. Th

  • How can I avoid NULLs in my database, while also representing missing data? 2010-12-02

    In SQL and Relational Theory (C.J. Date, 2009) chapter 4 advocates avoiding duplicate rows, and also to avoid NULL attributes in the data we store. While I have no troubles avoiding duplicate rows, I am struggling to see how I can model data without

  • SQL query that returns all dates not used in a table 2010-12-30

    So lets say I have some records that look like: 2011-01-01 Cat 2011-01-02 Dog 2011-01-04 Horse 2011-01-06 Lion How can I construct a query that will return 2011-01-03 and 2011-01-05, ie the unused dates. I postdate blogs into the future and I want a

  • Standard use of 'Z' instead of NULL to represent missing data? 2011-07-09

    Outside of the argument of whether or not NULLs should ever be used: I am responsible for an existing database that uses NULL to mean "missing or never entered" data. It is different from empty string, which means "a user set this value, and they sel

  • Isolate employees based on missing dates 2012-03-21

    I have a table with labor details for several employees. It has EmplName, Hrs, TransDate, etc. The TransDate is the week ending date (Friday). If an employee terminates in the middle of a month, I need to have a list of these employees and their last

  • Problem of SQLite3::SQLException: SQL logic error or missing database 2008-12-25

    SQLite3::SQLException: SQL logic error or missing database error when do insert, update and delete operation to tables from browser( that means the create, update and destroy action is failed but the show action is fine ), the same operation in conso

Copyright (C) pcaskme.com, All Rights Reserved.

processed in 0.805 (s). 13 q(s)