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:33:46 +0200
Message-ID: <86plpiF7d7U2_at_mid.individual.net>
On 03.06.2010 14:29, Robert Klemme wrote:
> 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
Date: Thu, 03 Jun 2010 14:33:46 +0200
Message-ID: <86plpiF7d7U2_at_mid.individual.net>
On 03.06.2010 14:29, Robert Klemme wrote:
> 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
Oops, naming of columns was wrong. And with the WHERE clause one CASE is superfluous:
select account
, sum(qty) as qty_last_24
, sum(case when ts >= sysdate - 1/24 then qty else 0 end) as qty_last_hr
where ts >= sysdate - 1
group by account
Cheers
robert
-- remember.guy do |as, often| as.you_can - without end http://blog.rubybestpractices.com/Received on Thu Jun 03 2010 - 07:33:46 CDT