9 Replies Latest reply: Mar 15, 2013 2:30 AM by jsha RSS

    select records closest to 15 minute time intervals

    jsha
      Hello,

      I am trying to figure out a way to select records from a log table at 15 minute intervals (approx) based on a timestamp value in the table. The table has the following columns RECORD_ID, EVENT_TIME, EVENT_VALUE. Id like to select the records that are closest to 15 minute intervals for the last 24 hours starting at midnight.

      So if there were records with the following values:

      1, 13/03/2013 00:13:00, 100
      2, 13/03/2013 00:16:00, 200
      3, 13/03/2013 00:18:00, 300
      4, 13/03/2013 00:28:00, 400
      5, 13/03/2013 00:31:00, 500
      6, 13/03/2013 00:41:00, 600
      7, 13/03/2013 00:48:00, 700

      The query should return
      1, 13/03/2013 00:13:00, 100 -- Closest to 00:00
      2, 13/03/2013 00:16:00, 200 -- Closest to 00:15
      5, 13/03/2013 00:31:00, 500 -- Closest to 00:30
      7, 13/03/2013 00:48:00, 700 -- Closest to 00:45

      Any help is greatly appreciated. I've been going around in circles with this all day!!!
        • 1. Re: select records closest to 15 minute time intervals
          rp0428
          >
          I am trying to figure out a way to select records from a log table at 15 minute intervals (approx) based on a timestamp value in the table. The table has the following columns RECORD_ID, EVENT_TIME, EVENT_VALUE. Id like to select the records that are closest to 15 minute intervals for the last 24 hours starting at midnight.
          >
          You need to fill in the missing information about the requirements.

          A single record could be the 'closest' to two 15 minute periods.

          For example if you remove the 16 minute record then the 13 minute record is closer than 18 minutes to the 15 minute interval but you said it is also closest to 0 minutes.

          Also there could be more than one record that is 'closest': both lower than the next interval or one 1 minute lower and the other 1 minute higher.

          Can you provide sample data for all of the boundary conditions and indicate how the selection should be made?
          • 2. Re: select records closest to 15 minute time intervals
            jeneesh
            This can be a starting point..
            SQL> with t as
              2  (
              3  select 1 event_id, to_date('13/03/2013 00:13:00','dd/mm/yyyy hh24:mi:ss') event_time, 100 event_value
              4  from dual union all
              5  select 2, to_date('13/03/2013 10:16:00','dd/mm/yyyy hh24:mi:ss'), 200
              6  from dual union all
              7  select 3, to_date('13/03/2013 00:18:00','dd/mm/yyyy hh24:mi:ss'), 300
              8  from dual
              9  ),
             10  t_rounded as
             11  (
             12  select event_id,event_time,event_value,
             13     trunc(event_time,'dd') + round(to_char(event_time,'sssss') / 900) / 96 r_time
             14  from t
             15  )
             16  select event_id,event_time,event_value
             17  from
             18  (
             19     select event_id,event_time,event_value,
             20           rank() over(partition by r_time
             21             order by abs(event_time-r_time)) rn
             22     from t_rounded
             23  )
             24  where rn = 1;
            
              EVENT_ID EVENT_TIME           EVENT_VALUE
            ---------- -------------------- -----------
                     1 13-mar-2013 00:13:00         100
                     2 13-mar-2013 10:16:00         200
            • 3. Re: select records closest to 15 minute time intervals
              jeneesh
              Or to get your output..
              SQL> with t as
                2  (
                3  select 1 event_id, to_date('13/03/2013 00:13:00','dd/mm/yyyy hh24:mi:ss') event_time, 100 event_value
                4  from dual union all
                5  select 2, to_date('13/03/2013 00:16:00','dd/mm/yyyy hh24:mi:ss'), 200
                6  from dual union all
                7  select 3, to_date('13/03/2013 00:18:00','dd/mm/yyyy hh24:mi:ss'), 300
                8  from dual union all
                9  select 4, to_date('13/03/2013 00:28:00','dd/mm/yyyy hh24:mi:ss'), 400
               10  from dual union all
               11  select 5, to_date('13/03/2013 00:31:00','dd/mm/yyyy hh24:mi:ss'), 500
               12  from dual union all
               13  select 6, to_date('13/03/2013 00:41:00','dd/mm/yyyy hh24:mi:ss'), 600
               14  from dual union all
               15  select 7, to_date('13/03/2013 00:48:00','dd/mm/yyyy hh24:mi:ss'), 700
               16  from dual
               17  ),
               18  t_rounded as
               19  (
               20  select event_id,event_time,event_value,
               21     trunc(event_time,'dd') + floor(to_char(event_time,'sssss') / 900) / 96 r_time
               22  from t
               23  )
               24  select event_id,event_time,event_value
               25  from
               26  (
               27     select event_id,event_time,event_value,
               28           rank() over(partition by r_time
               29             order by abs(event_time-r_time)) rn
               30     from t_rounded
               31  )
               32  where rn = 1
               33  order by event_time;
              
                EVENT_ID EVENT_TIME           EVENT_VALUE
              ---------- -------------------- -----------
                       1 13-mar-2013 00:13:00         100
                       2 13-mar-2013 00:16:00         200
                       5 13-mar-2013 00:31:00         500
                       7 13-mar-2013 00:48:00         700
              • 4. Re: select records closest to 15 minute time intervals
                jsha
                Thank you for your response.

                In reality the data will have the same record_id recorded at many different times. The idea is to chose 4 records for each record_id per hour using the 15 interval as a guide. It its ok to select the same record twice if it is closest to two 15 minute periods. In the event of multiple records being equidistant the most recent can be chosen.

                Therefore for data:

                1, 13/03/2013 00:13:00, 100
                1, 13/03/2013 00:18:00, 200
                1, 13/03/2013 00:41:00, 300
                1, 13/03/2013 00:51:00, 400

                Should return the 4 records (per hour) deemed closest to the intervals.

                1, 13/03/2013 00:13:00, 100 -- Closest to 00:00
                1, 13/03/2013 00:18:00, 200 -- Equidistant from 00:15 as 00:13 but more recent
                1, 13/03/2013 00:41:00, 300 -- Closest to 00:30
                1, 13/03/2013 00:41:00, 300 -- Closest to 00:45

                Thanks - J
                • 5. Re: select records closest to 15 minute time intervals
                  chris227
                  with data as  (
                    select 1 event_id, to_date('13/03/2013 00:13:00','dd/mm/yyyy hh24:mi:ss') event_time, 100 event_value
                    from dual union all
                    select 2, to_date('13/03/2013 00:16:00','dd/mm/yyyy hh24:mi:ss'), 200
                    from dual union all
                    select 3, to_date('13/03/2013 00:18:00','dd/mm/yyyy hh24:mi:ss'), 300
                    from dual union all
                    select 4, to_date('13/03/2013 00:28:00','dd/mm/yyyy hh24:mi:ss'), 400
                    from dual union all
                    select 5, to_date('13/03/2013 00:31:00','dd/mm/yyyy hh24:mi:ss'), 500
                    from dual union all
                    select 6, to_date('13/03/2013 00:41:00','dd/mm/yyyy hh24:mi:ss'), 600
                    from dual union all
                    select 7, to_date('13/03/2013 00:48:00','dd/mm/yyyy hh24:mi:ss'), 700
                    from dual
                  )
                  , ranking as (
                  select
                     event_id
                    ,event_time
                    ,event_value
                    ,row_number() over (partition by 
                                              floor(to_number(to_char(event_time, 'SSSSS'))/900)
                                        order by
                                        mod( extract(MINUTE from (cast(event_time as timestamp))), 15)
                                        ) rnk
                   from data
                  )
                  select
                     event_id
                    ,event_time
                    ,event_value
                  from ranking
                  where
                  rnk = 1
                  
                  EVENT_ID     EVENT_TIME     EVENT_VALUE
                  "1"     13/03/2013 00:13:00     "100"
                  "2"     13/03/2013 00:16:00     "200"
                  "5"     13/03/2013 00:31:00     "500"
                  "7"     13/03/2013 00:48:00     "700"
                  regarding event id add a partition
                  , ranking as (
                  select
                     event_id
                    ,event_time
                    ,event_value
                    ,row_number() over (partition by
                                               event_id
                                              ,floor(to_number(to_char(event_time, 'SSSSS'))/900)
                                        order by
                                        mod( extract(MINUTE from (cast(event_time as timestamp))), 15)
                                        ) rnk
                   from data
                  )
                  This assumes that there is a value in every quarter, otherwise nothing is displayed.
                  There is a mistake in the data of your last example.
                  There is 51 in the data but 41 in the result.
                  If 41 should in fact be considered as the nearest for both quaters, we have to alter the solution.

                  Edited by: chris227 on 13.03.2013 02:46
                  • 6. Re: select records closest to 15 minute time intervals
                    jsha
                    Thanks everyone.

                    I will need to do some research on the suggested method and test against some real table data in order to understand the suggested solutions fully.
                    • 7. Re: select records closest to 15 minute time intervals
                      chris227
                      The solutions given yet wont give you the desired result, but they hint you to some tools.
                      Before going further to find a correct solution, some more information is needed.
                      For example it would be very helpful to know if there is at least one record for every quarter.
                      Otherwise it would be necesary to find the nearest record over a range of unknown quarters, that will lead to a somehow different solution.
                      Another question is, if there is always data of one day in the table or a separate calculation for every day is needed.

                      Regards
                      • 8. Re: select records closest to 15 minute time intervals
                        jsha
                        Ok, there will not always be a record for each Event_ID in each quarter and the table data will span over years. I will be restricting the search to a set period (e.g. last 2 hours worth of data). The idea is to return a record at a given sampling interval (15 minutes in this case) for each event_id for each hour. (Lets assume there are no duplicates and that the most recent record will be chosen in the event of equidistant records)

                        Ideally we would get 4 records per event_id for each hour however if only 1 record exists for a given hour for an event_id then we want that record returned once (matched against its nearest quarter). It should not be ignored it because it doesn't have entries in other quarters.

                        My first attempt is below as I'm still trying to understand the above solutions.

                        My approach (so far) is to calculate a target quarter for each record and then calculate the time difference from each record to its target quarter. I then return the record with the smallest time difference per event_id/quarter grouping. This should produce a minimum of 1 record for each Event_Id entry however will only return 1 record per event_id/quarter grouping (not ideal).

                        Obvious Limitations:
                        1. A record will only be considered for retrieval within a specific quarter. i.e. If there is only 2 records for the hour but both are in the same quarter only 1 is returned.
                        2. Im not 100% if the minimum difference is always the correct (when the difference is negative)
                        3. It seems to take a long time to process

                        Any comments or suggestions welcome :)
                        Select event_data.* from 
                        (   SELECT elog.event_id event_id,elog.event_value event_value, event_time, 
                                  EXTRACT (MINUTE FROM ( to_timestamp( to_char( trunc(event_time) + ( round(to_char(event_time,'sssss')/900) /96 ) ,'dd/mm/yyyy hh24:mi:ss'), 'dd/mm/yyyy hh24:mi:ss' ) - event_time))*60 +
                                     EXTRACT (SECOND FROM ( to_timestamp( to_char( trunc(event_time) + ( round(to_char(event_time,'sssss')/900) /96 ) ,'dd/mm/yyyy hh24:mi:ss'), 'dd/mm/yyyy hh24:mi:ss' ) - event_time)) delta,
                                  to_timestamp( to_char( trunc(event_time) + ( round(to_char(event_time,'sssss')/900) /96 ) ,'dd/mm/yyyy hh24:mi:ss'), 'dd/mm/yyyy hh24:mi:ss' ) target
                            FROM sc_event_log elog 
                            WHERE elog.event_time  > (CURRENT_TIMESTAMP - 0.08055555555)
                        ) event_data  
                        JOIN
                        (select t.event_id, t.target, min(t.delta) mindelta from
                           (  SELECT elog.event_id event_id,elog.event_value event_value, event_time, 
                                  EXTRACT (MINUTE FROM ( to_timestamp( to_char( trunc(event_time) + ( round(to_char(event_time,'sssss')/900) /96 ) ,'dd/mm/yyyy hh24:mi:ss'), 'dd/mm/yyyy hh24:mi:ss' ) - event_time))*60 +
                                     EXTRACT (SECOND FROM ( to_timestamp( to_char( trunc(event_time) + ( round(to_char(event_time,'sssss')/900) /96 ) ,'dd/mm/yyyy hh24:mi:ss'), 'dd/mm/yyyy hh24:mi:ss' ) - event_time)) delta,
                                  to_timestamp( to_char( trunc(event_time) + ( round(to_char(event_time,'sssss')/900) /96 ) ,'dd/mm/yyyy hh24:mi:ss'), 'dd/mm/yyyy hh24:mi:ss') target
                              FROM sc_event_log elog 
                              WHERE elog.event_time  > (CURRENT_TIMESTAMP - 0.08055555555
                           )
                        ) t
                        group by t.event_id, t.target) match
                        ON event_data.event_id=match.event_id
                        AND event_data.delta = match.mindelta
                        AND event_data.target = match.target
                        ORDER BY event_data.event_id,event_data.TARGET,event_data.EVENT_TIME
                        • 9. Re: select records closest to 15 minute time intervals
                          jsha
                          I tried to restructure my query above using the analytic function example and came up with this.......I'm not 100% but I think its working correctly!!
                          SELECT event_data.event_id, event_data.event_time, event_data.event_value
                          from (
                             SELECT t.event_id, t.target, t.delta, t.event_time,t.event_value,  rank()
                             OVER (partition by t.target,t.event_id order by  t.event_id, t.target,t.delta) rank
                             FROM
                             (  SELECT elog.event_id event_id,elog.event_value event_value, event_time, 
                                   abs( EXTRACT (MINUTE FROM ( to_timestamp( to_char( trunc(event_time) + ( round(to_char(event_time,'sssss')/900) /96 ) ,'dd/mm/yyyy hh24:mi:ss'), 'dd/mm/yyyy hh24:mi:ss' ) - event_time))*60 +
                                       EXTRACT (SECOND FROM ( to_timestamp( to_char( trunc(event_time) + ( round(to_char(event_time,'sssss')/900) /96 ) ,'dd/mm/yyyy hh24:mi:ss'), 'dd/mm/yyyy hh24:mi:ss' ) - event_time))) delta,
                                    to_timestamp( to_char( trunc(event_time) + ( round(to_char(event_time,'sssss')/900) /96 ) ,'dd/mm/yyyy hh24:mi:ss'), 'dd/mm/yyyy hh24:mi:ss' ) target
                                FROM event_log elog
                                WHERE elog.event_time  > (CURRENT_TIMESTAMP - 0.08055555555) 
                             ) t
                          ) event_data WHERE rank = 1
                          order by event_data.event_id, event_data.event_time, event_data.event_value
                          Edited by: jsha on Mar 15, 2013 12:28 AM (added abs function to delta column calculation to allow correct ranking for negative time differences)