求一时间范围查询语句

[刀枪blue]

数据demo

RecordDate per
2015-06-19 09:32:07.000 0.99412915851272
2015-06-19 09:30:58.000 0.990215264187867
2015-06-19 09:31:32.000 0.99412915851272
2015-06-19 10:30:10.000 0.992172211350294

我如果要取1小时间隔内per的值<=0.99的数据该怎么写？

[Tiger_Zhao]

什么叫做“8:00-20:00”“一小时间隔内符合<=0.99的数据”？
举例，什么结果？每一条符合什么逻辑？

[刀枪blue]

引用 8 楼 Tiger_Zhao 的回复:

什么叫做“8:00-20:00”“一小时间隔内符合<=0.99的数据”？ 举例，什么结果？每一条符合什么逻辑？

我的数据从8:00-20:00差不多有200W条 只要在一小时区间里数据都是<=0.99的 就是我要的数据 2015-06-19 09:32:07.000 0.89412915851272 2015-06-19 09:30:58.000 0.890215264187867 2015-06-19 09:31:32.000 0.89412915851272 2015-06-19 10:29:10.000 0.892172211350294 比如这四条 在9:30-10:30这一小时区间里都是<=0.99 就是我要的数据 但是比如我多了一条数据 2015-06-19 10:30:00.000 0.992172211350294 因为这一条>0.99 所以在9:30-10:30这一小时区间里就不是我要的数据了 每分钟里都有数据 8:00-9:00 8:01-9:01 这些都算是1小时区间 不知道听懂了没？

[Tiger_Zhao]

WITH /* 测试数据 demo(RecordDate,per) AS (
    SELECT '2015-06-19 08:32:07.000',0.89412915851272 UNION ALL
    SELECT '2015-06-19 08:32:08.000',0.89412915851272 UNION ALL
    SELECT '2015-06-19 09:30:58.000',0.890215264187867 UNION ALL
    SELECT '2015-06-19 09:31:32.000',0.89412915851272 UNION ALL
    SELECT '2015-06-19 09:32:07.000',0.99412915851272 UNION ALL
    SELECT '2015-06-19 10:29:10.000',0.892172211350294 UNION ALL
    SELECT '2015-06-19 10:32:07.000',0.892172211350294
), */
t AS ( -- 挑出有异常数据的时间，两端扩大1小时内为过滤区间
    SELECT CONVERT(datetime,null) RecordDate,
           CONVERT(datetime,null) limit1,
           CONVERT(datetime,'2015-06-19 07:59:59',120) limit2,
           0 rn
    UNION ALL
    SELECT RecordDate,
           DATEADD(second,-3599,RecordDate) limit1,
           DATEADD(second,3599,RecordDate) limit2,
           ROW_NUMBER() OVER(ORDER BY RecordDate) rn
      FROM demo
     WHERE RecordDate >= '2015-06-19 08:00'
       AND RecordDate < '2015-06-19 20:00'
       AND per > 0.99
)
,s AS ( -- 挑出过滤范围之外的区间
    SELECT t1.rn,
           t1.limit2 startDate,
           ISNULL(t2.limit1, CONVERT(datetime,'2015-06-19 20:00',120)) endDate
      FROM t t1
 LEFT JOIN t t2
        ON t1.rn + 1 = t2.rn
     WHERE t1.limit2 < ISNULL(t2.limit1, CONVERT(datetime,'2015-06-19 20:00',120))
)
--SELECT * FROM s
SELECT demo.*
  FROM s
  JOIN demo
    ON demo.RecordDate > s.startDate
   AND demo.RecordDate < s.endDate

RecordDate                               per
----------------------- --------------------
2015-06-19 08:32:07.000    0.894129158512720
2015-06-19 10:32:07.000    0.892172211350294

[刀枪blue]

引用 10 楼 Tiger_Zhao 的回复:

WITH /* 测试数据 demo(RecordDate,per) AS (
    SELECT '2015-06-19 08:32:07.000',0.89412915851272 UNION ALL
    SELECT '2015-06-19 08:32:08.000',0.89412915851272 UNION ALL
    SELECT '2015-06-19 09:30:58.000',0.890215264187867 UNION ALL
    SELECT '2015-06-19 09:31:32.000',0.89412915851272 UNION ALL
    SELECT '2015-06-19 09:32:07.000',0.99412915851272 UNION ALL
    SELECT '2015-06-19 10:29:10.000',0.892172211350294 UNION ALL
    SELECT '2015-06-19 10:32:07.000',0.892172211350294
), */
t AS ( -- 挑出有异常数据的时间，两端扩大1小时内为过滤区间
    SELECT CONVERT(datetime,null) RecordDate,
           CONVERT(datetime,null) limit1,
           CONVERT(datetime,'2015-06-19 07:59:59',120) limit2,
           0 rn
    UNION ALL
    SELECT RecordDate,
           DATEADD(second,-3599,RecordDate) limit1,
           DATEADD(second,3599,RecordDate) limit2,
           ROW_NUMBER() OVER(ORDER BY RecordDate) rn
      FROM demo
     WHERE RecordDate >= '2015-06-19 08:00'
       AND RecordDate < '2015-06-19 20:00'
       AND per > 0.99
)
,s AS ( -- 挑出过滤范围之外的区间
    SELECT t1.rn,
           t1.limit2 startDate,
           ISNULL(t2.limit1, CONVERT(datetime,'2015-06-19 20:00',120)) endDate
      FROM t t1
 LEFT JOIN t t2
        ON t1.rn + 1 = t2.rn
     WHERE t1.limit2 < ISNULL(t2.limit1, CONVERT(datetime,'2015-06-19 20:00',120))
)
--SELECT * FROM s
SELECT demo.*
  FROM s
  JOIN demo
    ON demo.RecordDate > s.startDate
   AND demo.RecordDate < s.endDate

RecordDate                               per
----------------------- --------------------
2015-06-19 08:32:07.000    0.894129158512720
2015-06-19 10:32:07.000    0.892172211350294

3q 结贴

[Tiger_Zhao]

SELECT *
  FROM demo 
 WHERE RecordDate >= '2015-06-19 09:00'
   AND RecordDate < '2015-06-19 10:00'
   AND per <= 0.99 

[刀枪blue]

引用 1 楼 Tiger_Zhao 的回复:

SELECT *
  FROM demo 
 WHERE RecordDate >= '2015-06-19 09:00'
   AND RecordDate < '2015-06-19 10:00'
   AND per <= 0.99 

9：30-10:30呢 9：31-10:31呢 时间范围是不固定的 只取1小时间隔

[刀枪blue]

引用 1 楼 Tiger_Zhao 的回复:

SELECT *
  FROM demo 
 WHERE RecordDate >= '2015-06-19 09:00'
   AND RecordDate < '2015-06-19 10:00'
   AND per <= 0.99 

或者这么说 8:00-20:00这个时间段内 查找一小时间隔内符合<=0.99的数据

[a443715279]

SELECT * FROM demo  WHERE RecordDate >= DATEADD(hh,-1,GETDATE())
   AND RecordDate <= GETDATE()
   AND per <= 0.99 

[刀枪blue]

引用 4 楼 a443715279 的回复:

SELECT * FROM demo  WHERE RecordDate >= DATEADD(hh,-1,GETDATE())
   AND RecordDate <= GETDATE()
   AND per <= 0.99 

这个只能拿当前时间去比一小时的 并不能拿到我每分钟的1小时间隔

[a443715279]

你把GETDATE()替换成你的时间不就好了

[刀枪blue]

引用 6 楼 a443715279 的回复:

你把GETDATE()替换成你的时间不就好了

你难道要我遍历？