您可以借助一些窗口函数、子查询和内部联接来完成此操作。下面的版本相当长，但它并不像看起来那么复杂。它使用子查询中的窗口函数来计算所需的每种货币的统计信息，并将其分解到一个公共表表达式中，以便于将其与自身联接。
除了内联注解，复杂的主要原因是原始查询3。查询(1)和(2)可以很容易地组合为一个简单的聚合查询，但第三个查询就不那么容易处理了。为了保持exchange数据与相应的要价和出价相关联，该查询使用窗口函数而不是聚合查询。这还提供了一种不同于DISTINCT的工具，用于每种货币获得一个结果。
以下是最简单的查询：

WITH output_stats AS (
    -- The ask and bid information for every row of output_table, every row
    -- augmented by the needed maximum ask and minimum bid statistics
    SELECT
      target_currency as tc,
      ask_price_usd as ask,
      bid_price_usd as bid,
      exchange as market,
      MAX(ask_price_usd) OVER (PARTITION BY target_currency) as high,
      ROW_NUMBER() OVER (
        PARTITION_BY target_currency, ask_price_usd ORDER BY exchange DESC)
        as ask_rank
      MIN(bid_price_usd) OVER (PARTITION BY target_currency) as low,
      ROW_NUMBER() OVER (
        PARTITION_BY target_currency, bid_price_usd ORDER BY exchange ASC)
        as bid_rank
    FROM output_table
  )
REPLACE INTO coin_best_returns(
  -- you must, of course, include all the columns you want to fill in the
  -- upsert column list
  coin,
  highest_price,
  lowest_price,
  highest_market,
  lowest_market)
SELECT
  -- ... and select a value for each column
  asks.tc,
  asks.ask,
  bids.bid,
  asks.market,
  bids.market
FROM output_stats asks
  JOIN output_stats bids
    ON asks.tc = bids.tc
WHERE
  -- These conditions choose exactly one asks row and one bids row
  -- for each currency
  asks.ask = asks.high
  AND asks.ask_rank = 1
  AND bids.bid = bids.low
  AND bids.bid_rank = 1

请注意，与原始查询3不同，在目标表中设置highest_market和lowest_market列时，它将只考虑与目标货币相关联的exchange值。我认为这是你真正想要的，但如果不是，那么就需要一个不同的战略。