Sql 使用其他表 SUM 的交换价格转换价格并获得 AVG

我会在这里写下一些发现，首先我想再次判断您的第一个查询是否有效，请确认:

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 Bytry 了一种不同的方法，查询成本是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;

我建议添加更多的数据，这样就可以测试许多不同的 case ，您可以继续在小提琴上工作，分享链接.

希望我能在某种程度上帮上忙.

由于聚合错误，更新了UPDATE个带有Over Partition by的查询.IMPORTANT:尽管如此，性能仍然比其他方法更好. 以下是正在运行的查询:

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;