MySQL自定义时间间隔抽稀

本文主要是介绍MySQL自定义时间间隔抽稀，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

表设计

create table monitor
(tid       varchar(255)   not null,save_date datetime       not null,tlevel    decimal(10, 2) null,primary key (tid, save_date)
);

数据如下

按分钟抽稀

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 ) AS seqFROM (SELECT tid,save_date,tlevel,FLOOR(UNIX_TIMESTAMP(save_date) / (60 * 1)) AS gidFROM monitorORDER BY tid,save_date) t1ORDER BY t1.tid,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 ) AS seqFROM (SELECT tid,save_date,tlevel,FLOOR(UNIX_TIMESTAMP(save_date) / (60 * 60 * 1)) AS gidFROM monitorORDER BY tid,save_date) t1ORDER BY t1.tid,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 ) AS seqFROM (SELECT tid,save_date,tlevel,FLOOR(UNIX_TIMESTAMP(save_date) / (60 * 60 * 24 * 1)) AS gidFROM monitorORDER BY tid,save_date) t1ORDER BY t1.tid,t1.save_date) t2
WHERE t2.seq = 1

这篇关于MySQL自定义时间间隔抽稀的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---