bugfix> mysql > 投稿

私は2つのテーブルを持っています。1つは異なる間隔(約30分ごと)でプルされた米ドルの暗号値です。

「暗号」テーブル

  +-----+---------------------+-------------+-------------+-----------+
    | id  | date_hour           | crypto_code | crypto_name | price_usd |
    +-----+---------------------+-------------+-------------+-----------+
    |  13 | 2018-05-24 16:43:29 | BTC         | Bitcoin     | 7553.75   |
    |  14 | 2018-05-24 16:43:29 | ETH         | Ethereum    | 584.764   |
    |  15 | 2018-05-24 16:43:29 | BTM         | Bytom       | 0.617043  |
    |  16 | 2018-05-24 16:43:29 | DCR         | Decred      | 102.578   |
    |  17 | 2018-05-24 16:43:29 | SC          | Siacoin     | 0.0164824 |
    |  18 | 2018-05-24 16:43:29 | ZEC         | Zcash       | 289.619   |
    |  19 | 2018-05-24 17:00:01 | BTC         | Bitcoin     | 7528.51   |
    |  20 | 2018-05-24 17:00:01 | ETH         | Ethereum    | 584.769   |
    |  21 | 2018-05-24 17:00:01 | BTM         | Bytom       | 0.614629  |
    |  22 | 2018-05-24 17:00:01 | DCR         | Decred      | 102.229   |
    |  23 | 2018-05-24 17:00:01 | SC          | Siacoin     | 0.016547  |
    |  24 | 2018-05-24 17:00:01 | ZEC         | Zcash       | 286.252   |
    |  25 | 2018-05-24 17:31:01 | BTC         | Bitcoin     | 7508.15   |
    |  26 | 2018-05-24 17:31:01 | ETH         | Ethereum    | 584.024   |
    |  27 | 2018-05-24 17:31:01 | BTM         | Bytom       | 0.607491  |
    |  28 | 2018-05-24 17:31:01 | DCR         | Decred      | 101.801   |
    |  29 | 2018-05-24 17:31:01 | SC          | Siacoin     | 0.0163432 |
    |  30 | 2018-05-24 17:31:01 | ZEC         | Zcash       | 289.187   |
    |  31 | 2018-05-24 18:00:01 | BTC         | Bitcoin     | 7545.02   |
    |  32 | 2018-05-24 18:00:01 | ETH         | Ethereum    | 587.121   |
    |  33 | 2018-05-24 18:00:01 | BTM         | Bytom       | 0.612734  |
    |  34 | 2018-05-24 18:00:01 | DCR         | Decred      | 102.667   |
    |  35 | 2018-05-24 18:00:01 | SC          | Siacoin     | 0.0165401 |
    |  36 | 2018-05-24 18:00:01 | ZEC         | Zcash       | 289.686   |
    |  37 | 2018-05-24 18:31:01 | BTC         | Bitcoin     | 7574.54   |
    |  38 | 2018-05-24 18:31:01 | ETH         | Ethereum    | 590.538   |
    |  39 | 2018-05-24 18:31:01 | BTM         | Bytom       | 0.620313  |
    |  40 | 2018-05-24 18:31:01 | DCR         | Decred      | 103.306   |
    |  41 | 2018-05-24 18:31:01 | SC          | Siacoin     | 0.0164874 |
    |  42 | 2018-05-24 18:31:01 | ZEC         | Zcash       | 291.421   |
    |  43 | 2018-05-24 19:00:02 | BTC         | Bitcoin     | 7600.11   |
    |  44 | 2018-05-24 19:00:02 | ETH         | Ethereum    | 594.962   |
    |  45 | 2018-05-24 19:00:02 | BTM         | Bytom       | 0.622085  |
    |  46 | 2018-05-24 19:00:02 | DCR         | Decred      | 103.229   |
    |  47 | 2018-05-24 19:00:02 | SC          | Siacoin     | 0.0166324 |
    |  48 | 2018-05-24 19:00:02 | ZEC         | Zcash       | 296.651   |
    |  49 | 2018-05-24 19:31:01 | BTC         | Bitcoin     | 7599.6    |
    |  50 | 2018-05-24 19:31:01 | ETH         | Ethereum    | 593.13    |
    |  51 | 2018-05-24 19:31:01 | BTM         | Bytom       | 0.620733  |
    |  52 | 2018-05-24 19:31:01 | DCR         | Decred      | 101.368   |
    |  53 | 2018-05-24 19:31:01 | SC          | Siacoin     | 0.0165951 |
    |  54 | 2018-05-24 19:31:01 | ZEC         | Zcash       | 292.538   |
    |  55 | 2018-05-24 20:00:01 | BTC         | Bitcoin     | 7603.72   |

また、「プロダクション」と呼ばれる、X分ごとにいくつかのウォレットを監視するもう1つのウォレット

+-----+----------+---------------------+--------------+
| id  | currency | date_hour           | bal_conf     |
+-----+----------+---------------------+--------------+
|   1 | CMM      | 2018-05-25 08:00:00 | 24.87        |
| 197 | ETH      | 2018-05-24 10:56:53 | 0.82431267   |
| 198 | SIA      | 2018-05-24 10:56:54 | 0.77711006   |
| 199 | BTM      | 2018-05-24 10:58:02 | 81.33782690  |
| 201 | SIA      | 2018-05-24 11:00:23 | 0.77711006   |
| 202 | ETH      | 2018-05-24 11:30:15 | 0.83045272   |
| 203 | SIA      | 2018-05-24 11:30:16 | 0.77711006   |
| 204 | ETH      | 2018-05-24 12:00:23 | 0.83441691   |
| 205 | SIA      | 2018-05-24 12:00:23 | 0.77711006   |
| 206 | ETH      | 2018-05-24 12:30:18 | 0.84046355   |
| 207 | SIA      | 2018-05-24 12:30:31 | 0.77711006   |
| 208 | ETH      | 2018-05-24 13:00:14 | 0.84323421   |
| 209 | SIA      | 2018-05-24 13:00:18 | 0.77711006   |
| 211 | SIA      | 2018-05-24 13:30:18 | 0.77711006   |
| 212 | BTM      | 2018-05-24 13:58:05 | 85.18699829  |
| 213 | BTM      | 2018-05-24 14:00:01 | 85.18699829  |
| 215 | SIA      | 2018-05-24 14:00:19 | 0.77711006   |
| 216 | BTM      | 2018-05-24 14:02:02 | 85.18699829  |
| 217 | ETH      | 2018-05-24 14:02:45 | 0.85096095   |
| 218 | SIA      | 2018-05-24 14:02:54 | 0.77711006   |
| 219 | ETH      | 2018-05-24 14:03:32 | 0.85096095   |
| 220 | SIA      | 2018-05-24 14:03:36 | 0.77711006   |
| 221 | BTM      | 2018-05-24 14:04:01 | 85.18699829  |
| 222 | BTM      | 2018-05-24 14:06:01 | 85.41043305  |
| 223 | ETH      | 2018-05-24 14:09:55 | 0.85096095   |
| 224 | SIA      | 2018-05-24 14:10:17 | 0.77711006   |
| 225 | BTM      | 2018-05-24 14:30:01 | 85.75066324  |
| 226 | ETH      | 2018-05-24 14:30:10 | 0.85363614   |
| 227 | SIA      | 2018-05-24 14:30:13 | 0.77711006   |
| 228 | BTM      | 2018-05-24 15:00:01 | 86.67740965  |
| 229 | ETH      | 2018-05-24 15:00:12 | 0.85694953   |
| 230 | SIA      | 2018-05-24 15:00:14 | 0.77711006   |
| 231 | BTM      | 2018-05-24 15:30:01 | 87.02525693  |
| 233 | SIA      | 2018-05-24 15:30:18 | 0.77711006   |
| 234 | ETH      | 2018-05-24 15:34:33 | 0.86264950   |
| 235 | SIA      | 2018-05-24 15:34:40 | 0.77711006   |
| 236 | ETH      | 2018-05-24 15:38:30 | 0.86264950   |
| 237 | SIA      | 2018-05-24 15:38:31 | 0.77711006   |
| 239 | SIA      | 2018-05-24 15:40:15 | 0.77711006   |
| 240 | BTM      | 2018-05-24 15:44:01 | 87.49497772  |
| 241 | BTC      | 2018-05-24 15:48:37 | 0.03155398   |
| 243 | SIA      | 2018-05-24 15:50:20 | 0.77711006   |
| 244 | BTC      | 2018-05-24 15:51:22 | 0.03155398   |
| 245 | BTM      | 2018-05-24 15:55:01 | 87.67270990  |
| 246 | BTC      | 2018-05-24 15:57:01 | 0.03155398   |
| 247 | BTC      | 2018-05-24 16:00:02 | 0.03155398   |
| 248 | BTM      | 2018-05-24 16:00:01 | 87.67270990  |
| 250 | SIA      | 2018-05-24 16:00:21 | 0.77711006   |
| 251 | ETH      | 2018-05-24 16:10:15 | 0.86810400   |
| 252 | SIA      | 2018-05-24 16:10:19 | 0.77711006   |
| 253 | BTM      | 2018-05-24 16:11:01 | 88.15004778  |
| 254 | BTC      | 2018-05-24 16:19:01 | 0.03155398   |
| 256 | SIA      | 2018-05-24 16:20:19 | 0.77711006   |
| 257 | BTM      | 2018-05-24 16:22:01 | 88.15004778  |

productionテーブルのdate_hourごとに、対応するcrypto_code(production.currency = crypto.currency)に最も近いprice_usdの値(タイミングに関して最も近い)を見つける必要があります。

私はこのコードを試しました:

SELECT t.date_hour,t.currency,t.bal_conf, s.price_usd
FROM production t
INNER JOIN crypto s
 ON(t.date_hour like s.date_hour and s.crypto_code=t.currency)

しかし、すべてのプロダクションレコードが返されるわけではないため、暗号テーブルに正確に一致する日付がない場合、最も近い日付を見つける必要があります。

回答 1 件
  • 私はそれをサブクエリで行い、 price_usd を取得します   crypto から  通貨コードが一致し、タイムスタンプの差の絶対値が通貨コードのタイムスタンプの差の最小値に等しい場合。別のサブクエリでその最小値を取得します。

    SELECT p.date_hour,
           p.currency,
           p.bal_conf,
           (SELECT c.price_usd
                   FROM crypto c
                   WHERE c.crypto_code = p.currency
                         AND (SELECT min(abs(timestampdiff(second, ci.date_hour, p.date_hour)))
                                     FROM crypto ci
                                     WHERE ci.crypto_code = p.currency)
                             = abs(timestampdiff(second, c.date_hour, p.date_hour))
                   ORDER BY c.date_hour DESC
                   LIMIT 1) price_usd
           FROM production p;
    
    

    ザ・ ORDER BY c.date_hour DESC LIMIT 1   crypto からの2つ以上のタイムスタンプがある場合に、まれなイベントを処理するだけです。  指定されたタイムスタンプに対して、本番と同じ差を生成します。そのような場合、最新のタイムスタンプが使用されます。

    このクエリをサポートするインデックスは

    CREATE INDEX crypto_crypto_code_date_hour
                 ON crypto
                    (crypto_code,
                     date_hour);
    
    

    最も内側のサブクエリと

    CREATE INDEX crypto_crypto_code_date_hour_price_usd
                 ON crypto
                    (crypto_code,
                     date_hour,
                     price_usd);
    
    

    最も外側のサブクエリ用。 (また、 production の条件がないため、外部クエリにはありません )

あなたの答え