哪些SQL查询将允许我在特定时间间隔选择记录?

我有以下数据:

  • X,Y,接收器,时间戳
  • 1.534268,51.03796,126,2017-06-01 02:01:01
  • 1.534268,51.03793,126,2017-06-01 02:03:01
  • 1.534268,51.03799,133,2017-06-01 02:05:01
  • 1.534268,51.03716,133,2017-06-01 02:09:01
  • 1.534268,51.03766,126,2017-06-01 02:10:01
  • 1.534268,51.03766,126,2017-06-01 02:17:01
  • 1.534268,51.03795,126,2017-06-01 02:19:01
  • 1.534268,51.03791,133,2017-06-01 02:22:01
  • 1.534268,51.03746,126,2017-06-01 02:24:01
  • 1.534268,51.03796,133,2017-06-01 02:31:01

我需要SQL查询来做两件事:

  1. 以大约10分钟的间隔选择记录,所以最终得到这样的结果:
  2. X,Y,接收器,时间戳
  3. 1.534268,51.03796,126,2017-06-01 02:01:01
  4. 1.534268,51.03766,126,2017-06-01 02:10:01
  5. 1.534268,51.03795,126,2017-06-01 02:19:01
  6. 1.534268,51.03796,133,2017-06-01 02:31:01

然后,我将如何修改查询以在15分钟,30分钟和每小时的时间间隔选择数据?

  1. 从上面的结果中,我将如何计算每个接收者下的记录数量,即
  2. 接收器126:3,
  3. 接收器133:1
评论
  • 硕向槐
    硕向槐 回复

    “像这样的东西”。好吧,最简单的方法是每十分钟从“日历时钟”中获取第一条记录。那将是00:00、00:10、00:20等之后的第一条记录。

    For this, you can use first_value() and select distinct:

    select distinct
           first_value(x) over (partition by dd, hh, mm order by timestamp) as x,
           first_value(y) over (partition by dd, hh, mm order by timestamp) as y,
           first_value(receiver) over (partition by dd, hh, mm order by timestamp) as receiver,
           min(timestamp) over (partition by dd, hh, mm),
           count(*) over (partition by dd, hh, mm) as cnt
    
    from t cross apply
         (values (convert(date, timestamp), datepart(hour, timestamp), datepart(minute, timestamp) / 6)
         ) v(dd, hh, mm);
    

    这不会完全返回您指定的结果,但似乎符合问题中提到的条件。