我将一些日内库存数据保存到MySQL表中,如下所示:
+----------+-------+
| 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值.我设法做到这一点的唯一方法是使用游标,由于数据量很大,游标很慢.我基本上是在搜索这样的东西:
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
当然这个查询不起作用,但它应该是这样的.
如果没有游标和临时表,我会很感激如何解决这个问题.
解决方法:
这应该工作:
SET @prev = NULL;
UPDATE ticks
SET quote= @prev := coalesce(quote, @prev)
ORDER BY tick;
BTW同样的技巧适用于阅读:
SELECT t.tick, @prev := coalesce(t.quote, @prev)
FROM ticks t
JOIN (SELECT @prev:=NULL) as x -- initializes @prev
ORDER BY tick
标签:mysql
来源: https://codeday.me/bug/20190621/1253315.html