Home » SQL & PL/SQL » SQL & PL/SQL » Grouping records based on time (every 5 minutes)
Grouping records based on time (every 5 minutes) [message #28865] Wed, 28 January 2004 19:24 Go to next message
Developer
Messages: 19
Registered: January 2004
Junior Member
I have a table temp which has a date column(dt) and char column (name).

I want to group the records based on time,every 5 minutes and display the count of records for every 5 minutes interval.

select * from temp;

DT                                        NAME

------------                             --------

12/28/2003 09:24:00             A

12/28/2003 09:28:00             A

12/28/2003 09:29:00             B

12/29/2003 11:04:00             C

12/29/2003 11:49:00             A

12/29/2003 11:50:00             A

How do I write a query that gives this ouput.

Interval                                        count of records

---------------------------------                 ------------------------

12/28 09:21 to 09:25                          1

12/28 09:26 to 09:30                          2

12/29 11:01 to 11:05                          1

12/29 11:46 to 11:50                          2

Thanks for your help.

 

 
Re: Grouping records based on time (every 5 minutes) [message #28879 is a reply to message #28865] Thu, 29 January 2004 05:46 Go to previous messageGo to next message
Art Metzer
Messages: 2480
Registered: December 2002
Senior Member
I think this is close:
SQL> CREATE TABLE temp (dt DATE, name VARCHAR2(1));
  
Table created.
  
SQL> INSERT INTO temp VALUES (TO_DATE('200312280924','YYYYMMDDHH24MI'),'A');
SQL> INSERT INTO temp VALUES (TO_DATE('200312280928','YYYYMMDDHH24MI'),'A');
SQL> INSERT INTO temp VALUES (TO_DATE('200312280929','YYYYMMDDHH24MI'),'B');
SQL> INSERT INTO temp VALUES (TO_DATE('200312281104','YYYYMMDDHH24MI'),'C');
SQL> INSERT INTO temp VALUES (TO_DATE('200312281149','YYYYMMDDHH24MI'),'A');
SQL> INSERT INTO temp VALUES (TO_DATE('200312281150','YYYYMMDDHH24MI'),'A');
SQL> COMMIT;
 
Commit complete.
 
SQL> SELECT TO_CHAR(t.dt,'fmMM/DD/YYYY HH:fmMI:SS AM') dt
  2  ,      RPAD(t.name,4)                             name
  3  FROM   temp        t
  4  /
 
DT                     NAME
---------------------- ----
12/28/2003 9:24:00 AM  A
12/28/2003 9:28:00 AM  A
12/28/2003 9:29:00 AM  B
12/28/2003 11:04:00 AM C
12/28/2003 11:49:00 AM A
12/28/2003 11:50:00 AM A
 
6 rows selected.
 
SQL> SELECT   TO_CHAR(grpd.dt,'fmMM/DD')
  2           || ' '
  3           || TO_CHAR(TRUNC(grpd.lower_limit / 12),'fm00')
  4           || ':'
  5           || TO_CHAR(MOD(grpd.lower_limit,12) * 5,'fm00')
  6           || ':01 to '
  7           || TO_CHAR(TRUNC(grpd.upper_limit / 12),'fm00')
  8           || ':'
  9           || TO_CHAR(MOD(grpd.upper_limit,12) * 5,'fm00') interval
 10  ,        grpd.qty                                        count_of_records
 11  FROM (SELECT   TRUNC(t.dt)                                          dt
 12        ,        TRUNC((TO_NUMBER(TO_CHAR(t.dt
 13                                  ,       'SSSSS')) - 1)/300)         lower_limit
 14        ,        MOD(TRUNC((TO_NUMBER(TO_CHAR(t.dt
 15                                      ,       'SSSSS')) - 1)/300) + 1
 16                 ,   288)                                             upper_limit
 17        ,        COUNT(*)                                             qty
 18        FROM     temp     t
 19        GROUP BY TRUNC(t.dt)
 20        ,        TRUNC((TO_NUMBER(TO_CHAR(t.dt,'SSSSS')) - 1)/300)) grpd
 21  /
 
INTERVAL                    COUNT_OF_RECORDS
--------------------------- ----------------
12/28 09:20:01 to 09:25                    1
12/28 09:25:01 to 09:30                    2
12/28 11:00:01 to 11:05                    1
12/28 11:45:01 to 11:50                    2
 
SQL>
HTH,

Art.
Re: Grouping records based on time (every 5 minutes) [message #28891 is a reply to message #28879] Fri, 30 January 2004 04:23 Go to previous messageGo to next message
Developer
Messages: 19
Registered: January 2004
Junior Member
Thanks a lot for your help!
Would you please throw some light on the calculation part where upper and lower limits are calculated?
I am trying to understand this query so as to modify the same for every 20 minutes or so..
I appreciate your help.
Re: Grouping records based on time (every 5 minutes) [message #28892 is a reply to message #28891] Fri, 30 January 2004 07:04 Go to previous messageGo to next message
Art Metzer
Messages: 2480
Registered: December 2002
Senior Member
Here is the gist of the query:
SELECT   TRUNC(t.dt)                                          dt 
,        TRUNC((TO_NUMBER(TO_CHAR(t.dt 
                          ,       'SSSSS')) - 1)/300)         lower_limit 
,        MOD(TRUNC((TO_NUMBER(TO_CHAR(t.dt 
                              ,       'SSSSS')) - 1)/300) + 1 
         ,   288)                                             upper_limit 
,        COUNT(*)                                             qty 
FROM     temp     t 
GROUP BY TRUNC(t.dt) 
,        TRUNC((TO_NUMBER(TO_CHAR(t.dt,'SSSSS')) - 1)/300)
I wanted to "collapse" all times that could take place within each five-minute window into some single representative value.

I started with TO_NUMBER(TO_CHAR(t.dt,'SSSSS')), which represents the number of seconds since midnight. So, for example, if the time portion of t.dt is 2:06:48 p.m., then this seconds-since-midnight value is 50808:
SQL> SELECT TO_NUMBER(TO_CHAR(TO_DATE('2:06:48 PM','HH:MI:SS AM'),'SSSSS')) as_num FROM DUAL;
 
    AS_NUM
----------
     50808
 
SQL>
Now, when I divide this number-of-seconds-since-midnight by 300 seconds (= five minutes) and discard any fraction (TRUNC), I cluster together all times within five-minute windows.
So if I have three rows whose t.dt is, say, 2:06:48 PM, 2:07:09 PM and 2:09:55 PM, then these times' seconds since midnight are 50808, 50829 and 50995. But when I divide by 300 and ignore those remainders, the answer is the same: 169. Then I can group by this quotient, and COUNT(*) up the number of rows in that five-minute window.

The SELECT surrounding this in-line view is concerned with converting that quotient (e.g., 169) back to a string telling which five-minute window it represents.

If you want to run this query for twenty-minute intervals, change every 300 to 1200, and change the 288 in the MOD statement to 72.

Hope this helps,

Art
Re: Grouping records based on time (every 5 minutes) [message #192826 is a reply to message #28865] Wed, 13 September 2006 16:35 Go to previous messageGo to next message
jazzist
Messages: 11
Registered: March 2006
Junior Member
could anyone suggest how this could be modified to only return results within a specified time window.

for example, you want to display only intervals with start times between 0900-1200, but still want results from multiple days - so the returned list has a cyclic 3 hourly time window, with an incrementing day of the month.

thanks.

[Updated on: Wed, 13 September 2006 16:37]

Report message to a moderator

Re: Grouping records based on time (every 5 minutes) [message #192829 is a reply to message #192826] Wed, 13 September 2006 16:42 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
WHERE TO_NUMBER(TO_CHAR(t.dt, 'hh24')) between 9 and 12 Wink


It it turns out to be inefficient, you can consider creating a function based index on TO_NUMBER(TO_CHAR(t.dt, 'hh24')) if necessary.
Previous Topic: ORA-00911 error....
Next Topic: Automating adding partitions by using data dictionary
Goto Forum:
  


Current Time: Wed Feb 19 06:40:51 CST 2025