Re: How to sum for last hour and last 24 hours without union all?
From: joel garry <joel-garry_at_home.com>
Date: Thu, 3 Jun 2010 11:10:53 -0700 (PDT)
Message-ID: <29541d18-3c5e-4fa9-99ed-9163e857b35f_at_a2g2000prd.googlegroups.com>
On Jun 3, 4:03 am, Robert Nicholson <robert.nichol..._at_gmail.com> wrote:
> So if you have a table that has a date time type and you want to sum
> the qty by account but want to do it in such a way that it fetches the
> data once first and then aggregates over different periods how do you
> use say rollup group by if you want to sum last hour and last 24
> hours? The idea is to simply get a sum by account for the last hour
> and last 24 hours.
>
> presumably there is an approach that is more efficient than doing two
> separate aggregations as components to a unionall and then merging
> those two. Where the first set qualifies the data by sysdate - 1/24
> (last hour) and the second set qualifies by sysdate - 1
Date: Thu, 3 Jun 2010 11:10:53 -0700 (PDT)
Message-ID: <29541d18-3c5e-4fa9-99ed-9163e857b35f_at_a2g2000prd.googlegroups.com>
On Jun 3, 4:03 am, Robert Nicholson <robert.nichol..._at_gmail.com> wrote:
> So if you have a table that has a date time type and you want to sum
> the qty by account but want to do it in such a way that it fetches the
> data once first and then aggregates over different periods how do you
> use say rollup group by if you want to sum last hour and last 24
> hours? The idea is to simply get a sum by account for the last hour
> and last 24 hours.
>
> presumably there is an approach that is more efficient than doing two
> separate aggregations as components to a unionall and then merging
> those two. Where the first set qualifies the data by sysdate - 1/24
> (last hour) and the second set qualifies by sysdate - 1
Also see http://www.orafusion.com/art_anlytc.htm under the section "Windowing aggregate functions:"
(I've seen this type of discursion in many places, that's just the first that caught my attention googling date aggregation Oracle)
Depending on what else you are doing in the sql and what everyone else is doing, sometimes the sum is better, sometimes the analytics. If it is something done heavily or often, it's worth it to try different ways under load.
jg
-- _at_home.com is bogus. http://www.signonsandiego.com/news/2010/jun/02/former-sequenom-exec-pleads-guilty/Received on Thu Jun 03 2010 - 13:10:53 CDT