Hope this helps. Let me know if you have any more questions.
update daily d2
set (avg_30day, busy_hour_value, busy_hour) =
(select avg(d1.daily_metric), max(h1.metric_value), h1.timestamp
from daily d1 left outer join hourly h1 on d1.id = h1.id and d1.timestamp = trunc (h1.timestamp)
where d1.id = d2.id and d1.timestamp > (d2.timestamp - 29) and d1.timestamp <= d2.timestamp)
select avg(d1.daily_metric) over() avg_30_days,
h1.metric_value,
h1.ts
from daily d1
left outer join (select h.*, row_number() over(partition by trunc(h.ts) order by metric_value desc) max_hour from hourly h) h1
on d1.ts=trunc(h1.ts)
where h1.max_hour=1
update daily d2
set (avg_30day, max_hour_value, max_hour) =
( select avg(d1.daily_metric),
max(h1.metric_value),
max(h1.date_time) keep (dense_rank first order by h1.metric_value desc)
from daily d1 left outer join hourly h1 on (d1.id = h1.id and d1.date_time = trunc (h1.date_time) )
where d1.id = d2.id
and d1.date_time > (d2.date_time - 29)
and d1.date_time <= d2.date_time)
Title | # Comments | Views | Activity |
---|---|---|---|
How to change character set after migration from 10g to 11g ? | 8 | 62 | |
oracle report printing 2 pages in one page | 2 | 35 | |
case statement in where clause with not exist | 15 | 32 | |
scheduler for Procedure in DB with 3 arguments in 10g | 7 | 8 |
Join the community of 500,000 technology professionals and ask your questions.
Connect with top rated Experts
12 Experts available now in Live!