通過最近的匹配連接兩個表

一般問題：我想在兩個大表上進行左連接，但沒有匹配的鍵 - 我想將左表與右表中的“最近”行連接起來。在此範例中，我想使用時間戳加入，但我認為地理座標會有類似（儘管更複雜）的問題。

為了簡化很多，我有一個帶有主鍵 id、時間戳、貨幣和金額的交易表 - 以及一個帶有 id、時間戳、貨幣和匯率的貨幣匯率表。這些表是相互獨立的，它們包含很多行；假設在某些時期，每次匯率更新之間有許多交易，而在其他時期，每次交易都有許多匯率更新。我想加入他們，以便每筆交易都有一行，具有最接近的匹配貨幣匯率。我有關於時間戳的索引。

所以，這裡有一個簡短的例子，說明我所擁有的和需要的：

transaction:
+---------------------+--------+
| timestamp           | amount |
+---------------------+--------+
| 2017-07-31 00:07:05 | 173.17 |
| 2017-08-02 15:29:11 | 136.57 |
| 2017-08-05 03:45:24 |  81.27 |
| 2017-08-05 03:46:47 |   48.1 |
| 2017-08-05 03:47:38 |  35.21 |
+---------------------+--------+

rate:
+---------------------+-------------------+
| timestamp           | rate              |
+---------------------+-------------------+
| 2017-07-31 00:04:49 |            9.2923 |
| 2017-07-31 01:37:59 | 9.313423370522607 |
| 2017-08-01 08:07:59 |             9.325 |
| 2017-08-01 16:52:23 |            9.3542 |
| 2017-08-01 21:07:09 | 9.357076262185192 |
| 2017-08-02 15:07:33 |  9.34936993421895 |
| 2017-08-02 17:48:45 | 9.357217848393876 |
| 2017-08-04 04:33:51 |  9.38690807898736 |
| 2017-08-04 08:13:01 | 9.383765889641701 |
| 2017-08-06 03:45:03 | 9.118193727124817 |
| 2017-08-06 04:15:01 | 9.353042966450854 |
| 2017-08-06 05:23:29 | 9.353042966450854 |
+---------------------+-------------------+

Want something like this:
+---------------------+--------+--------+
| timestamp           | amount | rate   |
+---------------------+--------+--------+
| 2017-07-31 00:07:05 | 173.17 | 9.2923 |
| 2017-08-02 15:29:11 | 136.57 | 9.3493 |
| 2017-08-05 03:45:24 |  81.27 | 9.3869 |
| 2017-08-05 03:46:47 |   48.1 | 9.3869 |
| 2017-08-05 03:47:38 |  35.21 | 9.3869 |
+---------------------+--------+--------+

（生產數據比這密集得多）

後期編輯：我確實嘗試使用子查詢，它失敗了，但可能是因為我這邊的一個錯誤。子查詢似乎是解決此問題的最佳方法，因此我將其從問題方面移到了答案中。

我想將左表與右表中的“最近”行連接起來。

這是一個 KNN 問題。為此，您可能需要一個真正的數據庫。我強烈建議您遷移到 PostgreSQL 並使用 PostGIS，這可以使用索引來解決。您的答案與您的問題有關。你的問題是，

通過最近的匹配

您的答案只關注單向方向性，並且只針對一行而不是一組：它是一個相關的子查詢。您只需要改變路線，或賞金開發人員將 KNN 包含在 MySQL 中。MySQL 正在為 GIS 使用 Boost，所以也許有一天有人會跳入nearest(Point const &, unsigned)程式碼庫。我檢查了你，它不在那裡，也沒有專業的方法可以在任何規模上做你想做的事。

MySQL 的 GIS 產品是所有主要參與者中最差的。沒有人使用它。為了讓您了解規模，

• MySQL 在 GIS.SE 上有 225 個問題。
• PostGIS 有 5,771

說它支持空間索引是一個行銷點。也可以看看

• PostgreSQL vs. MySQL：空間特徵比較

順便說一句，PostGIS 還支持在 timestampg 上使用 KNNbtree_gist

UNION用這兩個表創建一個表；ORDER BY時間戳；創建一個AUTO_INCREMENT.

現在“附近”變成了“相鄰”。

使用自連接將 id 與 id+1 進行比較以查看相鄰項目。

引用自：https://dba.stackexchange.com/questions/194810