php – 删除重复项并在一个语句中更新一个唯一的左侧？

2023-07-23 11:41:16

CREATE TABLE hostname_table
(
id INT NOT NULL AUTO_INCREMENT,
hostname CHAR(65) NOT NULL,
interval_avg INT,
last_update DATETIME NOT NULL,
numb_updates INT,
PRIMARY KEY (id)
)

我有这个表,我将500-600k行数据导入其中.在写入数据库时,我不检查重复项,因为我想知道每个主机有多少重复项,并且我还想知道每个主机名更新之间的间隔.

hostname_table中的示例值：

id  hostname          interval_avg  last_update          numb_updates
1   www.host.com      60            2012-04-25 20:22:21  1
2   www.hostname.com  10            2012-04-25 20:22:21  5
3   www.name.com      NULL          2012-04-25 20:22:21  NULL
4   www.host.com      NULL          2012-04-25 20:22:26  NULL
5   www.host.com      NULL          2012-04-25 20:22:36  NULL

我清理它时我想要它的样子：

id  hostname          interval_avg  last_update          numb_updates
1   www.host.com      25            2012-04-25 20:22:36  3
2   www.hostname.com  10            2012-04-25 20:22:21  5
3   www.name.com      NULL          2012-04-25 20:22:21  NULL

有了这样一个庞大的数据库,我不想发送太多的查询来获得这个目标,但我相信3个查询是这样的操作的最小值(如果我错了,请纠正我).每小时将有大约50万个新行,其中~50％或更多将是重复的,因此尽可能有效地去除那些重复项是至关重要的,同时仍然记录重复发生的次数和频率(hense the interval_avg和numb_update更新).

这是一个三步问题,我希望这里的社区能伸出援助之手.

所以总结一下伪代码,我需要帮助优化这些查询;

>选择所有last_update和interval_avg值,获取sum(numb_update),获取计数(重复)foreach主机名,
>以min(id)更新interval_avg,以min(id)更新numb_updates,使用max(id)中的值更新min(id)中的last_update,
>删除除min(id)以外的所有重复项

SOLVED.
在一两天的研究过程中,我优化了一部分94％,另一部分优化了~97％.我真的希望这有助于其他人寻找相同的解决方案.如果选择错误的解决方案,mySQL和大型数据库可能是一个大问题.
(我将last_update列从DATETIME更改为INT(10),然后我从格式化时间更改为时间戳作为最终解决方案中的值,以便能够获取max(last_update)和min(last_update)值

(感谢GolezTrol帮助解决部分问题)

解决方法:

如果要按主机名聚合,则无法为主机名获取interval_avg和numb_updates的每个不同值.你的意思是SUM还是AVG呢？或者你只想保持最低身份证的价值？

在下面的查询中,我总结了它们.

SELECT 
  MIN(id) as id, 
  hostname, 
  SUM(interval_avg) as total_interval_avg,
  SUM(numb_updates) as total_numb_updates,
  COUNT(*) as hostname_count
FROM
  hostname_table
GROUP BY 
  hostname

在此之后,您将需要使用interval_avg和numb_updates的正确值更新每个找到的id.

之后,您将需要删除此查询找不到的每个ID.

DELETE FROM hostname_table
WHERE
  id NOT IN
    (SELECT 
      MIN(id)
    FROM
      hostname_table
    GROUP BY 
      hostname)

码农公寓

相关文章