sqlserver——按时间间隔对数据采样查询

yicai168

于 2019-10-25 09:57:39 发布

阅读量3.3k

点赞数

分类专栏： SQL Server

本文链接：https://blog.csdn.net/yicai168/article/details/102737380

版权

SQL Server 专栏收录该内容

9 篇文章

订阅专栏

原数据为每10秒写入一条新数据

查询历史记录时要求每间隔10分钟取一个样点，查过去10小时的数据

SQL语句：

declare @endDate datetime
declare @startDate datetime
set @endDate=(select max([monitorTime]) from  table_x  where columnA='条件')
set @startDate=DATEADD(minute,-600,@endDate)

select 
       [uuid]
      ,[columnA]
      , convert(varchar(16),monitorTime,120) as monitorTime
 from table_x a where columnA='条件' and  monitorTime>=@startDate and  datediff(MINUTE,monitorTime,@endDate)%10=0
 and not exists
 (
 select [uuid] from table_x b where columnA='条件' and  monitorTime>=@startDate and  datediff(MINUTE,monitorTime,@endDate)%10=0 
 and convert(varchar(16),a.monitorTime,120) = convert(varchar(16),b.monitorTime,120) 
 and a.monitorTime < b.monitorTime
 )
 order by monitorTime desc