怎样按一定时间间隔跳跃查询数据?-问答-阿里云开发者社区-阿里云

开发者社区> 问答> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

怎样按一定时间间隔跳跃查询数据?

2016-02-11 23:00:10 2836 1

我的项目用的是MySQL,但也想同时请教下在Oracle、SqlServer中应该如何处理如下问题:

有数据表如下所示:
希望从表中抽取数据,要求两条记录的时间间隔至少2分钟
对下面图片中的数据而言,假如我查询的时间范围是从2014-08-10 23:20:00开始的,
则希望抽取到如下结果集:
'83', '57', '10041', '74.27', '0', '2014-08-10 23:20:04'
'113', '57', '10041', '59.25', '0', '2014-08-10 23:22:06'
'145', '57', '10041', '96.21', '0', '2014-08-10 23:24:07'
'177', '57', '10041', '34.16', '0', '2014-08-10 23:26:08'
'209', '57', '10041', '39.11', '0', '2014-08-10 23:28:09'
真实的场景是:传感器每隔30秒左右会向数据库里写一条记录,我要取N天的数据绘图,如果一次性查询N天的记录再进行抽稀的话,由于结果集太大,循环次数过多,导致时耗严重。我希望能通过sql语句直接在数据库层面进行数据抽稀,程序里要处理的数据就会少很多。

问题就是,应该如何写SQL语句?

取消 提交回答
全部回答(1)
  • 德哥
    2019-07-17 18:41:03

    对于PostgreSQL数据库来说,这个需求很简单,写个函数就可以搞定。
    例子:

    digoal=# create table test(id serial, crt_time timestamp);
    CREATE TABLE
    digoal=# insert into test (crt_time) select generate_series(now(),now()+interval '10 min', interval '30 sec');
    INSERT 0 21
    digoal=# select * from test;
     id |          crt_time          
    ----+----------------------------
      1 | 2016-04-12 10:25:08.696388
      2 | 2016-04-12 10:25:38.696388
      3 | 2016-04-12 10:26:08.696388
      4 | 2016-04-12 10:26:38.696388
      5 | 2016-04-12 10:27:08.696388
      6 | 2016-04-12 10:27:38.696388
      7 | 2016-04-12 10:28:08.696388
      8 | 2016-04-12 10:28:38.696388
      9 | 2016-04-12 10:29:08.696388
     10 | 2016-04-12 10:29:38.696388
     11 | 2016-04-12 10:30:08.696388
     12 | 2016-04-12 10:30:38.696388
     13 | 2016-04-12 10:31:08.696388
     14 | 2016-04-12 10:31:38.696388
     15 | 2016-04-12 10:32:08.696388
     16 | 2016-04-12 10:32:38.696388
     17 | 2016-04-12 10:33:08.696388
     18 | 2016-04-12 10:33:38.696388
     19 | 2016-04-12 10:34:08.696388
     20 | 2016-04-12 10:34:38.696388
     21 | 2016-04-12 10:35:08.696388
    (21 rows)
    
    create or replace function get_sparse_data(b timestamp, e timestamp, sparse interval, lmt int) returns setof test as 
    $$
    
    declare
      res test;
      rec test;
      cn int := 0;
    begin
      for rec in select * from test where crt_time between b and e order by crt_time loop
        if res is null or rec.crt_time - res.crt_time >= sparse then
          res := rec;
          cn := cn+1;
          return next res;
        end if;
    
        if cn >= lmt then
          return;
        end if;
      end loop;
    end;
    
    $$
     language plpgsql;
    
    digoal=# select get_sparse_data('2016-04-12 10:26:38.696388', '2016-04-12 10:34:08.696388', '1 min', 5);
              get_sparse_data          
    -----------------------------------
     (4,"2016-04-12 10:26:38.696388")
     (6,"2016-04-12 10:27:38.696388")
     (8,"2016-04-12 10:28:38.696388")
     (10,"2016-04-12 10:29:38.696388")
     (12,"2016-04-12 10:30:38.696388")
    (5 rows)
    
    digoal=# select get_sparse_data('2016-04-12 10:26:38.696388', '2016-04-12 10:34:08.696388', '2 min', 5);
              get_sparse_data          
    -----------------------------------
     (4,"2016-04-12 10:26:38.696388")
     (8,"2016-04-12 10:28:38.696388")
     (12,"2016-04-12 10:30:38.696388")
     (16,"2016-04-12 10:32:38.696388")
    (4 rows)

    另外,您的场景非常像物联网,建议可以考虑使用PostgreSQL, 有几篇文章建议你可以看看:
    一个简单算法可以帮助物联网,金融 用户 节约98%的数据存储成本 (PostgreSQL,Greenplum帮你做到)
    https://yq.aliyun.com/articles/18042
    PostgreSQL 如何潇洒的处理每天上百TB的数据增量
    https://yq.aliyun.com/articles/8528
    PostgreSQL 1000亿数据量 正则匹配 速度与激情
    https://yq.aliyun.com/articles/7549
    PostgreSQL 百亿地理位置数据 近邻查询性能
    https://yq.aliyun.com/articles/2999
    PostgreSQL "物联网"应用 - 1 实时流式数据处理案例(万亿每天)
    https://yq.aliyun.com/articles/166

    0 0
相关问答

1

回答

能给图书管理模型数据添加怎样的专有函数?

2022-03-30 08:03:52 195浏览量 回答数 1

1

回答

Java中的完整的类名称是怎样的啊?

2022-04-03 23:13:16 507浏览量 回答数 1

1

回答

python可以通过什么方法来查看变量的数据类型呢?

2021-11-04 21:28:24 158浏览量 回答数 1

1

回答

jdbc中的字符大对象和二进制大对象数据类型分别是?

2021-10-26 21:31:30 511浏览量 回答数 1

1

回答

优化查询过程中的数据访问

2020-03-31 11:22:58 404浏览量 回答数 1

0

回答

greenplum中怎么记录慢查询日志哦?

2019-10-17 14:33:02 623浏览量 回答数 0

1

回答

怎样查看某个目录下文件的数量

2019-02-22 15:58:18 317浏览量 回答数 1

1

回答

查询本服务器的数据库账号和密码

2018-10-07 22:23:32 845浏览量 回答数 1

1

回答

可指定任意时间段查询网站流量统计数据的问题

2016-02-29 18:25:35 3500浏览量 回答数 1

2

回答

我购买的ecs服务器里的数据库信息怎样查找

2015-11-20 13:59:53 4534浏览量 回答数 2
+关注
蛮大人123
我说我不帅他们就打我,还说我虚伪
0
文章
7733
问答
问答排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载