Home » SQL & PL/SQL » SQL & PL/SQL » SQL Question / Analytic function (Ora 10g 64bit)
SQL Question / Analytic function [message #398210] |
Wed, 15 April 2009 12:52  |
pw1975
Messages: 24 Registered: June 2006
|
Junior Member |
|
|
Hi all,
i am facing a table with about 1 mio entries with this special content:
timestamp | server_id | free_ram | free_hdd
------------------------------------------------------------
01.01.2009 | 1 | 200 | null
02.01.2009 | 1 | 100 | 500
03.01.2009 | 1 | 300 | null
04.01.2009 | 1 | 100 | 400
05.01.2009 | 1 | 200 | null
01.01.2009 | 2 | 100 | null
02.01.2009 | 2 | 100 | 1500
03.01.2009 | 2 | 100 | null
04.01.2009 | 2 | 100 | 2400
05.01.2009 | 2 | 100 | null
So for different servers we receive different monitoring KPIs (in this case free MB of RAM and free MB of Harddiskspace)
These KPIs are delivered in different cycles, as you can see above. Free-HDD is delivered only each second day.
So if a report queries this table and is executed on the day, harddiskspace was not delivered, the user will see "null".
I´d like to find a (performant!) solution, on how i always can present the last value (regarding the column timestamp) of any KPI per server_id (there are more than two, but just to make ist easier).
So in the case of server_id = 1 i´d like to get as a result
200 for free ram and 400 for free hdd. I do not want to use PL/SQL, if it is not necessary...
Example-Code:
drop table zz_t5;
create table zz_t5 (
tstamp date,
server_id number,
free_ram number,
free_hdd number);
insert into zz_t5 values (sysdate, 1, 200, null);
insert into zz_t5 values (sysdate-1, 1, 100, 500);
insert into zz_t5 values (sysdate-2, 1, 100, null);
insert into zz_t5 values (sysdate-3, 1, 300, 300);
insert into zz_t5 values (sysdate-4, 1, 100, null);
insert into zz_t5 values (sysdate-5, 1, 500, 200);
insert into zz_t5 values (sysdate, 2, 100, null);
insert into zz_t5 values (sysdate-1, 2, 300, 200);
insert into zz_t5 values (sysdate-2, 2, 200, null);
insert into zz_t5 values (sysdate-3, 2, 400, 100);
insert into zz_t5 values (sysdate-4, 2, 200, null);
insert into zz_t5 values (sysdate-5, 2, 400, 400);
commit;
The result i´d like to receive would look like:
timestamp | server_id | free_ram | free_hdd
------------------------------------------------------------
05.01.2009 | 1 | 200 | 400 <- last value from day before
Thanks very much for your help!
Cheers,
Philipp
[Updated on: Wed, 15 April 2009 12:59] Report message to a moderator
|
|
|
|
|
Re: SQL Question / Analytic function [message #398243 is a reply to message #398210] |
Wed, 15 April 2009 13:51   |
pw1975
Messages: 24 Registered: June 2006
|
Junior Member |
|
|
i think i found a proper way:
select
tstamp
, server_id
, free_ram
, coalesce(last_value(free_hdd ignore nulls) over (partition by server_id order by tstamp asc),
last_value(free_hdd ignore nulls) over (partition by server_id order by tstamp desc)) free_hdd_analytical
, free_hdd
from
zz_t5
order by
server_id, tstamp desc;
would this one be appropriate or is there any better way?
Cheers,
Philipp
|
|
|
|
Re: SQL Question / Analytic function [message #398247 is a reply to message #398243] |
Wed, 15 April 2009 13:59   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
pw1975 wrote on Wed, 15 April 2009 20:51 | i think i found a proper way:
select
tstamp
, server_id
, free_ram
, coalesce(last_value(free_hdd ignore nulls) over (partition by server_id order by tstamp asc),
last_value(free_hdd ignore nulls) over (partition by server_id order by tstamp desc)) free_hdd_analytical
, free_hdd
from
zz_t5
order by
server_id, tstamp desc;
would this one be appropriate or is there any better way?
Cheers,
Philipp
|
It is another way to do it.
I don't understand the need of your coalesce, why the first part only does not fit your requirements?
Regards
Michel
|
|
|
|
Goto Forum:
Current Time: Sat Feb 15 22:40:14 CST 2025
|