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  |
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   |
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 #28892 is a reply to message #28891] |
Fri, 30 January 2004 07:04   |
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   |
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
|
|
|
|
Goto Forum:
Current Time: Wed Feb 19 06:40:51 CST 2025
|