Re: Help with query

From: Chuck <skilover_nospam_at_bluebottle.com>
Date: Fri, 08 Dec 2006 18:45:13 GMT
Message-ID: <Zoieh.21$bj5.15@trnddc07>

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Bruman wrote:

> Michel Cadot wrote:>

>> Here's another query which works even if the interval
>> between 2 rows is not 10 minutes:
>>
>> select begin_time,
>> last_value(end_time)
>> over (order by begin_time
>> range between current row
>> and interval '1:59:59' hour to second following)
>> end_time,
>> sum(undoblks)
>> over (order by begin_time
>> range between current row
>> and interval '1:59:59' hour to second following)
>> undoblks
>> from v$undostat
>> order by begin_time
>> /
>>
>> Regards
>> Michel Cadot

> 
> I like it.  Is there a good source on Analytic Functions other than the
> Oracle documentation?  Some of us dunderheads have trouble following
> the little charts and need examples to understand what is going on.
> 

Not sure why but it's not returning the same results as my query that doesn't use the analytic functions. I'm just not familiar enough with the analytic functions to debug it yet. Like Bruman, I'm looking for a good reference on them. The Oracle documentation seem to be written for someone who already knows them, not someone who's trying to learn them.

SELECT a.begin_time,

         MAX (b.end_time) end_time,
         SUM (b.undoblks) * 8 / 1024 undo_mb
    FROM v$undostat a JOIN v$undostat b
         ON b.begin_time >= a.begin_time
       AND b.begin_time < a.begin_time + 2/24

GROUP BY a.begin_time
ORDER BY 1 DESC;
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (MingW32)

iEYEARECAAYFAkV5srkACgkQzIf+rZpn0oQKzQCfeIoow6xua0BDEPZJRH2ovk+R kz8AoJDKqNHhPwu6SI/37rQKjHw+8xBF
=NgiV
-----END PGP SIGNATURE----- Received on Fri Dec 08 2006 - 12:45:13 CST