MySQL自定义时间间隔抽稀

壹龙

已于 2024-01-05 09:41:45 修改

阅读量623

点赞数 5

分类专栏： mysql 文章标签： mysql 数据库

于 2024-01-04 21:35:22 首次发布

本文链接：https://blog.csdn.net/elong490/article/details/135396556

版权

mysql 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

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, 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