Re: How to sum for last hour and last 24 hours without union all?
From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Thu, 03 Jun 2010 14:29:49 +0200
Message-ID: <86pli5F7d7U1_at_mid.individual.net>
On 03.06.2010 13:03, Robert Nicholson 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, 03 Jun 2010 14:29:49 +0200
Message-ID: <86pli5F7d7U1_at_mid.individual.net>
On 03.06.2010 13:03, Robert Nicholson 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
You can do something like (pseudo SQL):
assuming a column "ts" of type DATE, but the concept would work with TIMESTAMP types as well.
select account
, sum(case when ts >= sysdate - 1 then qty else 0 end) as qty_last_hr
, sum(case when ts >= sysdate - 1/24 then qty else 0 end) as qty_last_24
where ts >= sysdate - 1
group by account
Kind regards
robert
-- remember.guy do |as, often| as.you_can - without end http://blog.rubybestpractices.com/Received on Thu Jun 03 2010 - 07:29:49 CDT