tags:

views:

31

answers:

1

table1 -> id, time_stamp, value

This table consists of 10 id's. Each id would be having a value for each hour in a day. So for 1 day, there would be 240 records in this table.

table2 -> id

Table2 consists of a dynamically changing subset of id's present in table1.

At a particular instance, the intention is to get sum(value) from table1, considering id's only in table2, grouping by each hour in that day, giving the summarized values a rank and repeating this each day.

the query is at this stage:

select time_stamp, sum(value),
rank() over (partition by trunc(time_stamp) order by sum(value) desc) rn
from table1
where exists (select t2.id from table2 t2 where id=t2.id)
and
time_stamp >= to_date('05/04/2010 00','dd/mm/yyyy hh24') and
time_stamp <= to_date('25/04/2010 23','dd/mm/yyyy hh24')
group by time_stamp
order by time_stamp asc

If the query is correct, can this be made more efficient, considering that, table1 will actually consist of thousand's of id's instead of 10 ?

EDIT: I am using sum(value) 2 times in the query, which I am not able to get a workaround such that the sum() is done only once. Pls help on this

+2  A: 
shahkalpesh
Oops. typo. corrected
Abhi
No problem. Using `join` or 'inner join` could be of help in your case.
shahkalpesh