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 Go to next message
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 #398217 is a reply to message #398210] Wed, 15 April 2009 13:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at LAG/LEAD functions.

Regards
Michel
Re: SQL Question / Analytic function [message #398237 is a reply to message #398210] Wed, 15 April 2009 13:36 Go to previous messageGo to next message
pw1975
Messages: 24
Registered: June 2006
Junior Member
Hi Michel,

thanks for the quick response. I tried those functions but the offset is not always the same (in contrary to my example, sorry for that) ...

Is there any other way?

Regards,
Philipp
Re: SQL Question / Analytic function [message #398243 is a reply to message #398210] Wed, 15 April 2009 13:51 Go to previous messageGo to next message
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 #398244 is a reply to message #398237] Wed, 15 April 2009 13:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Define groups that starts or ends (depending on what you want) with each not null value and take the max value of each group using MAX in its analytic form.

Regards
Michel
Re: SQL Question / Analytic function [message #398247 is a reply to message #398243] Wed, 15 April 2009 13:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
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

Re: SQL Question / Analytic function [message #398380 is a reply to message #398210] Thu, 16 April 2009 02:26 Go to previous message
pw1975
Messages: 24
Registered: June 2006
Junior Member
hm, your right... yesterday it looked like that i need this workaround Wink maybe it was too late...

Thanks!
Previous Topic: Select the data with highest update no
Next Topic: Time difference and sum
Goto Forum:
  


Current Time: Mon Dec 05 18:55:23 CST 2016

Total time taken to generate the page: 0.27798 seconds