select station,freq,service_area,destination_place,avg(H_VALUE) from
(
(
select b.*
from
( -- 计算中间一行的行号
select station,freq,service_area,destination_place,decode(mod(max_rk, 2), -- 取余数
0, max_rk / 2, -- 偶数
trunc(max_rk / 2) + 1 -- 奇数
) mid_rk
from ( -- 计算行号
select station,freq,service_area,destination_place,max(a1.rk) max_rk
from (-- 排序的结果集
select a11.*,
row_number() over(partition by station,freq,service_area,destination_place order by H_VALUE desc) rk
from effect_intense_info a11
) a1 group by station,freq,service_area,destination_place
)
) a ,
( -- 排序的结果集
select b1.*,
row_number() over(partition by station,freq,service_area,destination_place order by H_VALUE desc) rk
from effect_intense_info b1
) b
where b.rk = a.mid_rk and b.station=a.station and b.freq=b.freq and a.service_area = b.service_area and a.destination_place = b.destination_place
)
union
(
select b.*
from
( -- 计算中间一行的行号
select station,freq,service_area,destination_place,decode(mod(max_rk, 2), -- 取余数
0, max_rk / 2 + 1, -- 偶数
trunc(max_rk / 2) + 1 -- 奇数
) mid_rk
from ( -- 计算行号
select station,freq,service_area,destination_place,max(a1.rk) max_rk
from (-- 排序的结果集
select a11.*,
row_number() over(partition by station,freq,service_area,destination_place order by H_VALUE desc) rk
from effect_intense_info a11
) a1 group by station,freq,service_area,destination_place
)
) a ,
( -- 排序的结果集
select b1.*,
row_number() over(partition by station,freq,service_area,destination_place order by H_VALUE desc) rk
from effect_intense_info b1
) b
where b.rk = a.mid_rk and b.station=a.station and b.freq=b.freq and a.service_area = b.service_area and a.destination_place = b.destination_place
)
) AA group by station,freq,service_area,destination_place
(
(
select b.*
from
( -- 计算中间一行的行号
select station,freq,service_area,destination_place,decode(mod(max_rk, 2), -- 取余数
0, max_rk / 2, -- 偶数
trunc(max_rk / 2) + 1 -- 奇数
) mid_rk
from ( -- 计算行号
select station,freq,service_area,destination_place,max(a1.rk) max_rk
from (-- 排序的结果集
select a11.*,
row_number() over(partition by station,freq,service_area,destination_place order by H_VALUE desc) rk
from effect_intense_info a11
) a1 group by station,freq,service_area,destination_place
)
) a ,
( -- 排序的结果集
select b1.*,
row_number() over(partition by station,freq,service_area,destination_place order by H_VALUE desc) rk
from effect_intense_info b1
) b
where b.rk = a.mid_rk and b.station=a.station and b.freq=b.freq and a.service_area = b.service_area and a.destination_place = b.destination_place
)
union
(
select b.*
from
( -- 计算中间一行的行号
select station,freq,service_area,destination_place,decode(mod(max_rk, 2), -- 取余数
0, max_rk / 2 + 1, -- 偶数
trunc(max_rk / 2) + 1 -- 奇数
) mid_rk
from ( -- 计算行号
select station,freq,service_area,destination_place,max(a1.rk) max_rk
from (-- 排序的结果集
select a11.*,
row_number() over(partition by station,freq,service_area,destination_place order by H_VALUE desc) rk
from effect_intense_info a11
) a1 group by station,freq,service_area,destination_place
)
) a ,
( -- 排序的结果集
select b1.*,
row_number() over(partition by station,freq,service_area,destination_place order by H_VALUE desc) rk
from effect_intense_info b1
) b
where b.rk = a.mid_rk and b.station=a.station and b.freq=b.freq and a.service_area = b.service_area and a.destination_place = b.destination_place
)
) AA group by station,freq,service_area,destination_place