MySQL一段时间内保留一条数据

原创已于 2022-12-02 15:05:35 修改 · 862 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #mysql #sql

于 2022-12-02 14:46:46 首次发布

本文介绍了一个MySQL中的实际案例，即如何通过SQL语句将大量重复数据进行聚合处理，特别是将同一时间段内相同传感器产生的数据合并为一条记录。通过创建模拟数据和使用GROUP BY子句，实现了将每分钟的数据汇总为每10分钟一次的有效解决方案。

最近一个同学遇到了这个问题： mysql中一个1.3亿条记录的表，为了降低条数，将同时段同一个传感器的记录只留一份，其他删除；

我自己比较好奇，然后自己写了一份模拟数据，共有100个device_id，一个device_id每分钟插入一条数据，向表中插入1w条数据；使用sql语句把原来1分钟一条的数据，改为10分钟一条；

1. 预置数据，该时间比较长

create database db1;
use db1;

create table db1.test(id int unsigned auto_increment primary key,  device_id int, create_time timestamp(3) DEFAULT CURRENT_TIMESTAMP(3) )engine=innodb;

drop procedure IF EXISTS idata;

delimiter ;;
  create procedure idata()
  begin
    declare i int;
	declare j int;
    set i=1;
	set @dt = now(); 
	while (i <=100) do
		set j=1;
		while(j<=100)do
		  insert into test (device_id, create_time) values(j, DATE_ADD(@dt, INTERVAL - i*60 SECOND));
		  set j=j+1;
		end while;
		set i=i+1;
	end while;
  end;;
delimiter ;
call idata();

2. 创建临时表，将10分钟一次的数据查询出来，插入到临时表

create database db2;
create table db2.temp_test like db1.test;

insert into db2.temp_test(id, device_id, create_time) (select id, device_id, create_time from db1.test group by device_id, floor(UNIX_TIMESTAMP(create_time) / 600 )) ;