mysql - 数据库查询，怎样按一定时间间隔跳跃查询数据？

Question

我的项目用的是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语句？谢谢各位！

Answer 1

To check the rank ranking, just divide the rank by 4 and the remainder is the same

Answer 2

The author has changed his mind. It is drawing anyway, and it is not necessary to check the data according to the time interval;

--MySQL approach:
select @rank:=0;
select * from(select *,@rank:=@rank+1 as rank from your table name) as dd where dd.rank% interval parameter=0;

Answer 3

@Chobits’s answer. I originally wanted to comment below, but after thinking about it I decided to write it down so that the question owner can see it.

  select * from (select rank() over(order by HTAH01A060) as rank_sort,* from table)
    as a where a.rank_sort%4 = 0

If it cannot be guaranteed that HTAH01A060 is different, it is recommended to use row_number()

Answer 4

Hello everyone, I found a method last night that basically meets my needs. Welcome everyone to continue the discussion~~Let’s see if there is a better way~~

/*存储过程：按起始、终止时间，以及时间间隔，抽取数据*/
DELIMITER $$
DROP PROCEDURE IF EXISTS proc_HTAH01A_sampleData $$
CREATE DEFINER=`root`@`localhost` PROCEDURE proc_HTAH01A_sampleData(IN timeBegin DATETIME, IN timeEnd DATETIME, IN timeInterval INT)
    BEGIN
        SET @time := '1000-01-01 00:00:00';
        SET @interval := timeInterval;
        SELECT * FROM HTAH01A
        WHERE HTAH01A060 >= timeBegin AND HTAH01A060 <= timeEnd
        AND TIMESTAMPDIFF( SECOND, @time, HTAH01A060 ) >= @interval
        AND @time := htah01a060;
    END$$
DELIMITER ;

The call is as follows:

call proc_HTAH01A_sampleData('2014-08-10 23:20:00', '2014-08-11 05:00:00', 120);