ORACLE取中值解决办法

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