Forum Stats

  • 3,757,567 Users
  • 2,251,246 Discussions
  • 7,869,867 Comments

Discussions

Select records at interval of 5 minutes

645384
645384 Member Posts: 8
edited Apr 24, 2010 5:15AM in SQL & PL/SQL
hi, I've asked this question before, but it's not solved yet. Thanks a lot for all your suggestion and help!

The data is like this:


TYPE | ID | TIME
CHIT|1000311390|11-JUN-08 05.39.59.000000000 AM
CHIT|1000311370|11-JUN-08 05.40.10.000000000 AM
CHIT|1000311370|11-JUN-08 05.46.27.000000000 AM
CHIT|9050|11-JUN-08 05.49.41.000000000 AM
CHIT|9050|11-JUN-08 05.50.41.000000000 AM
CMIK|1000094670|11-JUN-08 05.54.30.000000000 AM
CMIK|5480|11-JUN-08 05.54.30.000000000 AM
CHIT|1000005980|11-JUN-08 05.54.36.000000000 AM
CMIK|1000289540|11-JUN-08 05.58.10.000000000 AM
CMIK|5480|11-JUN-08 06.01.03.000000000 AM
CMIK|1000094670|11-JUN-08 06.01.03.000000000 AM
CMIK|1000095130|11-JUN-08 06.04.42.000000000 AM
CMIK|5480|11-JUN-08 06.04.42.000000000 AM
CMIK|1000056490|11-JUN-08 06.11.18.000000000 AM
EHUY|486604824|11-JUN-08 06.21.55.000000000 AM
CMIK|9040|11-JUN-08 06.25.44.000000000 AM
CHIT|1000311390|11-JUN-08 06.25.50.000000000 AM
CHIT|5480|11-JUN-08 06.25.50.000000000 AM
CHIT|1000016600|11-JUN-08 06.37.53.000000000 AM
EHUY|492437505|11-JUN-08 06.50.20.000000000 AM
CMIK|5480|11-JUN-08 06.51.42.000000000 AM
CMIK|1000251580|11-JUN-08 06.51.42.000000000 AM
CMIK|5480|11-JUN-08 06.58.12.000000000 AM
CMIK|1000251580|11-JUN-08 06.58.12.000000000 AM
EHUY|134414363|11-JUN-08 07.09.38.000000000 AM
EHUY|100663396|11-JUN-08 07.09.50.000000000 AM
EHUY|100663418|11-JUN-08 07.09.50.000000000 AM
CMIK|5480|11-JUN-08 07.27.27.000000000 AM
CMIK|1000094670|11-JUN-08 07.27.27.000000000 AM
CMIK|1000289540|11-JUN-08 07.30.28.000000000 AM

The requirement:
Starting from the 1st record, skip for 5 minutes (or more, if we could not find one record at exact 5 mintutes point), then choose the next one; then skip for another 5 minutes (or more) and so on. Putting it in another way, we'll ignore records within 5 minutes time range.

So, the result from the above data should be:

TYPE | ID | TIME
CHIT|1000311390|11-JUN-08 05.39.59.000000000 AM
CMIK|1000289540|11-JUN-08 05.58.10.000000000 AM
EHUY|486604824|11-JUN-08 06.21.55.000000000 AM
CHIT|1000016600|11-JUN-08 06.37.53.000000000 AM
CMIK|5480|11-JUN-08 06.58.12.000000000 AM
CMIK|5480|11-JUN-08 07.27.27.000000000 AM
«13

Comments

  • NicloeiW
    NicloeiW Member Posts: 1,811
    like this
    --http://forums.oracle.com/forums/message.jspa?messageID=2593239#2593239
    with t As
    (

    Select 'CHIT' c1 , 1000311390 c2 , to_date('11-JUN-08 05.39.59 AM','dd-mon-rr hh:mi:ss AM') c3 From dual Union All
    Select 'CHIT' , 1000311370 , to_date('11-JUN-08 05.40.10 AM','dd-mon-rr hh:mi:ss AM') c3 From dual Union All
    Select 'CHIT' , 1000311370 , to_date('11-JUN-08 05.46.27 AM','dd-mon-rr hh:mi:ss AM') c3 From dual Union All
    Select 'CHIT' , 9050 , to_date('11-JUN-08 05.49.41 AM','dd-mon-rr hh:mi:ss AM') c3 From dual Union All
    Select 'CHIT' , 9050 , to_date('11-JUN-08 05.50.41 AM','dd-mon-rr hh:mi:ss AM') c3 From dual Union All
    Select 'CMIK' , 1000094670 , to_date('11-JUN-08 05.54.30 AM','dd-mon-rr hh:mi:ss AM') c3 From dual Union All
    Select 'CMIK' , 5480 , to_date('11-JUN-08 05.54.30 AM','dd-mon-rr hh:mi:ss AM') c3 From dual Union All
    Select 'CHIT' , 1000005980 , to_date('11-JUN-08 05.54.36 AM','dd-mon-rr hh:mi:ss AM') c3 From dual Union All
    Select 'CMIK' , 1000289540 , to_date('11-JUN-08 05.58.10 AM','dd-mon-rr hh:mi:ss AM') c3 From dual Union All
    Select 'CMIK' , 5480 , to_date('11-JUN-08 06.01.03 AM','dd-mon-rr hh:mi:ss AM') c3 From dual Union All
    Select 'CMIK' , 1000094670 , to_date('11-JUN-08 06.01.03 AM','dd-mon-rr hh:mi:ss AM') c3 From dual Union All
    Select 'CMIK' , 1000095130 , to_date('11-JUN-08 06.04.42 AM','dd-mon-rr hh:mi:ss AM') c3 From dual Union All
    Select 'CMIK' , 5480 , to_date('11-JUN-08 06.04.42 AM','dd-mon-rr hh:mi:ss AM') c3 From dual Union All
    Select 'CMIK' , 1000056490 , to_date('11-JUN-08 06.11.18 AM','dd-mon-rr hh:mi:ss AM') c3 From dual Union All
    Select 'EHUY' , 486604824 , to_date('11-JUN-08 06.21.55 AM','dd-mon-rr hh:mi:ss AM') c3 From dual Union All
    Select 'CMIK' , 9040 , to_date('11-JUN-08 06.25.44 AM','dd-mon-rr hh:mi:ss AM') c3 From dual Union All
    Select 'CHIT' , 1000311390 , to_date('11-JUN-08 06.25.50 AM','dd-mon-rr hh:mi:ss AM') c3 From dual Union All
    Select 'CHIT' , 5480 , to_date('11-JUN-08 06.25.50 AM','dd-mon-rr hh:mi:ss AM') c3 From dual Union All
    Select 'CHIT' , 1000016600 , to_date('11-JUN-08 06.37.53 AM','dd-mon-rr hh:mi:ss AM') c3 From dual Union All
    Select 'EHUY' , 492437505 , to_date('11-JUN-08 06.50.20 AM','dd-mon-rr hh:mi:ss AM') c3 From dual Union All
    Select 'CMIK' , 5480 , to_date('11-JUN-08 06.51.42 AM','dd-mon-rr hh:mi:ss AM') c3 From dual Union All
    Select 'CMIK' , 1000251580 , to_date('11-JUN-08 06.51.42 AM','dd-mon-rr hh:mi:ss AM') c3 From dual Union All
    Select 'CMIK' , 5480 , to_date('11-JUN-08 06.58.12 AM','dd-mon-rr hh:mi:ss AM') c3 From dual Union All
    Select 'CMIK' , 1000251580 , to_date('11-JUN-08 06.58.12 AM','dd-mon-rr hh:mi:ss AM') c3 From dual Union All
    Select 'EHUY' , 134414363 , to_date('11-JUN-08 07.09.38 AM','dd-mon-rr hh:mi:ss AM') c3 From dual Union All
    Select 'EHUY' , 100663396 , to_date('11-JUN-08 07.09.50 AM','dd-mon-rr hh:mi:ss AM') c3 From dual Union All
    Select 'EHUY' , 100663418 , to_date('11-JUN-08 07.09.50 AM','dd-mon-rr hh:mi:ss AM') c3 From dual Union All
    Select 'CMIK' , 5480 , to_date('11-JUN-08 07.27.27 AM','dd-mon-rr hh:mi:ss AM') c3 From dual Union All
    Select 'CMIK' , 1000094670 , to_date('11-JUN-08 07.27.27 AM','dd-mon-rr hh:mi:ss AM') c3 From dual Union All
    Select 'CMIK' , 1000289540 , to_date('11-JUN-08 07.30.28 AM','dd-mon-rr hh:mi:ss AM') c3 From dual
    )
    Select c1, c2, c3 From
    (
    Select c1, c2, c3,
    first_value(c3) over(Order By c3 Range Between Interval '5' Minute PRECEDING AND CURRENT ROW) new_c3
    From t
    )
    Where c3 = new_c3

    C1 C2 C3
    1 CHIT 1000311390 6/11/2008 5:39:59 AM
    2 CHIT 1000311370 6/11/2008 5:46:27 AM
    3 CMIK 1000056490 6/11/2008 6:11:18 AM
    4 EHUY 486604824 6/11/2008 6:21:55 AM
    5 CHIT 1000016600 6/11/2008 6:37:53 AM
    6 EHUY 492437505 6/11/2008 6:50:20 AM
    7 CMIK 5480 6/11/2008 6:58:12 AM
    8 CMIK 1000251580 6/11/2008 6:58:12 AM
    9 EHUY 134414363 6/11/2008 7:09:38 AM
    10 CMIK 5480 6/11/2008 7:27:27 AM
    11 CMIK 1000094670 6/11/2008 7:27:27 AM
  • 645384
    645384 Member Posts: 8
    hi, Nicolei,
    thanks a lot for your quick response. But there are a few problems.

    I gave the result in the last post. But it was based on 15 minutes.
    If calculated for 5 minutes interval, the result should be this:

    CHIT|1000311390|11-JUN-08 05.39.59.000000000 AM
    CHIT|1000311370|11-JUN-08 05.46.27.000000000 AM
    CMIK|1000094670|11-JUN-08 05.54.30.000000000 AM
    CMIK|5480|11-JUN-08 06.01.03.000000000 AM
    CMIK|1000056490|11-JUN-08 06.11.18.000000000 AM
    EHUY|486604824|11-JUN-08 06.21.55.000000000 AM
    CHIT|1000016600|11-JUN-08 06.37.53.000000000 AM
    EHUY|492437505|11-JUN-08 06.50.20.000000000 AM
    CMIK|5480|11-JUN-08 06.58.12.000000000 AM
    EHUY|134414363|11-JUN-08 07.09.38.000000000 AM
    CMIK|5480|11-JUN-08 07.27.27.000000000 AM



    Your result is incorrect,
    First,
    the 7th (6/11/2008 6:58:12 ) and 8th (6/11/2008 6:58:12 ) are in a tie;
    and so are the same for 10th and 11th. That is, the interval is 0 instead
    of "5 minutes".
    I think this is a bug.
    So how to eliminate tie records,(only keep one of the tie records)?

    I think you're almost there. I'm going to study your solution carefully. Thank you so much!!!
  • NicloeiW
    NicloeiW Member Posts: 1,811
    i am not getting you, kindly post it properly for more people to answer you,

    else you wont get nice answers
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    edited Jun 18, 2008 8:03AM
    I have thought arranged question.
    Umm I recommend to use TableFunction or model.

    10 <-- will Output
    14
    15 <-- will Output
    30 <-- will Output
    36 <-- will Output
    40
    45 <-- will Output
    50 <-- will Output
    51
    52
    53
    54
    55 <-- will Output
    61 <-- will Output
    65
    66 <-- will Output
    67
  • 645384
    645384 Member Posts: 8
    we start from the 1st record, that's

    CHIT|1000311390|11-JUN-08 05.39.59.000000000 AM

    then we skip for 5 minutes, the time should be 05:44:59 am (or later), but there is no record at this time point, so we simply move to the next record that is later than 05:44:59, that's

    CHIT|1000311370|11-JUN-08 05.46.27.000000000 AM

    Now, we skip for 5 minutes again, the time should be 05:51:27 am (or later), however, there is no record at this time point, so we move on to the next record, that's

    CMIK|1000094670|11-JUN-08 05.54.30.000000000 AM

    skip for another 5 minutes, the time should be 05:59:30 am, since there is no record at this time point, we move on to the next one which is later than 05:59:30 am. That is:

    CMIK|5480|11-JUN-08 06.01.03.000000000 AM

    skip for 5 minutes again, next record should be at 06:06:03 am, but no records at this time, so move on, and choose:

    CMIK|1000056490|11-JUN-08 06.11.18.000000000 AM

    skip for 5 minutes, next record should be at 06:16:18 am or later, so we choose;

    EHUY|486604824|11-JUN-08 06.21.55.000000000 AM

    for another 5 minutes, next record should be at 06:26:55 am( or later), so we move to this one:

    CHIT|1000016600|11-JUN-08 06.37.53.000000000 AM

    5 minutes later, time should be 06:42:53 am, but no records at this time, so simply move on to the next one:

    EHUY|492437505|11-JUN-08 06.50.20.000000000 AM

    now, after 5 minutes, we'll choose a record at 06:55:20 am, no records at this time point, so we move to the closest record to this time, that's:

    CMIK|5480|11-JUN-08 06.58.12.000000000 AM

    next one should at 07:03:12 am (or later), so we'll actually choose:

    EHUY|134414363|11-JUN-08 07.09.38.000000000 AM
    after 5 minutes, it's 07:14:38 am, so we'll choose

    CMIK|5480|11-JUN-08 07.27.27.000000000 AM


    So the result should be this:

    CHIT|1000311390|11-JUN-08 05.39.59.000000000 AM
    CHIT|1000311370|11-JUN-08 05.46.27.000000000 AM
    CMIK|1000094670|11-JUN-08 05.54.30.000000000 AM
    CMIK|5480|11-JUN-08 06.01.03.000000000 AM
    CMIK|1000056490|11-JUN-08 06.11.18.000000000 AM
    EHUY|486604824|11-JUN-08 06.21.55.000000000 AM
    CHIT|1000016600|11-JUN-08 06.37.53.000000000 AM
    EHUY|492437505|11-JUN-08 06.50.20.000000000 AM
    CMIK|5480|11-JUN-08 06.58.12.000000000 AM
    EHUY|134414363|11-JUN-08 07.09.38.000000000 AM
    CMIK|5480|11-JUN-08 07.27.27.000000000 AM


    You can see from the result, each record is at least 5 minutes apart from it's previous record.

    Hope this clarification makes sense.

    Thanks a lot for all your help!
  • 645384
    645384 Member Posts: 8
    I could not understand your "thought", what are the numbers? Looks like line numbers but I don't have over 30 lines of data.

    Let me breiefly explain the requirement here:

    choose the the 1st record, then skip for 5 minutes, if no record at exact 5 minutes point, then simply move to the next record which is closest to 5 minutes;

    Then wait for another 5 minutes, and so on.

    So in the result, each record should be at least 5 minutes later than its previous record.
    here it is:

    CHIT|1000311390|11-JUN-08 05.39.59.000000000 AM
    CHIT|1000311370|11-JUN-08 05.46.27.000000000 AM
    CMIK|1000094670|11-JUN-08 05.54.30.000000000 AM
    CMIK|5480|11-JUN-08 06.01.03.000000000 AM
    CMIK|1000056490|11-JUN-08 06.11.18.000000000 AM
    EHUY|486604824|11-JUN-08 06.21.55.000000000 AM
    CHIT|1000016600|11-JUN-08 06.37.53.000000000 AM
    EHUY|492437505|11-JUN-08 06.50.20.000000000 AM
    CMIK|5480|11-JUN-08 06.58.12.000000000 AM
    EHUY|134414363|11-JUN-08 07.09.38.000000000 AM
    CMIK|5480|11-JUN-08 07.27.27.000000000 AM
  • 561825
    561825 Member Posts: 646
    Check this link.

    658306

    Your requirement is more or less similar to this one.

    Regards

    Raj
  • 603036
    603036 Member Posts: 382
    edited Jun 18, 2008 2:19PM
    if Nic's result set was essentially correct, but only has duplicates (tie records) just use the row_number() analytic function and wrap it with another select to grab only the first row:
    SELECT c1, c2, c3
    FROM (SELECT c1, c2, c3,
    ROW_NUMBER () OVER (PARTITION BY c1, c3 ORDER BY c2 DESC) rn
    FROM (SELECT c1, c2, c3,
    FIRST_VALUE (c3) OVER (ORDER BY c3 RANGE BETWEEN INTERVAL '5' MINUTE PRECEDING AND CURRENT ROW)
    new_c3
    FROM t)
    WHERE c3 = new_c3)
    WHERE rn = 1;
    -- uses Nic's 't' table.

    Message was edited by:
    RACER

    left out some of Nic's code...
  • 645384
    645384 Member Posts: 8
    hi,

    Nicloei's result is not correct. Two problems,
    1, there are duplicate records.
    2, he skipped some records (which should be included in the result); and then he included some records which supposed not to be there.
    His first two records are correct;

    comparing with the result I want:

    CHIT|1000311390|11-JUN-08 05.39.59.000000000 AM
    CHIT|1000311370|11-JUN-08 05.46.27.000000000 AM
    CMIK|1000094670|11-JUN-08 05.54.30.000000000 AM
    CMIK|5480|11-JUN-08 06.01.03.000000000 AM
    CMIK|1000056490|11-JUN-08 06.11.18.000000000 AM
    EHUY|486604824|11-JUN-08 06.21.55.000000000 AM
    CHIT|1000016600|11-JUN-08 06.37.53.000000000 AM
    EHUY|492437505|11-JUN-08 06.50.20.000000000 AM
    CMIK|5480|11-JUN-08 06.58.12.000000000 AM
    EHUY|134414363|11-JUN-08 07.09.38.000000000 AM
    CMIK|5480|11-JUN-08 07.27.27.000000000 AM
  • NicloeiW
    NicloeiW Member Posts: 1,811
    then probably wait for other experts to look at your post
This discussion has been closed.