插值MySQL表中的缺失值 [英] Interpolate missing values in a MySQL table

问题描述

我将一些当日股票数据保存到一个如下所示的MySQL表中:

I have some intraday stock data saved into a MySQL table which looks like this:

+----------+-------+
| tick     | quote |
+----------+-------+
| 08:00:10 |  5778 |
| 08:00:11 |  5776 |
| 08:00:12 |  5778 |
| 08:00:13 |  5778 |
| 08:00:14 |  NULL |
| 08:00:15 |  NULL |
| 08:00:16 |  5779 |
| 08:00:17 |  5778 |
| 08:00:18 |  5780 |
| 08:00:19 |  NULL |
| 08:00:20 |  5781 |
| 08:00:21 |  5779 |
| 08:00:22 |  5779 |
| 08:00:23 |  5779 |
| 08:00:24 |  5778 |
| 08:00:25 |  5779 |
| 08:00:26 |  5777 |
| 08:00:27 |  NULL |
| 08:00:28 |  NULL |
| 08:00:29 |  5776 |
+----------+-------+

如您所见，有些地方没有可用数据(引号为NULL).我想做的是一个简单的步骤插值.这意味着每个NULL值都应使用最新的可用值进行更新.我设法做到这一点的唯一方法是使用游标，由于数据量很大，它非常慢.我基本上是在搜索类似这样的内容:

As you can see, there are some points where no data is available (quote is NULL). What I would like to do is a simple step interpolation. This means each NULL value should be updated with the last value available. The only way I managed to do this is with cursors, which is pretty slow due to the large amount of data. I'm basically searching something like this:

UPDATE table AS t1
SET quote = (SELECT quote FROM table AS t2 
             WHERE t2.tick < t1.tick AND 
                   t2.quote IS NOT NULL 
             ORDER BY t2.tick DESC 
             LIMIT 1)
WHERE quote IS NULL

当然，此查询将不起作用，但这就是它的外观.

Of course this query will not work, but this is how it should look like.

对于不使用游标和临时表如何解决此问题的任何想法，我将不胜感激.

I would appreciate any ideas on how this can be solved without cursors and temp tables.

推荐答案

这应该有效:

SET @prev = NULL;

UPDATE   ticks
SET      quote= @prev := coalesce(quote, @prev)
ORDER BY tick;

顺便说一句，同样的技巧也可以用于阅读:

BTW the same trick works for reading:

SELECT   t.tick, @prev := coalesce(t.quote, @prev)
FROM     ticks t
JOIN     (SELECT @prev:=NULL) as x -- initializes @prev
ORDER BY tick

这篇关于插值MySQL表中的缺失值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！