mariadb 将价格与其他表SUM中的汇率进行转换并获得AVG

我的想法是：
服务器：Ubuntu
这是一个投资组合算法，用户可以用美元等货币购买股票，但他的投资组合可以是欧元，因此必须转换价值。
1.我需要一个汇率附近的创建日期，如果不使用从日期前的汇率。
1.将amounts_lefts * 价格转换为我想要的货币（在这种情况下，为了更容易理解，它将是1的汇率，目标货币将在这种情况下提交查询2。
1.结果应该除以amounts_left的总和以获得正确的buyIN aka AVG价格。
我更新了数据，使测试更简单，比如源货币现在和目标货币相同，所以计算是1：1

SQL Fiddle is here: http://sqlfiddle.com/#!9/8c7fc59/11
Results running against my DB with querys 

    https://pastebin.com/pSerHN0j

前两个结果是我想要的，第三个。一个来自jmvcollaborator，其中AVG BuyIN是正确的，但金额加倍

SQL Export with more Data 

    https://pastebin.com/d1aVX2wm

更新问题：
通过子查询的解决方案正在工作，但这种方法在性能上不是最好的，所以我目前正在寻找一个没有这个的解决方案。
我的资料分区结束后应该在这里有一个解决办法，不过这不是我的那种知识，所以也许有人可以在这里帮助我。

我会写一些调查结果在这里，首先我想仔细检查，你的第一个查询工程，请确认：

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;

我建议添加更多的数据，以便可以测试许多不同的案例，您可以继续研究并分享链接。
我希望我以某种方式帮助。
由于聚合错误，更新了使用Over Partition By的UPDATE查询。注意事项：但是性能仍然优于其它方法。下面是工作查询：

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;

mariadb 将价格与其他表SUM中的汇率进行转换并获得AVG

1条答案

相关问题

热门标签

最新问答