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
