다른 테이블 SUM의 환율로 가격을 변환하고 AVG를 얻습니다.

다른 테이블 SUM의 환율로 가격을 변환하고 AVG를 얻습니다.

제 아이디어는 다음과 같습니다.

서버: Ubuntu with 최신 MariaDB

이것은 포트폴리오와 마찬가지로, 사용자는 USD와 같은 통화로 주식을 살 수 있지만, 그의 포트폴리오는 EURO일 수 있으므로 가치를 환산해야 합니다.

1. 이전 날짜의 환율을 사용하지 않으면 생성된 날짜 근처의 환율이 필요합니다.
2. 금액_lefts * 가격을 제가 원하는 통화로 환산한 합계(이 경우 1의 비율이 된다는 것을 더 쉽게 이해하기 위해 이 경우 2에서 대상 통화가 쿼리를 통해 제출됩니다.
3. 올바른 구매를 위해 금액의 합계_남았지만 결과는 나눗셈되어야 합니다.AVG 가격이라고도 합니다.

소스 통화가 이제 목표와 동일하므로 계산이 1:1인 것처럼 테스트를 위해 데이터를 더 쉽게 업데이트했습니다.

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

    https://pastebin.com/pSerHN0j

처음 2개의 결과는 제가 원하는 것입니다. 3. 하나는 AVG Buy IN이 맞지만 금액이 두 배가 된 jmvc 공동작업자의 결과입니다.

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로 파티션 내부의 열로 인해 이전 쿼리보다 훨씬 빠르기 때문에 고유하지 않은 키 조회입니다.

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;

다양한 사례를 테스트할 수 있도록 더 많은 데이터를 추가하여 당신이 계속해서 바이올린 작업을 하고 링크를 공유할 것을 제안합니다.

제가 어떻게든 도와줬기를 바랍니다.

Aggregate가 잘못되었기 때문에 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; 

언급URL : https://stackoverflow.com/questions/76352194/convert-prices-with-exchangerates-from-other-table-sum-and-get-avg