如何将三个SQL查询合并为单个REPLACE INTO查询？

我有三个查询以及一张名为output_table的表。当前代码可正常运行，但需要将其合并为单个REPLACE INTO查询。我知道这涉及嵌套子查询，但不确定是否可行，因为我的主键是来自target_currency的DISTINCT币种数据。

如何改写第2、3个UPDATE查询，使其融入第1个REPLACE INTO查询中？即用单个REPLACE INTO查询替代独立的UPDATE查询：

# 1. 初始REPLACE INTO语句
conn3.cursor().execute(
    """REPLACE INTO coin_best_returns(coin) SELECT DISTINCT target_currency FROM output_table"""
)

# 2. 更新最高价和最低价的UPDATE语句
conn3.cursor().execute(
    """UPDATE coin_best_returns SET
    highest_price = (SELECT MAX(ask_price_usd) FROM output_table WHERE coin_best_returns.coin = output_table.target_currency),
    lowest_price = (SELECT MIN(bid_price_usd) FROM output_table WHERE coin_best_returns.coin = output_table.target_currency)"""
)

# 3. 更新对应交易所的UPDATE语句
conn3.cursor().execute(
    """UPDATE coin_best_returns SET
        highest_market = (SELECT exchange FROM output_table WHERE coin_best_returns.highest_price = output_table.ask_price_usd),
        lowest_market = (SELECT exchange FROM output_table WHERE coin_best_returns.lowest_price = output_table.bid_price_usd)"""
)

解决方案 #

可以通过预先聚合output_table的数据，一次性完成REPLACE INTO操作，无需拆分执行多条UPDATE。以下提供两种实现方式：

方式一：子查询关联（兼容低版本数据库） #

REPLACE INTO coin_best_returns(coin, highest_price, lowest_price, highest_market, lowest_market)
SELECT
    ot.target_currency,
    max_ask.ask_price_usd AS highest_price,
    min_bid.bid_price_usd AS lowest_price,
    -- 取对应最高价的交易所，LIMIT 1避免多交易所匹配同一价格时出错
    (SELECT exchange FROM output_table WHERE target_currency = ot.target_currency AND ask_price_usd = max_ask.ask_price_usd LIMIT 1) AS highest_market,
    -- 取对应最低价的交易所
    (SELECT exchange FROM output_table WHERE target_currency = ot.target_currency AND bid_price_usd = min_bid.bid_price_usd LIMIT 1) AS lowest_market
FROM
    (SELECT DISTINCT target_currency FROM output_table) ot
LEFT JOIN
    (SELECT target_currency, MAX(ask_price_usd) AS ask_price_usd FROM output_table GROUP BY target_currency) max_ask 
    ON ot.target_currency = max_ask.target_currency
LEFT JOIN
    (SELECT target_currency, MIN(bid_price_usd) AS bid_price_usd FROM output_table GROUP BY target_currency) min_bid 
    ON ot.target_currency = min_bid.target_currency;

逻辑说明：

1. 通过子查询ot获取所有唯一币种
2. 关联max_ask、min_bid子查询分别得到每个币种的最高卖价、最低买价
3. 嵌套子查询匹配对应价格的交易所，用LIMIT 1处理多交易所同价的场景

方式二：窗口函数（适用于MySQL 8.0+等支持窗口函数的数据库） #

REPLACE INTO coin_best_returns(coin, highest_price, lowest_price, highest_market, lowest_market)
SELECT DISTINCT
    target_currency AS coin,
    MAX(ask_price_usd) OVER (PARTITION BY target_currency) AS highest_price,
    MIN(bid_price_usd) OVER (PARTITION BY target_currency) AS lowest_price,
    FIRST_VALUE(exchange) OVER (PARTITION BY target_currency ORDER BY ask_price_usd DESC) AS highest_market,
    FIRST_VALUE(exchange) OVER (PARTITION BY target_currency ORDER BY bid_price_usd ASC) AS lowest_market
FROM output_table;

逻辑说明：

• 利用窗口函数在一次表扫描中完成所有数据聚合，效率更高
• PARTITION BY target_currency确保按币种分组计算极值
• FIRST_VALUE直接取最高/最低价对应的第一个交易所

内容的提问来源于stack exchange，提问作者Mitchs