移动整个范围的键

发布于 2024-12-07 01:15:41 字数 543 浏览 1 评论 0原文

我正在处理时间序列数据，其关键列是时间戳：时间。每行还有许多“值”列。

我即将将整个数据范围移动几个小时（由于夏令时问题）。为此，我将更新几行的键，这可能会导致一些重复的键。我希望忽略日期范围边缘的重复键。我希望移动的范围覆盖旧的范围。

我计划做类似的事情：

UPDATE IGNORE time_series_table 
SET time=time-<some_shift> 
WHERE <time in a date-range>

这是时间键的 describe 的输出：

Field     Type      Null Key     Default Extra
TimeMeas  datetime  NO   PRI     NULL

我的问题是：它会立即移动所有键，还是会尝试移动每一行一个接一个，导致在移动范围本身内出现大量重复键？

您有更好的方法吗？提前致谢

原文

I am working on time series data, for which the key column is a timestamp : Time.
There are also many "value" columns for each row.

I am about to shift a whole range of my data by several hours (due to a daylight saving time issue).
For that, I will update the key of several rows, and it might result in some duplicate keys. I would like the duplicate keys on the edge of the date range to be ignore. I want the shifted range to override the old one.

I plan to do something like :

UPDATE IGNORE time_series_table 
SET time=time-<some_shift> 
WHERE <time in a date-range>

Here is the output of describe <table> for the time key :

Field     Type      Null Key     Default Extra
TimeMeas  datetime  NO   PRI     NULL

My question is : Will it shift all the keys at once, or will it try to shift each row one by one, resulting in massive duplicate keys wihthin the shifted range itself ?

Do you have a better way of doing this in mind ?
Thanks in advance

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

如梦初醒的夏天 2024-12-14 01:15:41

它会立即移动所有键，还是会尝试逐行移动

它会立即移动所有键。

导致移动范围内出现大量重复键？

如果任何主键重复，它就会失败。
使用updateignore，它只是默默地跳过。

解决这个问题的

/* create a temporary table to store matches records*/
create table tmp_table select time-<some_shift>, etc_cols....
from time_series_table 
where <time in a date-range>;

这是我最后

/* delete the matches in the original table */
delete from time_series_table where <time in a date-range>;
delete from time_series_table where <time in a date-range - some_shift>;

方法

/* at this point, there won't be any duplicate data */
/* so, insert back into original table */
insert into time_series_table select * from tmp_table;
optmize table time_series_table;

Will it shift all the keys at once, or will it try to shift each row one by one

It will shift all the keys at once.

resulting in massive duplicate keys wihthin the shifted range itself ?

It just failed if any of primary key is duplicated.
With update ignore, it just skip silently.

This is my approach to fix this

/* create a temporary table to store matches records*/
create table tmp_table select time-<some_shift>, etc_cols....
from time_series_table 
where <time in a date-range>;

then

/* delete the matches in the original table */
delete from time_series_table where <time in a date-range>;
delete from time_series_table where <time in a date-range - some_shift>;

finally

/* at this point, there won't be any duplicate data */
/* so, insert back into original table */
insert into time_series_table select * from tmp_table;
optmize table time_series_table;

回复收藏 0 原文

~没有更多了~