表设计
create table monitor
(
tid varchar(255) not null,
save_date datetime not null,
tlevel decimal(10, 2) null,
primary key (tid, save_date)
);
数据如下
![在这里插入图片描述](https://i-blog.csdnimg.cn/blog_migrate/8e515cfae11a4402630163edfc592a13.png)
按分钟抽稀
SELECT t2.tid,
t2.save_date,
t2.tlevel,
t2.gid,
t2.seq
FROM (SELECT t1.tid,
t1.save_date,
t1.tlevel,
t1.gid,
ROW_NUMBER() over ( PARTITION BY t1.tid, t1.gid ORDER BY t1.tid, t1.gid, t1.save_date ) AS seq
FROM (SELECT tid,
save_date,
tlevel,
FLOOR(UNIX_TIMESTAMP(save_date) / (60 * 1)) AS gid
FROM monitor
ORDER BY tid,
save_date) t1
ORDER BY t1.tid,,
t1.gid
t1.save_date) t2
WHERE t2.seq = 1
按小时抽稀
SELECT t2.tid,
t2.save_date,
t2.tlevel,
t2.gid,
t2.seq
FROM (SELECT t1.tid,
t1.save_date,
t1.tlevel,
t1.gid,
ROW_NUMBER() over ( PARTITION BY t1.tid, t1.gid ORDER BY t1.tid, t1.gid, t1.save_date ) AS seq
FROM (SELECT tid,
save_date,
tlevel,
FLOOR(UNIX_TIMESTAMP(save_date) / (60 * 60 * 1)) AS gid
FROM monitor
ORDER BY tid,
save_date) t1
ORDER BY t1.tid,,
t1.gid
t1.save_date) t2
WHERE t2.seq = 1
按天抽稀
SELECT t2.tid,
t2.save_date,
t2.tlevel,
t2.gid,
t2.seq
FROM (SELECT t1.tid,
t1.save_date,
t1.tlevel,
t1.gid,
ROW_NUMBER() over ( PARTITION BY t1.tid, t1.gid ORDER BY t1.tid, t1.gid, t1.save_date ) AS seq
FROM (SELECT tid,
save_date,
tlevel,
FLOOR(UNIX_TIMESTAMP(save_date) / (60 * 60 * 24 * 1)) AS gid
FROM monitor
ORDER BY tid,
save_date) t1
ORDER BY t1.tid,
t1.gid
t1.save_date) t2
WHERE t2.seq = 1