在筛选之前，使用条件聚合将gbp和ils速率放入列中。

select base_currency,currency_code,rate
from (select r.*
            ,max(case when currency_code='GBP' then rate end) over(partition by base_currency) as gbp_rate
            ,max(case when currency_code='ILS' then rate end) over(partition by base_currency) as ils_rate
      from rate r
      where base_currency = 'USD'
     ) r
where rate >= gbp_rate and rate <= ils_rate