gpt4 book ai didi

MySQL:在带时间戳的行之间移动数据

转载 作者:行者123 更新时间:2023-11-29 14:10:07 24 4
gpt4 key购买 nike

我有一个如下所示的表格:

mysql> explain test_table;
+---------------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+-------------+------+-----+---------+-------+
| timestamp | datetime | NO | PRI | NULL | |
| id | varchar(64) | NO | PRI | NULL | |
| px_last | float | NO | | NULL | |
| twap | float | YES | | NULL | |
+---------------------+-------------+------+-----+---------+-------+

对于给定的符号和时间戳,我想将字段 twap 的值向后移动一个时间步长。

此表:

mysql> select * from test_table;
+---------------------+-------+---------+-------+
| timestamp | id | px_last | twap |
+---------------------+-------+---------+-------+
| 2011-01-01 00:00:00 | apple | 101 | 101.1 |
| 2011-01-01 00:00:00 | pear | 50 | 50.1 |
| 2011-01-02 00:00:00 | apple | 102 | 112.8 |
| 2011-01-02 00:00:00 | pear | 51 | 57.3 |
| 2011-01-03 00:00:00 | pear | 52 | 59.1 |
| 2011-01-03 00:00:00 | apple | 103 | 104.1 |
+---------------------+-------+---------+-------+

最终应该是这样的:

mysql> select * from test_table;
+---------------------+-------+---------+-------+
| timestamp | id | px_last | twap |
+---------------------+-------+---------+-------+
| 2011-01-01 00:00:00 | apple | 101 | 112.8 |
| 2011-01-01 00:00:00 | pear | 50 | 57.3 |
| 2011-01-02 00:00:00 | apple | 102 | 104.1 |
| 2011-01-02 00:00:00 | pear | 51 | 59.1 |
| 2011-01-03 00:00:00 | pear | 52 | NULL |
| 2011-01-03 00:00:00 | apple | 103 | NULL |
+---------------------+-------+---------+-------+

我的第一种方法(使用此示例: How to number rows... )是在每个符号内创建一个行号 (1, 2....n),然后将行号、符号和 twap 复制到临时表,减少临时表中每行行号加1,然后将数据复制回原表。

有没有办法在不创建临时表的情况下做到这一点?该表很大,超过 5000 万行并且还在不断增长,因此查找小于给定时间戳的最大时间戳不够高效。

最佳答案

你可以写:

UPDATE test_table tt1
SET tt1.twap =
( SELECT tt2.twap
FROM ( SELECT timestamp,
id,
twap
FROM test_table
) tt2
WHERE tt2.timestamp = DATE_ADD(tt1.timestamp, INTERVAL 1 DAY)
AND tt2.id = tt1.id
)
;

从技术上讲,这确实创建了一个临时表(作为可怕的 ERROR 1093 (HY000): You can't specify target table 'tt1' for update in FROM clause 的解决方法),但至少它都是由该内部子查询隐式处理的,而不是要求您创建一个临时表作为一个单独的步骤。

对于一个大表,我不希望它表现得很好,但是您可以通过一次仅处理一个时间范围将其分解为较小的更新(前提是您从最早的时间范围开始并向前移动,这样您就永远不会覆盖您将要使用的数据)。您的个人陈述将如下所示:

UPDATE test_table tt1
SET tt1.twap =
( SELECT tt2.twap
FROM ( SELECT timestamp,
id,
twap
FROM test_table
WHERE timestamp BETWEEN TIMESTAMP '2011-01-02 00:00:00'
AND TIMESTAMP '2011-02-01 23:59:59'
) tt2
WHERE tt2.timestamp = DATE_ADD(tt1.timestamp, INTERVAL 1 DAY)
AND tt2.id = tt1.id
)
WHERE tt1.timestamp BETWEEN TIMESTAMP '2011-01-01 00:00:00'
AND TIMESTAMP '2011-01-31 23:59:59'
;

关于MySQL:在带时间戳的行之间移动数据,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/13633479/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com