Andrey MetelyovМда...
у меня видимо проблема в другом...
Вот запрос, работающий на Oracle:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
select datetime, sum(h2124)* 100 /sum(h2089) TCH_BLOCKING_RATE_BH_MAX
from cellstats
where trunc(datetime) in (
select day
from (
select trunc(datetime) day, sum(h2277) traffic
from cellstats
where datetime >= trunc(sysdate)- 7 and datetime < trunc(sysdate)
group by trunc(datetime)
order by traffic desc)
where rownum<= 5
)
group by
datetime
order by
TCH_BLOCKING_RATE_BH_MAX desc
Для подзапросов можно использовать конструкцию TABLE(MULTISET()).
Подзапрос из Оракла:
1.
2.
3.
4.
select trunc(datetime) day, sum(h2277) traffic
from cellstats
where datetime >= trunc(sysdate)- 7 and datetime < trunc(sysdate)
group by trunc(datetime)
order by traffic desc
на первый взгляд можно заменить на:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
select s2.day, count(*) as rownum
from table(multiset(
select date(date_and_time) as day, sum(busy_tch_mean) as traffic
from v_cell_statistics
where date_and_time >= date(current) - 7 and date_and_time < date(current)
)) s1
join table(multiset(
select date(date_and_time) as day, sum(busy_tch_mean) as traffic
from v_cell_statistics
where date_and_time >= date(current) - 7 and date_and_time < date(current)
)) s2 on date(s2.date_and_time) = date(s1.date_and_time)
and (s2.traffic > s1.traffic or s2.traffic = s1.traffic and s2.day >= s1.day)
group by 1
Остальная часть запроса будет без изменений:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
select date_and_time, sum(alloc_tch_fail)* 100 /sum(alloc_tch+alloc_tch_fail) as TCH_BLOCKING_RATE_BH_MAX
from v_cell_statistics
where date(date_and_time) in (
select day
from table(multiset(
select s2.day, count(*) as rownum
from table(multiset(
select date(date_and_time) as day, sum(busy_tch_mean) as traffic
from v_cell_statistics
where date_and_time >= date(current) - 7 and date_and_time < date(current)
)) s1
join table(multiset(
select date(date_and_time) as day, sum(busy_tch_mean) as traffic
from v_cell_statistics
where date_and_time >= date(current) - 7 and date_and_time < date(current)
)) s2 on date(s2.date_and_time) = date(s1.date_and_time)
and (s2.traffic > s1.traffic or s2.traffic = s1.traffic and s2.day >= s1.day)
group by 1
)) s
where rownum <= 5 )
group by date_and_time
order by TCH_BLOCKING_RATE_BH_MAX desc
Проще через временные таблички, но в любом случае настолько красиво как в Оракле - не выйдет...
ЗЫ
Возможны ошибки, но в общем - так...
