Prev: CLOB > 32k
Next: ORA-06502: PL/SQL: numeric or value error: character string buffer too small
From: Robert Nicholson on 3 Jun 2010 07:03
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
From: Robert Klemme on 3 Jun 2010 08:29
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/
From: Robert Klemme on 3 Jun 2010 08:33
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/
From: joel garry on 3 Jun 2010 14:10
On Jun 3, 4:03 am, Robert Nicholson <robert.nichol...(a)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
--
@home.com is bogus.
http://www.signonsandiego.com/news/2010/jun/02/former-sequenom-exec-pleads-guilty/
 | 
Pages: 1
Prev: CLOB > 32k
Next: ORA-06502: PL/SQL: numeric or value error: character string buffer too small