Grouping data by time interval

Question

I want to group the data in a table by time interval. The table keeps the order info that includes the userid, item name, model, quantity, activity date, etc. Now I want to group this data by time intervals. The time interval could be anything like 5 min, 10 min, 15, etc. Also, the query should return only those users (all the column data) who made orders more than once within that 5 minutes interval. Is it possible to achieve this in one SQL query? I use Oracle.

Thank you

Edit:

Sample data

**userid    item name    model      quantity   order date**
abc calculator   cdm83ss    1      02-FEB-2013 09:20:13     
abc alarm clock  actp001    1      02-FEB-2013 09:26:22
yyy iPhone       iP4    1      02-FEB-2013 09:28:14
abc alarm clock  actz321    2      02-FEB-2013 09:30:00
zzz backpack     bp344tk    1      04-FEB-2013 13:15:00
zzz backpack     bp234zz    2      04-FEB-2013 13:19:32
zzz camera       cm234  1      04-FEB-2013 13:20:22 
ttt tv       fs45yup    1      04-FEB-2013 13:28:19

I expect to get:

**userid    item name    model      quantity   order date**
abc         calculator   cdm83ss    1      02-FEB-2013 09:20:13     
abc         alarm clock  actp001    1      02-FEB-2013 09:26:22
abc         alarm clock  actz321    2      02-FEB-2013 09:30:00
zzz         backpack     bp344tk    1      04-FEB-2013 13:15:00
zzz         backpack     bp234zz    2      04-FEB-2013 13:19:32
zzz         camera       cm234  1      04-FEB-2013 13:20:22 

Solution 1

Yes. Presumably you want to see the result as a datetime value as well. This requires some datetime arithmetic. Basically, getting the number of minutes since midnight, dividing by the number of minutes and then multiplying again (to round down). Then adding back in the time at midnight:

select t.*
from (select t.*,
             count(*) over (partition by userid, interval) as CntInInterval
      from (select trunc(orderdate)+
                   (floor(((orderdate - trunc(orderdate))*24*60)/10)*10)/(24*60) as interval, t.*
            from t
           ) t
     ) t
where cntInInterval > 1

To group by time intervals, you would use:

      select interval, count(*)
      from (select trunc(orderdate)+floor(((orderdate - trunc(orderdate))*24*60)/10)*10 as interval, t.*
            from t
           ) t
      group by interval

In these queries, "10" stands for any number of minutes. Note that these are calculated since midnight, so a value like 17 always starts with the first 17 minutes of the day.

The definition of interval is an arithmetic expression on dates.

          trunc(orderdate)+floor(((orderdate - trunc(orderdate))*24*60)/10)*10 as interval,

The first part, trunc(orderdate), is Oracle syntax for removing the time part of a date. This moves the date to midnight at the beginning of the day.

The expression orderdate - trunc(orderdate) calculates the number of days since midnight -- this is a fractional part of one day. So, 0.25 would be 6:00 a.m. The *24*60 converts this to minutes. So, 0.25 becomes 0.25*60*24 = 360 -- the number of minutes since midnight.

Then the expression floor(x/y)*y simply "truncates" any value to the lower multiple of y. So, floor(118/10) is 11, and 11*10 is 110. In other words, this will map all values between a*y and (a+1)*y (up to not including) to the same value, a*y.

Consider the expression in practice, on 6:08 a.m. on 2013-01-01:

`trunc(orderdate)` moves the date to midnight on 2013-01-01.
`orderdate - trunc(orderdate)` creates a number like 0.25.
`((orderdate - trunc(orderdate))*24*60)` produces the value 368
`floor(((orderdate - trunc(orderdate))*24*60)/10)*10` produces 360
`floor(((orderdate - trunc(orderdate))*24*60)/10)*10*(1/24*60)` produces 0.25

And when this is added to minight, the time turns into 6:00 a.m. again.

Solution 2

In case you need simpler way to get time interval - I cannot help you with anything else as i do not know your tables and data:

-- Time interval - every 15 min from midnight --
SELECT To_Char(trunc(SYSDATE) + (LEVEL/1440*15), 'HH24:MI') interval_15_min 
  FROM dual
CONNECT BY LEVEL <= 10 -- this is orbitraty 
/
SQL>

INTERVAL_15_MIN
--------------
00:15
00:30
00:45
...

Comments

[Mike]

Thanks so much. This is what I wanted. However I noticed that this excludes some data in scenarios like this, the ordered times for a user: 09:32:12, 09:37:34, 09:40:21. In this case, it only shows 09:32:12 and 09:37:34, not 09:40:21. I do understand why it's not counting this date/time, because it falls in the next 10 minute interval (even though all the transactions of this person took 10 minutes). It would be nice if I am able to count this date/time too(since all the transactions of this person took place within 10 minutes).

[Mike]

Gordon, Could you please help me to understand this query "select trunc(sysdate)+floor(((sysdate - trunc(sysdate))*24*60)/5)*5 "? I understand the "floor" part. But when adding this to trunc(sysdate), it returns 18-AUG-15, don't understand this. Thank you.

[Gordon Linoff]

@Mike . . . I will, but first I fixed the syntax. I had sysdate where orderdate should be. I was using sysdate to get the logic right.

[Mike]

Gordon, That's an excellent explanation. I did replace the sysdate in your query with the order date. I get 30-AUG-2015 as interval for the order date of 02-JAN-2013. This is what I don't understand. Also, will this query work for days interval if I remove *24*60 and replace 10(minutes) with days?

[Gordon Linoff]

@Mike . . . I actually did test the code for interval. Are you sure you got all the references to sysdate? The same idea works for any size interval less than a day. For intervals longer than a day, the logic is simpler . . . just convert the value to a number and do the arithmetic on the number, and the convert back to a date.

[Mike]

Gordon, Yes, I replaced the sysdate with the order date. I get 11-AUG-2014 when I run this query, select trunc(to_date('08-JAN-2013 09:44:12', 'DD-MON-YYYY HH24:MI:SS'))+floor(((to_date('08-JAN-2013 09:44:12', 'DD-MON-YYYY HH24:MI:SS') - trunc(to_date('08-JAN-2013 09:44:12', 'DD-MON-YYYY HH24:MI:SS')))*24*60)/10)*10 as interval from dual. Am I missing something?

[Mike]

Gordon, what do you mean "convert the value to a number"? Thank you

[Gordon Linoff]

You can convert a date to a number by measuring the number of days since some point in the past. Here is an example: select sysdate - to_date('1900-01-01', 'yyyy-mm-dd') from dual.

[Mike]

Thanks again,Gordon. The days interval makes sense now. However, like I mentioned in my previous comment, adding the minutes from the arithmetic expression to the truncated order date(mid night) gives me incorrect date/time (interval). It looks like, instead of treating the value from the arithmetic expression as minutes, the query treats that as days and adds that to the truncated order date. That's why it's returning 11-AUG-2014. I still don't understand why I am getting this. I tried this in both TOAD and SQLPLUS.

[Gordon Linoff]

@Mike . . . I think I missed copying over the part of the formula that divides the minutes by (24*60) to get what you need. I fixed it above. Here is the SQLFiddle . . . sqlfiddle.com/#!4/d41d8/7163/0.