代码之家 › 专栏 › 技术社区 › freddy888

MYSQL替换表的一部分

mysql

freddy888 · 技术社区 · 6 年前

我有以下数据增长太快太大:

c1    c2       timestamp
5      1       2019-01-31 15:35:36.055000
6      2       2019-01-31 15:35:36.055000
7      3       2019-01-31 15:35:35.055000
8      4       2019-01-31 15:35:35.055000
7      3       2019-01-30 15:35:36.055000
8      4       2019-01-30 15:35:36.055000
7      3       2019-01-30 15:35:35.055000
8      4       2019-01-30 15:35:35.055000

我想通过将时间戳设置为10秒间隔来替换表的一部分,其中时间戳早于2019-01-31 00:00:00:000000 结果应该是这样的,并且上述表格中底部的两列不应该在新表格中:

c1    c2       timestamp
5      1       2019-01-31 15:35:36.055000
6      2       2019-01-31 15:35:36.055000
7      3       2019-01-31 15:35:35.055000
8      4       2019-01-31 15:35:35.055000
7      3       2019-01-30 15:35:40.000000
8      4       2019-01-30 15:35:40.000000

我做了下面的查询来获取时间戳,但我不知道如何替换表中的特定部分。

SELECT c1, c2, FROM_UNIXTIME(CEIL(UNIX_TIMESTAMP(timestamp) / 10) * 10) as datetime 
FROM mytable
WHERE timestamp in (SELECT MAX(timestamp) FROM mytable
GROUP BY FROM_UNIXTIME(CEIL(UNIX_TIMESTAMP(timestamp) / 10) * 10)) 
GROUP BY datetime

2 回复 | 直到 6 年前

Uueerdo 6 年前

(注意:如果c1或c2可以为空,则如果不进行重大修改,此解决方案可能不适用于您。)

DELETE 
FROM mytable
WHERE timestamp < '2019-01-31 00:00:00:000000'
    AND (c1, c2, timestamp) 
        NOT IN (
           SELECT ends.c1, ends.c2, ends.windowEnd
           FROM (  SELECT c1, c2
                      , FROM_UNIXTIME(CEIL(UNIX_TIMESTAMP(timestamp) / 10) * 10) as dtWindow
                      , MAX(timestamp) AS windowEnd
                   FROM mytable
                   WHERE timestamp < '2019-01-31 00:00:00:000000'
                   GROUP BY c1, c2, dtWindow
                ) AS ends
        )
;

注意:有时MySQL对于在同一查询中从要删除的表中进行选择有点挑剔,但我认为该子查询中有足够多的间接寻址级别,因此不应该成为问题。

额外好处:如果您想在运行此类查询之前仔细检查将被删除的记录,可以将子选择移动到加入选择中以预览内容。

SELECT t.*
, CASE WHEN ends.rowPresent IS NOT NULL THEN 'keep' ELSE 'to delete' END AS `plan`
FROM mytable AS t 
LEFT JOIN (
   SELECT c1, c2
      , FROM_UNIXTIME(CEIL(UNIX_TIMESTAMP(timestamp) / 10) * 10) as dtWindow
      , MAX(timestamp) AS windowEnd
      , 1 AS rowPresent -- You could just use c1 or c2 in the above CASE, but if they could naturally be null that would complicate things
   FROM mytable
   WHERE timestamp < '2019-01-31 00:00:00:000000'
   GROUP BY c1, c2, dtWindow
) AS ends ON t.c1 = ends.c1 AND t.c2 = ends.c2 AND t.timestamp = ends.windowEnd
WHERE t.timestamp < '2019-01-31 00:00:00:000000'
;

Santi 4 年前

这样的查询将接收所有行的时间戳值

UPDATE mytable SET timestamp=FROM_UNIXTIME(CEIL(UNIX_TIMESTAMP(timestamp) / 10) * 10);

如果只想将WHERE子句应用于某些行,请添加WHERE子句