mysql - 对聚合查询执行外汇计算的子查询

Question

我有一个表 transactions 。这些有字段 id , trans_date , amount , currency .

我还有一张 table fx_rates带字段id , fx_date , currency , rate_to_gbp .

我希望对特定日期的英镑交易金额进行求和。例如

SELECT format(transactions.trans_date, "mm-yyyy") as monthyear, sum(transactions.amount) as amount_sum
FROM transactions
GROUP BY format(transactions.trans_date, "mm-yyyy");

但我的一些交易不是以英镑为单位。是否可以对具有 transactions.currency <> "GBP" 的字段执行货币转换通过使用子查询？

类似于:

sum(iif(transactions.currency = "GBP",
   transactions.amount,
   transactions.amount / (
     SELECT fx_rates.rate_to_gbp 
     WHERE fx_rates.currency = transactions.currency 
       AND transactions.trans_date = fx_rates.fx_date
   ))

？？？

编辑:下面的示例数据...

交易:

id      account_id  trans_date  amount      currency
----------------------------------------------------
1797    GFSS35      28/01/2005  365000      USD 
1798    LA1F8       04/03/2005  100247.57                   
1799    IDFS12      31/03/2005  2396.89     GBP         
1800    DF088       06/04/2005  14000       EUR         
1801    BGD005      18/05/2005  450000      

外汇汇率:

id      fx_date     currency    rate_to_gbp
-------------------------------------------
3367    25/11/2018  USD         1.62
3368    25/11/2018  EUR         1.2
3369    25/11/2018  CHF         1.47
3412    11/12/2013  USD         1.64
3413    11/12/2013  EUR         1.2
3414    11/12/2013  CHF         1.46        

Answer 1

看起来您应该进行连接。

SELECT format(transactions.trans_date, "mm-yyyy") as monthyear, 
  SUM(IIF(transactions.currency = "GBP" OR transactions.currency = "", 
    transactions.amount,
    transactions.amount/fx_rates.rate_to_gbp)) AS amount_sum
FROM transactions
LEFT JOIN fx_rates 
ON fx_rates.currency = transactions.currency 
AND transactions.trans_date = fx_rates.fx_date
GROUP BY format(transactions.trans_date, "mm-yyyy");

我选择了 LEFT JOIN，因为我不确定当 transactions.currency = "GBP"时 fx_rates 表中是否会有相应的记录。如果应该的话，我会使用 INNER JOIN 以避免 fx_rates.rate_to_gbp 可能为 null 的任何情况。