我会在这里写一些发现，首先我想要双重确认你的第一个查询是否有效，请确认：
    SELECT SUM(amounts_left*price)/SUM(amounts_left) as eBuyIN
     FROM transactions as t 
     WHERE 1 GROUP BY currency_id,broker_id,portfolio_id,instrument_id; 
如果不起作用，那么让我分享一些有关我们已经使其工作的查询的发现：
    SELECT 
        SUM(price)/SUM(mleft) 
    FROM(
        SELECT
            `instrument_id`,
            amounts_left AS mleft, 
            amounts_left * price * (
                 SELECT `rate` 
                 FROM `currency_exchanges` 
                 WHERE `from_currency_id` = t.currency_id 
                   AND `to_currency_id` = 2 
                   AND `date` <= t.created 
                 ORDER BY `date` DESC LIMIT 1
            ) AS price
        FROM `transactions` AS `t` 
        WHERE `action` = 1 
          AND `portfolio_id` = 128 
    ) a
查询成本为2.72，因为它执行了全表扫描，由于嵌套查询。我尝试了一种不同的方法，使用CTE和OVER PARTITION BY，查询成本为0.35，由于分区内的列是非唯一键查找，因此比以前的查询要快得多，下面是CTE代码：
    WITH 
    cte AS (
    SELECT ROW_NUMBER() OVER(PARTITION BY t.currency_id,t.broker_id,t.portfolio_id,t.instrument_id ORDER BY amounts_left DESC
            ) AS rowNumber,        
           SUM(amounts_left) OVER(PARTITION BY t.currency_id,t.broker_id,t.portfolio_id,t.instrument_id
           ORDER BY amounts_left DESC ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS amountsleftT,       
           SUM(amounts_left*price) OVER(PARTITION BY t.currency_id,t.broker_id,t.portfolio_id,t.instrument_id
           ORDER BY amounts_left DESC ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS priceT
    FROM currency_exchanges AS ce
    JOIN transactions AS t
    ON from_currency_id = t.currency_id 
    AND to_currency_id = 2 
    AND ce.date <= t.created
    AND action = 1 and portfolio_id = 128 
    )
    Select priceT/amountsleftT as Total from cte where rowNumber = 1;
我建议添加更多数据，以便可以测试许多不同的情况，您可以继续在fiddle上工作并分享链接。
希望我有些帮助。
**更新** 使用Over Partition By的查询已更新，因为聚合错误。**重要提示**：性能仍然优于其他方法。以下是有效的查询：
    WITH cte AS
    (
    select 
    instrument_id,
    ROW_NUMBER() OVER(PARTITION BY t.currency_id,t.broker_id,t.portfolio_id,t.instrument_id) AS rowNumber,
    Sum(amounts_left* price *ce.rate) OVER(PARTITION BY t.currency_id,t.broker_id,t.portfolio_id,t.instrument_id) AS priceSum,
    Sum(amounts_left) OVER(PARTITION BY t.currency_id,t.broker_id,t.portfolio_id,t.instrument_id) amountsleftSum 
    from transactions t left join
         (select ce.*, row_number() over (partition by from_currency_id order by date desc) as seqnum
          from currency_exchanges ce
          where
             to_currency_id = 2     
          order by ce.date desc
         ) ce
         on ce.from_currency_id = t.currency_id and seqnum = 1
      WHERE 
        ce.date <= t.created 
        AND action = 1 AND amounts_left > 0 AND portfolio_id = 128 
        
    )    
    SELECT     
     priceSum/amountsleftSum as   eBuyIN,
           amountsleftSum AS amounts_left,
           instrument_id       
     FROM cte
     WHERE  rownumber = 1;