Snowflake获取最近的上次日期

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