mysql - 比较两个不同表中的两个列值并根据 24 小时周期计算差异

Question

I have two tables asset_data and ticker_data. Symbol column in asset_data needs to match with quoteAsset in table ticker_data.If match found, calculate the price difference between lastPriceUSD in the last 24 hours by using the createdAt stamp.

Basically comparing the two prices of the same currency symbol in 24 hours cycle. eg. if the price of EUR on 2019-05-12 at 18:05 is 0.89, need to compare EUR with yesterday at the same time with 2019-05-11 at 18:05

asset_data Table

+---------+--------+--------+--------------+---------------------+
| assetId | pair   | symbol | lastPriceUSD | createdAt           |
+---------+--------+--------+--------------+---------------------+
|       1 | EURUSD | EUR    | 0.8900000000 | 2019-05-12 18:05:10 |
|       2 | AUDEUR | AUD    | 0.6500000000 | 2019-05-12 18:05:45 |
+---------+--------+--------+--------------+---------------------+

ticker_data Table

+----------+--------+------------+--------------+---------------------+
| tickerId | pair   | quoteAsset | lastPriceUSD | createdAt           |
+----------+--------+------------+--------------+---------------------+
|        1 | USDEUR | EUR        | 0.9500000000 | 2019-05-11 18:06:40 |
|        2 | EURAUD | AUD        | 0.7500000000 | 2019-05-11 18:17:49 |
+----------+--------+------------+--------------+---------------------+

预期结果

symbol     difference
 EUR         -6.00%
 AUD         -10.00%

Answer 1

您可以像这样连接表:

select 
  a.symbol,
  100.0 * (a.lastpriceusd - t.lastpriceusd) / a.lastpriceusd as difference
from asset_data a inner join ticker_data t
on t.quoteasset = a.symbol and (
    select min(createdat) from ticker_data 
    where quoteasset = a.symbol and 
    createdat between date_add(a.createdat, interval '-1' day) and a.createdat
  ) = t.createdat 

条件:

compare EUR with yesterday at the same time with 2019-05-11 at 18:05

如果 2 个日期相差恰好 1 天，则无法准确，因此我使用 ticker_data 中大于 的最小 createdat >创建时间 - 距离 asset_data

1 天