mysql - 如何在sql中选择前一个最接近的值

Question

现在我有 2 张 table :

*表1-*

日期                汇率

00/00/0000      0.6

2018 年 4 月 1 日          0.81

2018 年 4 月 2 日       0.82

2018 年 5 月 1 日          0.83

2018 年 5 月 2 日          0.81

2018 年 5 月 3 日          0.82

*表2 -*

日期                   美元

2018 年 2 月 2 日            50

2018 年 3 月 5 日            60

2018 年 4 月 1 日            50

2018 年 4 月 3 日            100

2018 年 4 月 15 日          200

2018 年 5 月 1 日            60

2018 年 5 月 2 日            80

2018 年 5 月 3 日            90

我想要一个反射(reflect)转换金额的表 3 *Table3 -*

日期                   美元        换算金额

2018 年 2 月 2 日            50          50*0.6 = 30

2018 年 3 月 5 日            60          60*0.6 = 36

2018 年 4 月 1 日            50          50*0.81 = 40.5

2018 年 4 月 3 日            100        100*0.82 = 82

2018 年 4 月 15 日          200        200*0.82 = 164

2018 年 5 月 1 日            60          60*0.83 = 49.8

2018 年 5 月 2 日            80          80*0.81 = 64.8

2018 年 5 月 3 日            90          90*0.82 = 73.8

如何在 SQL 中实现这一点？原始数据很大。

问题是，我希望 Table3 的 4/15/2018 和 4/3/2018 conversion_amount 遵循 4/2/2018 汇率，因为没有 4/15/2018 或 4/3/2018 交换率见表1。本质上，我希望缺失的汇率遵循其最近日期的汇率。

此外，对于 2018 年 4 月 1 日之前的所有日期，汇率应遵循表 1 的 00/00/0000 汇率。

非常感谢您的帮助!非常感谢!

Answer 1

您可以使用相关子查询来查找表 1 中与表 2 中的每个日期最接近的日期:

SELECT
    t2.Date,
    t2.USD,
    t2.USD * (SELECT t1.ExchangeRate FROM Table1 t1
              WHERE t1.Date <= t2.Date
              ORDER BY DATEDIFF(t2.Date, t1.Date) LIMIT 1) AS ConvertedAmount
FROM Table2 t2;

Demo

请注意，这是一种不受控制的方法，因为表 1 中的日期可能位于表 2 中的日期的两侧，并且可以任意选择一个日期。实际上，更好的长期方法是使用仅包含每天外汇汇率的日历表。