CODEWITHSUNDEEP
oracle-databasedate
user2276280
user2276280

Reputation: 601

In Oracle, get a count of the number of missing intervals across date ranges

In an Oracle table I have data coming in on 15 minute intervals across many different unique IDs. For example, the data might look something like this:

ObjectID     Date
123          1/1/2016 00:00:00
123          1/1/2016 00:15:00
123          1/1/2016 00:30:00
123          1/1/2016 00:45:00
123          1/1/2016 01:00:00
456          1/1/2016 00:00:00
456          1/1/2016 00:15:00
456          1/1/2016 00:30:00
456          1/1/2016 00:45:00
456          1/1/2016 01:00:00
789          1/1/2016 00:00:00
789          1/1/2016 00:15:00
789          1/1/2016 00:30:00
789          1/1/2016 00:45:00
789          1/1/2016 01:00:00

This is a very simple example. In reality, the objectIDs are not sorted in a uniform order and there's a possibility of missing intervals. Thus the data really looks more like this:

ObjectID     Date
456          1/1/2016 00:15:00
456          1/1/2016 00:30:00
123          1/1/2016 00:30:00
123          1/1/2016 00:45:00
123          1/1/2016 01:00:00
456          1/1/2016 00:45:00
456          1/1/2016 01:00:00
789          1/1/2016 00:45:00
789          1/1/2016 01:00:00
789          1/1/2016 00:00:00
789          1/1/2016 00:15:00
789          1/1/2016 00:30:00

I would like to be able to get a count of the missing intervals across all of the objectIDs for a specific date range. In the above, there are three missing intervals:

123          1/1/2016 00:00:00
123          1/1/2016 00:15:00
456          1/1/2016 00:00:00

I'm looking for a query that would catch these and return a count of 3 if given a date range of 1/1/2016 00:00:00 - 1/1/2016 1:00:00.

Upvotes: 1

Views: 64

Answers (3)

Marco Polo
Marco Polo

Reputation: 738

Let me take a shot at it.... (Aleksej i took off from your answer).

with 
--
-- Test case supplied
--
test(ObjectID, Date_) as
(
  select 456, to_date('1/1/2016 00:15:00', 'dd/mm/yyyy hh24:mi:ss') from dual  union all
  select 456, to_date('1/1/2016 00:30:00', 'dd/mm/yyyy hh24:mi:ss') from dual  union all
  select 123, to_date('1/1/2016 00:30:00', 'dd/mm/yyyy hh24:mi:ss') from dual  union all
  select 123, to_date('1/1/2016 00:45:00', 'dd/mm/yyyy hh24:mi:ss') from dual  union all
  select 123, to_date('1/1/2016 01:00:00', 'dd/mm/yyyy hh24:mi:ss') from dual  union all
  select 456, to_date('1/1/2016 00:45:00', 'dd/mm/yyyy hh24:mi:ss') from dual  union all
  select 456, to_date('1/1/2016 01:00:00', 'dd/mm/yyyy hh24:mi:ss') from dual  union all
  select 789, to_date('1/1/2016 00:45:00', 'dd/mm/yyyy hh24:mi:ss') from dual  union all
  select 789, to_date('1/1/2016 01:00:00', 'dd/mm/yyyy hh24:mi:ss') from dual  union all
  select 789, to_date('1/1/2016 00:00:00', 'dd/mm/yyyy hh24:mi:ss') from dual  union all
  select 789, to_date('1/1/2016 00:15:00', 'dd/mm/yyyy hh24:mi:ss') from dual  union all
  select 789, to_date('1/1/2016 00:30:00', 'dd/mm/yyyy hh24:mi:ss') from dual
),
--
-- Intervals generates all possible 15 minutes intervals within the given parameters
--
intervals (Date_) as
(
  select to_date('1/1/2016 00:00:00', 'dd/mm/yyyy hh24:mi:ss') + ((15/1440) * (level - 1))
  from dual
connect by level <= 24 * (to_date('1/1/2016 01:00:00', 'dd/mm/yyyy hh24:mi:ss') - to_date('1/1/2016 00:00:00', 'dd/mm/yyyy hh24:mi:ss')) * 4
)
--
-- Simple minus to find the missing intervals
--
select distinct test.ObjectID, to_char(intervals.Date_ ,'YYYY/MM/DD HH24:MI:SS') dt
  from test,
       intervals
minus
select test.ObjectID, to_char(test.Date_,'YYYY/MM/DD HH24:MI:SS') dt
  from test
/

When I execute the missing intervals:

  OBJECTID DT
---------- -------------------
       123 2016/01/01 00:00:00
       123 2016/01/01 00:15:00
       456 2016/01/01 00:00:00

Upvotes: 1

Aleksej
Aleksej

Reputation: 22959

If you only need counting, an approach could be count the expected intervals and make the difference with the number of intervals really found:

with test(ObjectID, Date_) as
(
  select 456, to_date('1/1/2016 00:15:00', 'dd/mm/yyyy hh24:mi:ss') from dual  union all
  select 456, to_date('1/1/2016 00:30:00', 'dd/mm/yyyy hh24:mi:ss') from dual  union all 
  select 123, to_date('1/1/2016 00:30:00', 'dd/mm/yyyy hh24:mi:ss') from dual  union all
  select 123, to_date('1/1/2016 00:45:00', 'dd/mm/yyyy hh24:mi:ss') from dual  union all
  select 123, to_date('1/1/2016 01:00:00', 'dd/mm/yyyy hh24:mi:ss') from dual  union all
  select 456, to_date('1/1/2016 00:45:00', 'dd/mm/yyyy hh24:mi:ss') from dual  union all
  select 456, to_date('1/1/2016 01:00:00', 'dd/mm/yyyy hh24:mi:ss') from dual  union all
  select 789, to_date('1/1/2016 00:45:00', 'dd/mm/yyyy hh24:mi:ss') from dual  union all
  select 789, to_date('1/1/2016 01:00:00', 'dd/mm/yyyy hh24:mi:ss') from dual  union all
  select 789, to_date('1/1/2016 00:00:00', 'dd/mm/yyyy hh24:mi:ss') from dual  union all
  select 789, to_date('1/1/2016 00:15:00', 'dd/mm/yyyy hh24:mi:ss') from dual  union all
  select 789, to_date('1/1/2016 00:30:00', 'dd/mm/yyyy hh24:mi:ss') from dual 
),
input_dates(start_date, end_date) as
(  select  to_date('1/1/2016 00:00:00', 'dd/mm/yyyy hh24:mi:ss'),
           to_date('1/1/2016 01:00:00', 'dd/mm/yyyy hh24:mi:ss') from dual) 
--
select (count( distinct objectId) * (input_dates.end_date - input_dates.start_date) * 24 * 5) 
       -
       sum(
            case when date_ between input_dates.start_date and input_dates.end_date
                      then 1
                      else 0
            end
          ) as missing
from test, input_dates

In case of an objectID that is in your table, but does not have any interval in the input period, this will return 5, to say that it's missing all the intervals. If you want to check only missing intervals on IDs having at least one interval in the period, you can simply remove the BETWEEN condition from the SUM and add in as WHERE clause

Upvotes: 1

OldProgrammer
OldProgrammer

Reputation: 12169

I think something like this may work. May be more efficient method.

select count(*) from your_table
where your_table.date not in
(
SELECT dateval
FROM
  ( WITH dates AS
  (SELECT to_date('01/01/2016 00:00:00','MM/DD/YYYY HH24:MI:SS') dstart,
    to_date('01/01/2016 01:00:00','MM/DD/YYYY HH24:MI:SS') dend
  FROM dual
  )
SELECT dstart + rownum/96.0 dateval
FROM dates
  CONNECT BY rownum <=
  (SELECT (dend - dstart)*96 FROM dates
  )
  )
)

The "connect by" counts the number of 15 minute intervals between the start/end date, then generates each of the date/times in 15 minute increments starting with the start date/time.

Upvotes: 1

Related Questions