雪花最近的约会

Question

我有下面三个表，我想从中检查货币是否不是美元，然后根据汇率进行转换。我需要根据交易日期和当月的exchange_date进行计算，如果没有找到匹配项，则获取小于交易日期的下一个最近的上一个日期。

PRC_TABLE
---------
Txn_Id  Net_Value
------  ---------
T001    120
T002    101.34
T003    54.2
T004    10.5
T005    20.45
TXN_TABLE
---------
Txn_Id  Curr    Ex_Type Txn_Date
------  ----    ------- --------
T001    USD     M       2021-05-28
T002    EUR     M       2019-02-07
T003    INR     A       2018-07-30
T004    EUR     B       2020-08-05
T005    GBP     M       2021-04-07
EXRATE_TABLE
------------
Curr    Ex_Type Ex_rate Ex_Date
----    ------  ------- --------
EUR     M       1.2     2021-07-01
EUR     M       0.75    2020-01-01
EUR     M       2.35    2018-01-01
INR     A       3.5     2021-01-01
INR     A       1.6     2018-07-01
EUR     B       0.54    2021-07-01
EUR     B       1.78    2020-08-01
GBP     M       2.45    2021-07-01
GBP     M       1.67    2020-01-01
USD     M       1       1970-01-01

基本上每个月的汇率都是可用的，但在最坏的情况下，我可能没有特定月份的汇率，或者全年的汇率都相同。我尝试过使用以下方法，如果找到当月的txn_date，则返回值。

select
PR.Txn_Id,
PR.Net_Value,
TX.Curr,
TX.Ex_Type,
TX.Txn_Date,
EX.Ex_rate,
case when TX.Curr = 'USD' then PR.Net_Value else round((PR.Net_Value*EX.Ex_rate),2) end as Conv_Net_Value
from
PRC_TABLE PR
left outer join TXN_TABLE TX
on PR.Txn_Id = TX.Txn_Id
left outer join EXRATE_TABLE EX
on TX.Curr = EX.Curr and TX.Ex_Type = EX.Ex_Type and EX.Ex_Date = date_trunc('MONTH',TX.Txn_Date);

上述查询的结果

TXN_ID  NET_VALUE   CURR    EX_TYPE TXN_DATE    EX_DATE     EX_RATE CONV_NET_VALUE
------  ---------   -----   ------- ---------   -------     ------- --------------
T001    120         USD     M       2021-05-28  NULL        NULL    120
T002    101.34      EUR     M       2019-02-07  NULL        NULL    NULL
T003    54.2        INR     A       2018-07-30  2018-07-01  1.6     86.72
T004    10.5        EUR     B       2020-08-05  2020-08-01  1.78    18.69
T005    20.45       GBP     M       2021-04-07  NULL        NULL    NULL

有人能帮我达到预期的效果吗。我希望最终的数值计算如下。理想情况下，我想获得给定txn_id的转换值。

TXN_ID  NET_VALUE   CURR    EX_TYPE TXN_DATE    EX_DATE     EX_RATE CONV_NET_VALUE
-----   ---------   ----    ------- --------    --------    ------- --------------
T001    120         USD     M       2021-05-28  1970-01-01  1       120
T002    101.34      EUR     M       2019-02-07  2018-01-01  2.35    238.149
T003    54.2        INR     A       2018-07-30  2018-07-01  1.6     86.72
T004    10.5        EUR     B       2020-08-05  2020-08-01  1.78    18.69
T005    20.45       GBP     M       2021-04-07  2020-01-01  1.67    34.1515

Answer 1

我找到了下面的解决方案，请告诉我这是否是检索所需结果的理想方式，否则可以用更好的方式完成某些事情。我正面临这个逻辑的性能问题。

with EX_RATE as (
select TX.Txn_Id,TX.Curr,TX.EX_Type,TX.Txn_Date,EX.Ex_Date,Ex_rate
from TXN_TABLE TX
left outer join EXRATE_TABLE EX
on TX.Curr = EX.Curr and Tx.Ex_type = Ex.Ex_type and EX.Ex_Date <= TX.Txn_Date
qualify row_number() over (partition by TX.Txn_Id,TX.Curr,TX.Ex_Type order by EX.Ex_Date desc) =1
)
select
PR.Txn_Id,
PR.Net_Value,
EX_RATE.Curr,
EX_RATE.Ex_Type,
EX_RATE.Txn_Date,
EX_RATE.EX_Date,
EX_RATE.Ex_rate,
--case when EX_RATE.Curr = 'USD' then PR.Net_Value else round((PR.Net_Value*EX_RATE.Ex_rate),2) end as Conv_Net_Value
round((PR.Net_Value*EX_RATE.Ex_rate),2) as Conv_Net_Value
from
PRC_TABLE PR
left outer join EX_RATE
on PR.Txn_id = EX_RATE.Txn_id;

上述查询的输出

TXN_ID  NET_VALUE   CURR    EX_TYPE TXN_DATE    EX_DATE     EX_RATE CONV_NET_VALUE
------  ---------   ----    ------- ---------   -------     ------- --------------
T001    120         USD     M       2021-05-28  1970-01-01  1       120
T002    101.34      EUR     M       2019-02-07  2018-01-01  2.35    238.15
T003    54.2        INR     A       2018-07-30  2018-07-01  1.6     86.72
T004    10.5        EUR     B       2020-08-05  2020-08-01  1.78    18.69
T005    20.45       GBP     M       2021-04-07  2020-01-01  1.67    34.15