RE: possible to do a group by on a time interval? (or some other way to do this)

From: Herring Dave - dherri <Dave.Herring_at_acxiom.com>
Date: Sun, 14 Oct 2007 07:52:12 -0500
Message-ID: <7ED53A68952D3B4C9540B4EFA5C76E3603CB9982@CWYMSX04.Corp.Acxiom.net>

To GROUP BY some time interval, where that interval could vary and is based on minutes, try something like the following:

SET FEEDBACK off
VARIABLE GROUPBY_MINS NUMBER;
EXEC :GROUPBY_MINS := ??; VARIABLE MINS_OF_DAY NUMBER;
EXEC :MINS_OF_DAY := :GROUPBY_MINS / 1440; SET FEEDBACK on

/*
 * If the minutes grouping-factor is intra-day (< 1440 minutes), then
 * display the date as the middle value of the range.  Otherwise,
 * display it as the beginning date of the range.  So a date of

'01-JAN-2007

00:00' and 120 groupby mins would cover 00:00 to 02:00
and be displayed as '01:00'. If 1440 groupby mins, then it'd be
displayed as '01-JAN-2007 00:00', meaning values for that whole day. */ SELECT CASE WHEN :GROUPBY_MINS >= 1440 THEN mytimestamp - ((MOD((mytimestamp - TRUNC(mytimestamp)) * 1440, :GROUPBY_MINS)) / 1440) ELSE (mytimestamp + :MINS_OF_DAY) - ((MOD((mytimestamp - TRUNC(mytimestamp)) * 1440, :GROUPBY_MINS)) / 1440) - (:MINS_OF_DAY / 2) END mytimestamp FROM metrictable ...

Dave

David C. Herring, DBA | A c x i o m Delivery Center Organization

630-944-4762 office | 630-430-5988 wireless | 630-944-4989 fax

> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org

[mailto:oracle-l-bounce_at_freelists.org]
> On Behalf Of William Robertson
> Sent: Saturday, October 13, 2007 12:52 PM
> To: oracle-l_at_freelists.org
> Subject: Re: possible to do a group by on a time interval? (or some
other
> way to do this)
>
> Maybe something like:
>
> SELECT FLOOR(EXTRACT(minute FROM mytimestamp) / 15)*15 AS period
> , COUNT(*)
> FROM metrictable
> GROUP BY FLOOR(EXTRACT(minute FROM mytimestamp) / 15)*15
>
>
> -----Original message-----
> From: ryan_gaffuri_at_comcast.net
> Date: 10/10/07 23:11
> > seselect max(mytimestamp) as time, sum(mymetric) as bytes
> > from metrictable
> > where mymetric > ( select max(mymetric) from metrictable) - (1/96)
> >
> >
> > "mytimestamp" is a timestamp data type.
> >
> > I want to get a sum for every 15 minute interval in the table. I
will
> add a where clause to limit how far back I want to go. The where
clause I
> have there now is just an example.
> >
> > tried group by, group by rollup, analytic with a window.
> >
> > do i need the model clause? I have never used that. I really do
think
> there is a way to do this in straight sql. Been working on it for a
> while....
> > --
> > http://www.freelists.org/webpage/oracle-l
> >
> >
> >
> >
>
> --
> http://www.freelists.org/webpage/oracle-l

The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged.

If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited.

If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system.

Thank you.

--
http://www.freelists.org/webpage/oracle-l

Received on Sun Oct 14 2007 - 07:52:12 CDT