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 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.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 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.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 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.save_date) t2
WHERE t2.seq = 1